VBA created on a MAC fails on pc running Widows

Wozzie · Feb 23rd 2021

Hi,

I have created a small Excel pricing model, that includes the attached vba to force a workbook Save_As to protect the template model in it's original format. When input is made to a certain cell, a workbook change routine calls this module to force the save as to a renamed .xlsm workbook. This works fine on my mac but the save as fails on the user pc (Windows 10). Can the code be amended to solve this? Cell B13 contains the reference being used as part of the file name.

Thanks,

Woz.

Code

Sub SaveAs_New_XLSM_Workbook()

'Save as xlsm file using the reference cell value

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

'Get active workbook folder

strPath = wbA.Path
If strPath = "" Then
   strPath = Application.DefaultFilePath
End If

strPath = strPath & "\"
strName = wsA.Range("B13").Value

'Create default name and save for file

strFile = strName & ".Model.xlsm"
strPathFile = strPath & strFile

ActiveWorkbook.SaveAs filename:=strFile

'Confirmation message with file info

MsgBox "Hello Rob, " & cbCrLf _
        & vbCrLf & vbCrLf & _
        "This workbook has been saved as a customer model: " _
        & vbCrLf & vbCrLf & _
        strPathFile


ExitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not save file"
    Resume ExitHandler
    
End Sub

Display More

**Carim** · Feb 23rd 2021

Hello,

If I am not mistaken ... on a Mac you would need to use strPath = strPath & "/"

Hope this will help

rollis13 · Feb 23rd 2021

Hi to all.

Probably will also need to change this line:

Code

from:  
ActiveWorkbook.SaveAs Filename:=strFile 
to: 
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=53

You also have a typo here:

Code

MsgBox "Hello Rob, " & cbCrLf _
should be:
MsgBox "Hello Rob, " & vbCrLf _

Why aren't you using Option Explicit ?

Wozzie · Feb 23rd 2021

Quote from Carim

Hello,

If I am not mistaken ... on a Mac you would need to use strPath = strPath & "/"

Hope this will help

Display More

Hi Carim,

It works fine on my mac. it's on the windows pc that it's not working!

Thanks for looking at this.

rollis13 · Feb 23rd 2021

So, if it's for Excel Windows then this:

Code

ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=53
should be:
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=52

Wozzie · Feb 23rd 2021

Quote from rollis13
Hi to all.
Probably will also need to change this line:
Code
from:  
ActiveWorkbook.SaveAs Filename:=strFile 
to: 
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=53
You also have a typo here:
Code
MsgBox "Hello Rob, " & cbCrLf _
should be:
MsgBox "Hello Rob, " & vbCrLf _
Why aren't you using Option Explicit ?
Display More

Hi, Thanks for your help.

Do you mean Fileformat:=52? I want the 'saved as' file to be a working customer xlsm not a template.

I know Option Explicit is best practise but would it make a difference here?

Woz.

Wozzie · Feb 23rd 2021

Quote from Wozzie

Hi, Thanks for your help.

Do you mean Fileformat:=52? I want the 'saved as' file to be a working customer xlsm not a template.

I know Option Explicit is best practise but would it make a difference here?

Woz.

Display More

Sorry, we crossed

**rory** · Feb 24th 2021

Ideally you'd use If Right$(strPath, 1) <> application.pathseparator Then strPath = strPath & application.pathseparator so that it works on both systems.

I'd also suggest using the enum xlOpenXMLWorkbookMacroEnabled for the FileFormat parameter.

Wozzie · Feb 24th 2021

Quote from rory

Ideally you'd use If Right$(strPath, 1) <> application.pathseparator Then strPath = strPath & application.pathseparator so that it works on both systems.

I'd also suggest using the enum xlOpenXMLWorkbookMacroEnabled for the FileFormat parameter.

Rory, Thanks for this, I'm sure this will be the best fix for me, as my brother (the user) has a Windows pc but I'm creating the model on a Mac.

If you have the time, I would really appreciate it if you could please show me how your fixes fit into or replace the code in my original post.

Woz.

**rory** · Feb 25th 2021

Sure - like this:

Code

Sub SaveAs_New_XLSM_Workbook()
'Save as xlsm file using the reference cell value
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant


On Error GoTo errHandler
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


'Get active workbook folder
strPath = wbA.Path


If strPath = "" Then
strPath = Application.DefaultFilePath
End If


' make sure path ends with the appropriate separator
If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.pathseparatorstrName = wsA.Range("B13").Value


