OS X Excel 2011 VBA Save File Name

[Gerasimos_Zap]

I need to write a script to save a worksheet with to a predetermined location and the save name is filled from values within the worksheet. I can get it to save in the proper location, but the file name returns a combination of FATPMetiFolderPath and FATPMetiPath (\Volumes\MFS1\Groups\METI...\METIman\MMP0123 - FATP.xlsm). I can do this just fine with Windows Excel VBA, but I have never used a Mac before. I am programming on a PC, but it needs to be able to be saved properly if used on a Mac.

[COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] saveFATPMMMac[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]()[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#000000]
 [/COLOR][COLOR=#808080]'Saves copy for access for everyone[/COLOR][COLOR=#000000]
 [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] FATPMetiPath [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000]
 [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] FATPMetiFolderPath [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000]


 FATPMetiFolderPath [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"\Volumes\MFS1\Groups\METI\Quality Control\Function and Acceptance Test Documents\METIman\"[/COLOR][COLOR=#000000]
 [/COLOR][COLOR=#808080]'FATPMetiFolderPath = "C:\Users\gzapantis\Desktop\"[/COLOR][COLOR=#000000]
 FATPMetiPath [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] FATPMetiFolderPath [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] _
    Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Failure Report"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"FailReportSN"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Text [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]" - FATP "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]".xlsm"[/COLOR][COLOR=#000000]

 ThisWorkbook[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]SaveAs Filename[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]FATPMetiPath
 [/COLOR]</code>[COLOR=#00008B][FONT=Consolas]End[/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR]

 

[starl]

..not sure your exact issue since you aren't stating it. But there are many differences between Macs & Windows VBA. Anything dealing with files you'll have to code differently based on your OS. I suggest bookmarking the following link - a wealth of information and should help with whatever your issue is: Working with Files and Folders (Mac)

fyi - Applescript is extremely finicky - even Mac users pull their hair out.

Also - you need to set the fileformat in your SaveAs. Wasn't important back in legacy Excel, but required nowadays - else, despite your extension, Excel will save it with your default format. Also, fileformats between Windows & Mac differ.

Welcome to the extremely frustrating world of programming on a Mac.

 

[Gerasimos_Zap]

What I am attempting to do is create a script to save the worksheet to a specific folder on the server with a file name that is pulled from a specified named range. As it is now, it will save as a new file, in the correct folder, but with the entire folder path as a part of the file name property. I can get the script to work properly on PC's...but it is having issues when used on a Mac. All of my other macros work just fine on Mac's...but this "Save As" macro is proving to be very problematic (though I am sure it would go a lot easier if I was not trying to learn a language while scripting).

 

[starl]

the path separator on Macs is : not \

 

[Gerasimos_Zap]

I already tried changing that and that did not make a difference. It would still save it as either the super long file path or truncate it to only use " - FATP" and saves it in the folder where the original document is located.

 

[starl]

please post your code using the proper application.pathseparator symbol. Also, make sure you have the correct fileformat value (53)
also, have you verified that path exists as you have coded it? Mac paths are different from PC paths

 

[Gerasimos_Zap]

I was actually able to combine two buttons into one (One to save in PC format and one to save in Mac format) and it works. I still need to test it out thoroughly, but it saves the file name correctly and in the correct location. Thank you for pointing me in the correct direction.

Sub saveFATPMMMac()

'Saves copy for access for everyone
Dim FATPMetiPath As String
Dim FATPMetiFolderPath As String


If Application.PathSeparator = ":" Then
    FATPMetiFolderPath = "Volumes:MFS1:Groups:METI:Quality 


Control:Function and Acceptance Test Documents:METIman:"
Else
    FATPMetiFolderPath = "F:\Groups\METI\Quality Control\Function and Acceptance Test Documents\METIman\"
End If


FATPMetiPath = FATPMetiFolderPath & _
        Sheets("Failure Report").Range("FailReportSN").Text & " - FATP.xlsm"
        
ThisWorkbook.SaveAs Filename:=FATPMetiPath


End Sub

 

[starl]

Sometimes you can combine PC & Mac - but do be careful - sometimes you can't. Not just code-wise, but actual objects in the workbook. Userforms for example - can't share. You wont' get an error, but the form will change. What I do is have the same code in both - but save two copies, one for each OS. Note that the forms on a Mac have to be designed on a Mac. Also, I have run into issues when trying to have pivot tables used on both. Weird things happen.

Don't forget the fileformat value. xlsm is 52 on PC and 53 on Mac.