VBA - Getting Computer Name and User Name on a Mac, Excel 2011

[willardpeabody]

Hi!

I'm finding it difficult to do one specific thing. I want to open a workbook that has the same filename on 2+ different computers, all of these computers being Macs (OSX version 10.5 and later).

The problem is when I use the following code to open a specific workbook, it will only open on the computer with that Computer name ('Macintosh HD') and that user name ('MainUser'):
Workbooks.Open Filename:= _
"Macintosh HD:Users:MainUser:Documents:Folder1:Example.xlsm"

How do I get it to open a file with the same name and in the same folder, except on a different computer with a different user? (So that this file can be shared and my macros can run effectively on each computer.)

Is there some way to use a name for a computer/user that is generic and not specific to one computer and user? Can I open the workbook in another way without referencing the entirety of the file's path?

I own a copy of Mr. Excel's textbook 'VBA and Macros: Microsoft Excel 2010,' but I could not find an answer there!! Other solutions that I found online either involve Windows API, which the Mr. Excel textbook says only works on windows. Likewise, using some sort of Environ("username") function also does not work on a Mac, I'm told. I'm sure that there is someway this can work on a Mac!

I hope someone can help me with this simple problem! There's not a lot of support out there for Excel 2011 for Mac! (Especially because it's so new.)

Thanks so much,

Will

 

[Jim Gordon]

To get the current file path use CurDir in VBA, then parse the result by searching for the colon ) character to capture the portion of the file path up to the 3rd colon.

 

[wimblejon]

Hi Will,
Did you ever solve this problem, I have a similar one?
Jon

Hi!

I'm finding it difficult to do one specific thing. I want to open a workbook that has the same filename on 2+ different computers, all of these computers being Macs (OSX version 10.5 and later).

The problem is when I use the following code to open a specific workbook, it will only open on the computer with that Computer name ('Macintosh HD') and that user name ('MainUser'):
Workbooks.Open Filename:= _
"Macintosh HD:Users:MainUser:Documents:Folder1:Example.xlsm"

How do I get it to open a file with the same name and in the same folder, except on a different computer with a different user? (So that this file can be shared and my macros can run effectively on each computer.)

Is there some way to use a name for a computer/user that is generic and not specific to one computer and user? Can I open the workbook in another way without referencing the entirety of the file's path?

I own a copy of Mr. Excel's textbook 'VBA and Macros: Microsoft Excel 2010,' but I could not find an answer there!! Other solutions that I found online either involve Windows API, which the Mr. Excel textbook says only works on windows. Likewise, using some sort of Environ("username") function also does not work on a Mac, I'm told. I'm sure that there is someway this can work on a Mac!

I hope someone can help me with this simple problem! There's not a lot of support out there for Excel 2011 for Mac! (Especially because it's so new.)

Thanks so much,

Will

 

[Jim Gordon]

Visit this web page by Excel MVP Ron DeBruin. It has examples of everything you need:
[h=2]Working with Files and Folders (Mac)[/h]