Ticket Options

Question Details

TICKET ARCHIVE -> Vba Macro Editor

FOUTURE - Mar 31, 2005 - 4:50 am

Hi,

I'm using MacOS X (10.3.8) and Microsoft Word for Mac Release 1, and I have a problem with the Visual Basic macro editor :

I need a pathname to reference a folder "Reports" that always resides on the Desktop (I create it if it does not exist).

On Windows, this is done this way :

"~/Desktop/Reports/"

But on MacOS X, slashes are not permitted in pathnames. We have to use colons instead. Still, this does not work :

"~esktop:Reports:"

Which could be the correct syntax on MacOS X ?

Thanks for your help

kainjow - Apr 2, 2005 - 6:43 pm

Hi Bart,

First, a little clarification. Mac OS X uses / as the file path delimiters, while Windows uses \ (but in newer versions of their OS can use / I believe). Mac OS (Classic) uses : so whenever you see : in a Mac OS X program, that's legacy software.

Anyways, ~ most likely will not work at all in VBA. ~ is a shortcut for the /Users/[username] directory. I'm 98% sure it will not work in Windows. This could be your problem. Many programs don't support ~ (they have to add additional support for it).

So in conclusion, I would try using the full path in your VBA macro such as /Users/[username]/Desktop/Reports and for Windows you would use \ (you could also try using \ in Mac OS X also).

Let me know if I can be of further assistance.

Thanks,
Kevin

FOUTURE - Apr 3, 2005 - 5:22 pm

Hi Kevin,

Thanks for your response.

In the VBA documentation I found this :


"~/Desktop/Reports/"

I suppose this documentation is written for Windows. It certainly does not work on MacOSX. Neither do backslashes or colons.

True, I didn't test this (I don't have a Windows system), but this is what the docs say.

Your suggestion of trying to specify the full path can't be used here, because both the username and the name and organization of the hard disk can vary (the macro is not for me, but for our 550 users). Absolute pathnames are no good here.

I need a relative pathname to the desktop, or a logical name that refers to the desktop directly.

Any suggestions ?

Bart

kainjow - Apr 3, 2005 - 6:15 pm

Bart,

I think I've figured it out. I searched online and through the Object Browser directly in the Visual Basic Editor in Word, and couldn't find anything that would give us the home folder (~). But I found that you can use the MacScript() function to execute any AppleScript.

So what I did (first time playing with VBA before - quite fun) was call the MacScript() function and have AppleScript return the path to the Desktop.

Here's the full code I used (got the file writing code from somewhere else). Let me know if it works!

------
' Name of the file
Const FileName As String = "Test.txt"
' Text to write into the file
Const TestString As String = "Testin123..."
Dim DesktopFolder As String
Dim FullPath As String

' Call AppleScript to get the Desktop folder
DesktopFolder = MacScript("return (path to desktop) as string")
' Append the FileName to DesktopFolder
FullPath = DesktopFolder & FileName

' Write the file (just for testing)
Dim FileNum As Integer
FileNum = FreeFile
Open FullPath For Append As #FileNum
Print #FileNum, TestString
Close #FileNum
------

Also, I did some testing before all of this and you do need to use : as the path delimiter. If you will be writing code for Windows, then you would want to the Application.PathSeparator to get the file delimiter (: for Mac, \ for Windows I'd imagine). The ~ doesn't appear to work. And :Users... doesn't work either. You need to use something like "Macintosh HD:Users:kainjowesktop:Reports" if you hard coded it in, but that obviously won't work because you need know the username.

Try the above code and let me know if it helps. If this document is going to have to work on Macs and Windows, then you'd need to do some if-then checking to see which OS it's running on (not sure of how to do this at the moment).

Kevin

kainjow - Apr 3, 2005 - 6:16 pm

Oh, and for the path delimiter (: or \ or /) you can use the Application.PathSeparator constant and that will give you the correct separator for Mac and Windows.

Kevin

FOUTURE - Apr 8, 2005 - 4:23 am

Thanks, Kevin !

Bart