I have a query that has to be formatted on a daily basis (sometimes several times a day). There is a button that exports everything and performs search and replace. If I run it once, everything is OK. If I modify the query parameter (that is essentially the only change that is done), and try to export again, I get an error
"Error number: - Method 'Cells' of object '_Global' failed".
If I quit Access, and start again, export works OK. Looks like Excel doesn't quit correctly?
If I try to delete generated file, I get the error that the file is "read-only". Looks like Excel is still in the background keeping it open.
"Error number: - Method 'Cells' of object '_Global' failed".
If I quit Access, and start again, export works OK. Looks like Excel doesn't quit correctly?
Code:
Private Sub btnExport2Excel_Click()
On Error GoTo SubError
Dim sFilename As String
Dim filePath As String
Dim LR, i As Long
sFilename = "qryDailyTest"
filePath = Application.CurrentProject.Path & "\" & sFilename & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryDailyTest", filePath, True
MsgBox "File Exported successfully", vbInformation + vbOKOnly, "Export Success"
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Dim wb As Object, ws As Object 'workbook & worksheet
Set wb = xlApp.Workbooks.Open(filePath, True, False)
Set ws = wb.Sheets(1)
ws.Range("a1:c1").EntireColumn.AutoFit
With ws
.Columns("D:D").NumberFormat = "General"
.Rows(1).Delete
.Columns("E:E").Replace What:="" & Chr(10) & "", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
LR = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
i = 1
Do While i <= LR
If Cells(i, 13).Value = "FALSE" Then
ws.Rows(i).EntireRow.Interior.Color = RGB(255, 0, 0)
End If
i = i + 1
Loop
ws.Columns(13).Delete
MsgBox "Search And Replace done successfully", vbInformation + vbOKOnly, "Search And Replace Success"
wb.Close True
xlApp.DisplayAlerts = False
xlApp.Application.Quit
Set xlApp = Nothing
SubExit:
Exit Sub
SubError:
MsgBox "Error Number: " & "- " & Err.Description, vbCritical + vbOKOnly, "An Error Occured"
GoTo SubExit
End SubIf I try to delete generated file, I get the error that the file is "read-only". Looks like Excel is still in the background keeping it open.
Last edited:
