VBA created on a MAC fails on pc running Widows

  • 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.


  • Hello,


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


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi to all.

    Probably will also need to change this line:


    Code
    1. from:
    2. ActiveWorkbook.SaveAs Filename:=strFile
    3. to:
    4. ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=53


    You also have a typo here:

    Code
    1. MsgBox "Hello Rob, " & cbCrLf _
    2. should be:
    3. MsgBox "Hello Rob, " & vbCrLf _

    Why aren't you using Option Explicit ?

  • 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.

  • 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
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 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.

  • Sure - like this:


    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Sure - like this:


    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.

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

    Could be cut down to:

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

    Code
    1. If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.pathseparator
    2. 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?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 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. :)