MAC VBA Fails to save and close named workbook

I have a Excel VBA to anlayse SFDC data that has been running on Windows for years and I need to port it across to Excell 2011 for MAC, due to the number of users choosing MAC laptops.

Most of the code /userforms , opening files and calculations all work fine. The only issue I have is trying to save a named Workbook, any method i try results in an error and exception. I end up with a lot of working file therefore left open.

Attempt to close open files either by direct filename reference or <file_pointer> always fails....even though the file is valid and open in MAC Excel.

• Workbooks("Extract.xls").Close
• Workbooks("Extract.xls").Close savechanges := False
• Workbooks(ref_fn_index("Extract.xls")).Close

======Example code tried ========

Workbooks("Extract.xls").Close

Workbooks("Extract.xls").Close savechanges := False

Workbooks(ref_fn_index("Extract.xls")).Close


Public Function ref_fn_index(enq_file As String) As Integer
'=== Find reference ptr of open workbook filename for MAC restriction====
Dim i As Integer
Dim file_seen As Boolean

file_seen = False
ref_fn_index = -1
For i = 1 To Workbooks.count
If LCase(Workbooks(i).name) = LCase(enq_file) Then
file_seen = True
ref_fn_index = i
Exit Function
End If
Next i
'==== Catch error case ===
If Not file_seen Then MsgBox "Unable to find current workbook", vbCritical, "File I/O Eror"
End Function

How are you opening the files?

presumably you mean

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

since neither of your examples are valid code. I suggest you simply store a reference to the workbook and use that to close it

Please Login or Register  to view this content.

although I can't see why your code would fail-do you get an error message?