Solved Problem with Excel operations (search / replace / save) with VBA

[A1ex037]

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?

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 Sub

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.

 

[June7]

Try:

If ws.Cells

 

[A1ex037]

Well, this is the catch of the day! Nice find. Looks like it's working now.

Thanks!

 

[Gasman]

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.

You still need to close the file etc if you have an error?, so put your SubExit: lable above that code.
I would have also thought you'd want to turn DisplayAlerts back on, not off?