Unable to Close Workbook VBA (Excel 97-2003)

[JohanGduToit]

Morning All,

I am struggling to Save and Close an Excel 97-2003 workbook after making some modifications to the file using VBA from MS Access (365). The execution seems to fail (hangs) on
the .Application.ActiveWorkbook.Close line and the workbook remain open until I manual reopen and save the file manually afterwards.

The code works without any issues when modifying a later version (i.e. .xlsx) of the same workbook, so I believe it's version (97-2003 .xls) related. Please advise as to how I can resolve this. Code below.

Public Sub FormatCrazy(sFile As String)

'On Error GoTo Err_FormatCrazy

Dim xlApp As Object

Dim xlSheet As Object

Application.SetOption "Show Status Bar", True

vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting Crazy Stores File... Please wait.")


Set xlApp = CreateObject("Excel.Application")

Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)


With xlApp

.Application.DisplayAlerts = False

.Application.Sheets(1).Select

.Application.Rows("1:1").Select

.Application.Selection.Delete shift:=xlUp

.Application.Range("C:C,E:E,G:G,H:H,J:J,K:K,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S").Select

.Application.Selection.Delete shift:=xlToLeft

.Application.Range("A1").Select

.Application.ActiveWorkbook.Save

.Application.DisplayAlerts = True



.Application.ActiveWorkbook.Close



.Quit

End With



vStatusBar = SysCmd(acSysCmdClearStatus)



Set xlSheet = Nothing

Set xlApp = Nothing



'Exit_FormatCrazy:

' Exit Sub



'Err_FormatCrazy:

' vStatusBar = SysCmd(acSysCmdClearStatus)

' MsgBox Err.Number & " - " & Err.Description

' Set xlSheet = Nothing

' Set xlApp = Nothing

' Resume Exit_FormatCrazy



End Sub

 

[Alex Blakenburg]

If you are able to verify that the previous .Application.ActiveWorkbook.Save has saved the spreadsheet, try changing the close line to:
.Application.ActiveWorkbook.Close SaveChanges:=False

 

[JohanGduToit]

I believe I have managed to solve the problem by replacing:

.Application.ActiveWorkbook.Save

with

.Application.ActiveWorkbook.SaveAs sPath, FileFormat:=xlWorkbookNormal

where sPath = full path and filename of formatted Excel 97-2003 file

 

[JohanGduToit]

If you are able to verify that the previous .Application.ActiveWorkbook.Save has saved the spreadsheet, try changing the close line to:
.Application.ActiveWorkbook.Close SaveChanges:=False

Thank you Alex! Tested your suggestion and it works as well

 

[Alex Blakenburg]

Thanks for letting me know. Now you have options.