Applescript choose folder with prompt from Excel VBA

Scripting Forums AppleScript | Mac OS X
#1

Hi all

System is running Lion with Excel 2011

New to running applescript from Excel VBA so I like to know this :

What is the good way ro get the returned folderPath string without the word alias before it
I can remove the characters with replace but maybe there is another way

The msgbox below put the word “alias” before the folderpath

Sub Select_Folder_On_Mac()
Dim folderPath As String
Dim RootFolder As String

On Error Resume Next
RootFolder = MacScript(“return (path to desktop folder) as String”)

folderPath = MacScript(“choose folder with prompt ““Select the folder”” default location alias “”” & RootFolder & “”“”)

On Error GoTo 0
If folderPath <> “” Then
MsgBox folderPath
End If
End Sub

Thanks

#2

I don’t know VBA, however this may work for you…

folderPath = MacScript("(choose folder with prompt ""Select the folder"" default location alias """ & RootFolder & """) as text")

You basically want to tell applescript to change it to text before sending it back to you. You did it in the other MacScript line of code where you have “as string” after (path to desktop) so the same approach should work. I used “as text” because that replaced “as string” a few years ago… but you could still use “as string” here too.

One other comment. Applescript paths normally are colon “:” delimited. Most languages use “/” delimited paths. So if VBA uses slash delimited paths then you should ask applescript to return the path to you in that style. You would use “posix path” for that so your MacScript commands would be…

RootFolder = MacScript("return posix path of (path to desktop folder)")

folderPath = MacScript("posix path of (choose folder with prompt ""Select the folder"" default location alias """ & RootFolder & """)")

Note that “posix path” does both, it converts the path to slash delimited and to a string at one time.

#3

If he uses both examples he should change alias into posix file.

#4

Thank you both

Great information
I am try to get all my VBA code working that is on my site in Excel 2011
VBA is bad in that version so using a comination of VBA and Applescript will give you better results.

I start this page where I will add this also
http://www.rondebruin.nl/mac.htm

Ron de Bruin
http://www.rondebruin.nl/tips.htm

#5

One more thing

Can you push me in the good direction with this example

Sub Select_File_Or_Files_On_Mac()
Dim FilePath As String
Dim RootFolder As String

On Error Resume Next
RootFolder = MacScript(“return (path to desktop folder) as String”)
'Or use RootFolder = “Macintosh HD:Users:mvp:Desktop:TestMap:”
FilePath = MacScript(“(choose file with prompt ““Please select a file or files”” default location alias “”” & RootFolder & “”" multiple selections allowed true) as string")
On Error GoTo 0

If FilePath <> “” Then
MsgBox FilePath
End If
End Sub

If I choose one or more then one file it will be in a list if I read it good
How can I loop through this list, if I look in the msgbox result i see no seperator between the files

Thanks, sorry I amnew to this stuff

#6

I’m not familiar with vba scripting but if you do not see {“pathtofile1”, “pathtofile2”, “pathtofile3”} list syntax (very much JSON like) you can create a unix style list (rows separated by a new line). Again I’m not familiar with vba and I don’t know if you can do anything with this data but it’s just an idea.


set theChosenFiles to choose file with multiple selections allowed
repeat with x from 1 to count theChosenFiles
	set item x of theChosenFiles to POSIX path of item x of theChosenFiles
end repeat
set AppleScript's text item delimiters to character id 10
set theChosenFiles to theChosenFiles as text
set AppleScript's text item delimiters to ""
return theChosenFiles
#7

Thanks

I will play with this and let you know if I can get it to work

Have a nice evening

#8

In applescript only it is working OK,but in the code when I not use as as string
in the file select example I get all the files seperated by a , and the word alias before it.

When I use as string the files will not be seperated but the word alias is OK

Maybe I must use replace in VBA to remove the word alias

Will try more this weekend

#9

Thanks to DJ Bazzie I put the first draft of the code online so this thread is closed
http://www.rondebruin.nl/mac.htm#GetOpenFilename

Thanks and have a nice weekend