'Create default name and save for file
strFile = strName & ".Model.xlsm"
strPathFile = strPath & strFile
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
'Confirmation message with file info
MsgBox "Hello Rob, " & cbCrLf _
& vbCrLf & vbCrLf & _
"This workbook has been saved as a customer model: " _
& vbCrLf & vbCrLf & _
strPathFile


ExitHandler:
Exit Sub


errHandler:
MsgBox "Could not save file"
Resume ExitHandler


End Sub

Display More

Wozzie · Feb 25th 2021

Quote from rory

Sure - like this:

Code

Sub SaveAs_New_XLSM_Workbook()
'Save as xlsm file using the reference cell value
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant


On Error GoTo errHandler
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


'Get active workbook folder
strPath = wbA.Path


If strPath = "" Then
strPath = Application.DefaultFilePath
End If


' make sure path ends with the appropriate separator
If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.pathseparatorstrName = wsA.Range("B13").Value


'Create default name and save for file
strFile = strName & ".Model.xlsm"
strPathFile = strPath & strFile
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
'Confirmation message with file info
MsgBox "Hello Rob, " & cbCrLf _
& vbCrLf & vbCrLf & _
"This workbook has been saved as a customer model: " _
& vbCrLf & vbCrLf & _
strPathFile


ExitHandler:
Exit Sub


errHandler:
MsgBox "Could not save file"
Resume ExitHandler


End Sub

Display More

Thanks Rory, I tried your code and it kept failing on my Mac. When I stepped through it, it appeared to be failing on the "If Right$" line (I think from the Then part).

I've tried a few things today and got the following code to work on my Mac, using FileFormat:=53. I then changed this to FileFormat:=52 for the users windows pc but it still failed to save when he ran it! There is no doubt a simple fix to this but it is beyond my beginner knowledge!

Woz.

Code

Sub SaveAs_New_XLSM_Workbook()

'Save as xlsm in same folder using the reference cell value

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


'Get active workbook folder
strPath = wbA.Path
If strPath = "" Then
   strPath = Application.DefaultFilePath
End If

strPath = strPath & "\"
strName = wsA.Range("B13").Value
   
   
'Create file name and save
strFile = strName & "_Model"
strPathFile = strPath & strFile

'__________________________________________________________________

ActiveWorkbook.SaveAs filename:=strFile, FileFormat:=53

'Nb
'On a Mac: FileFormat:=53 = xlOpenXMLWorkbookMacroEnabled = xlsm
'In Windows: It's FileFormat:=52 as the number is +1 for a Mac!
'__________________________________________________________________

'Confirmation message with file info
MsgBox "Hello Rob, " & vbCrLf _
        & vbCrLf & vbCrLf & _
        "This workbook has been saved as a customer model: " _
        & vbCrLf & vbCrLf & _
        strPathFile


ExitHandler:
    Exit Sub

Display More

rollis13 · Feb 25th 2021

It kept failing only because there is a typo in that line. Change those few lines like this:

Code

'[...]
' make sure path ends with the appropriate separator
If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.PathSeparator
'Create default name and save for file
strName = wsA.Range("B13").Value
strFile = strName & ".Model.xlsm"
strPathFile = strPath & strFile
ActiveWorkbook.SaveAs Filename:=strPathFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
'Confirmation message with file info
MsgBox "Hello Rob, " & vbCrLf & vbCrLf & vbCrLf & _
"This workbook has been saved as a customer model: " _
& vbCrLf & vbCrLf & strPathFile
'[...]

Display More

Could be cut down to:

Code

'[...]
' make sure path ends with the appropriate separator
If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.PathSeparator & wsA.Range("B13").Value & ".Model.xlsm"
'Create default name and save for file
'strName = wsA.Range("B13").Value
'strFile = strName & ".Model.xlsm"
'strPathFile = strPath & strFile
ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
'Confirmation message with file info
MsgBox "Hello Rob, " & vbCrLf & vbCrLf & vbCrLf & _
"This workbook has been saved as a customer model: " _
& vbCrLf & vbCrLf & strPath
'[...]

Display More

**rory** · Feb 26th 2021

Yeah, not sure how those lines got merged, but that should be two lines:

Code

If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.pathseparator
strName = wsA.Range("B13").Value

I'd have expected using the enum name for the fileformat rather than a literal number should work on either version - does it not?

Wozzie · Feb 27th 2021

Rollis13 and Rory,

Thank you both for your help. My saving problem has been resolved and now works on both Mac and Windows. I have incorporated your suggested code as it's much tighter than my original, although the problem was actually a silly error on my part! I had included a number of "/" in the reference that was making up the filename, which works on a Mac but is not accepted in Windows!

Many Thanks,

Woz.

VBA created on a MAC fails on pc running Widows

Participate now!

Share