JohanGduToit
Board Regular
- Joined
- Nov 12, 2021
- Messages
- 89
- Office Version
- 365
- 2016
- Platform
- Windows
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.
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.
VBA Code:
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