can this code work on mac excel?

[elwood96]

Hi there,

I am wondering if there is anyway to make the following code work on both PC and mac? It works fine on PC, but on mac the BeforeSave and BeforePrint code does not work and the SaveASheet code gets as far as opening and pasting the sheet but then is unable to save automatically.

thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
SaveASheet
End Sub
Private Sub Workbook_Open()
Sheets("Open").Activate
End Sub
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet
#If Mac Then
myPath = "file://localhost/Volumes/DOCUMENTS/3. Design/Supply Orders/Log/"
#Else
myPath = "U:\3. Design\Supply Orders\Log\"
#End If
For Each sht In ThisWorkbook.Worksheets
If (sht.Range("D1").Value <> "") * (sht.Range("g11").Value <> "") Then
fName = myPath & sht.Range("g11").Value & ".xls"
sht.Copy
With ActiveWorkbook
.SaveAs fName
.Close False
End With
End If
Next sht
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
MsgBox "Please use 'Save' to Save this Workbook...", vbCritical, "Your title"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Range("f13").Value = "Update Register" Then
Cancel = True
MsgBox "You must complete the register before printing"
End If
End Sub

 

[jindon]

I don't understaand why those guys create a new thread without replying to their original thread....
Mac path separator is different from Windows.

Application.PathSeparator

 

[RoryA]

Which version of Excel on the Mac?

 

[elwood96]

pretty sure its 2007

 

[RoryA]

There is no version 2007 of Excel for Mac. The two latest ones are 2004 and 2008. 2008 does not support VBA at all so it must be 2004 or earlier. I just checked 2004 and it does support both of those events and your BeforePrint event worked just fine.

 

[elwood96]

thanks for your help rorya and jindon. if 2008 came out this year, then we most certainly will be on 2004 or earlier. I will try the code again soon. cheers.

 

[RoryA]

I notice you are using a conditional compilation constant 'Mac' in the code. Have you changed this when the workbook is running on the Mac?

 

[elwood96]

no. when opened on a mac i used the exact code as above. does this 'Mac' part need to be removed?

 

[mikerickson]

Its probably 2004. 2007 is for Windows and Excel 2008 does not support macros.

2004 is awfully buggy when it comes to ThisWorkbook events, but the Before_Save and BeforePrint code looks OK (but you may want to qualify Range("f13") in to ActiveSheet.Range("f13"))

As Jindon mentioned, the path seperator is different in Mac. Putting =CELL("filename") in a cell will give you an idea of what the variable myPath should look like.
(My current test file's filepath is "Macintosh HD:Users:merickson:Desktop:[Workbook1.xls]Sheet1")

from the VBE help system:
"Strategies for Developing Cross-Platform Solutions

Use Built-in Properties for Paths
In applications whose object models support built-in properties, use these properties to return a file or folder name. For example:
• In Word, Excel, and PowerPoint, use the Path property of the Application object to return the path to the application.
• In Word and Excel, use the PathSeparator property of the Application object rather than a backslash or colon to separate folder names in a path.
• In Word, use the DefaultFilePath property of the Options object to return a folder, such as the Templates folder."

PS. Application.OperatingSystem Like "*Macintosh*" is a good test for which kind of system you are on.

 

[mikerickson]

If the workbook in which that code resides is in the same folder that you want to write the sheets to

myPath = ThisWorkbook.Path

would be one way to make that part of the code cross-platform.