Code:
Public Sub ExportQuery()
On Error GoTo ErrHandler
Dim dbs As DAO.Database
Dim rsQuery1 As DAO.Recordset
Dim excelApp As Object
Dim targetWorkbook As Object
Dim ws As Object
Dim i As Integer
Set dbs = CurrentDb
Set rsQuery1 = dbs.OpenRecordset("SHEET3ANALG1")
Set excelApp = CreateObject("Excel.application", "")
Set targetWorkbook = excelApp.workbooks.Open("C:\Users\Kyle\Desktop\DBTEST\workbook.xlsx")
excelApp.Visible = True
Set ws = targetWorkbook.Worksheets("Sheet3")
ws.Range("E2").CopyFromRecordset rsQuery1
'This will delete Column F
'ws.Columns(6).EntireColumn.Delete
For i = 1000 To 1 Step -1
If ws.cells(i, 7).Value Like "$*" Then
ws.cells(i, 7).Delete 1 'delete and shift left
End If
Next i
rsQuery1.Close
dbs.Close
ExitHandler:
Set rsQuery1 = Nothing
Set dbs = Nothing
Set ws = Nothing
Set targetWorkbook = Nothing
Set excelApp = Nothing
Exit Sub
ErrHandler:
MsgBox "error #" & Err.Number & " - " & Err.Description
Resume ExitHandler
End Sub