Access Run-time error '91' when working with Excel

Question

I know this has been raised many times (often under Run-time error '1004') but I am having difficulties isolating the error in my code - despite extensive research both here and other sites. My code runs from a command button on an Access form and runs successfully the first time after opening the form, but fails on subsequent attempts. I think I am using inadequate references and/or opening a second Excel object but I can't work out how. Other formatting is carried out, but I have removed as much as possible to keep it short.

Private Sub cmdExport_Click()

Dim dbs As Database
Dim rst As DAO.Recordset
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim lngCount As Long
Dim lngDataRows As Long
Dim intLoop As Integer
Dim strSheetName As String
Dim dteStart As Date
Dim dteEnd As Date
Dim curStartBal As Currency
Dim intMoves As Integer
Dim lngCol As Long
Dim lngRow As Long
Dim intField As Integer
Dim intFieldCount As Integer
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim blnFileCheck As Boolean

strFile = "BudgetForecast.xlsx"
strPath = genFindFolder("tblSettings")  'provides path to data store
strPathFile = strPath & strFile

blnFileCheck = genDeleteFile(strPath, strFile) 'Deletes existing file if it exists

dteStart = DateAdd("m", 1, Date)
dteEnd = DateAdd("m", 12, Date)
strSheetName = "Forecast " & MonthName(Month(dteStart), True) & " " & CStr(Year(dteStart))         'Start Month and Year
strSheetName = strSheetName & " To " & MonthName(Month(dteEnd), True) & " " & CStr(Year(dteEnd))   'Add End Month and Year
curStartBal = [Forms]![frmBudForecast]![txtStart1]

'Create new Excel Workbook and add data
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryBudForecastFinal")
Set appExcel = New Excel.Application
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.ActiveSheet

appExcel.Visible = True

With wks
    .Name = strSheetName
    .Cells(1, 1).Value = "Sort"
    .Cells(1, 2).Value = "Date"
    .Cells(1, 3).Value = "Type"
    .Cells(1, 4).Value = "Account"
    .Cells(1, 5).Value = "Payee/Details"
    .Cells(1, 6).Value = "Jan"
        ' lines for Feb to Nov removed to shorten extract
    .Cells(1, 17).Value = "Dec"
    .Cells(1, 18).Value = "Totals"

    rst.MoveLast
    rst.MoveFirst
    lngCount = rst.RecordCount
    intFieldCount = rst.Fields.Count
    lngDataRows = lngCount + 5

    rst.MoveFirst
    Do Until rst.EOF
        lngCol = 1
        lngRow = .[A65536].End(3).Row + 1
        For intField = 0 To intFieldCount - 1
            .Cells(lngRow, lngCol) = rst.Fields(intField).Value
            lngCol = lngCol + 1
        Next intField
        rst.MoveNext
    Loop

    'Shift columns around to correct order
    If Month(Date) <> 12 Then   'If December, records are already in correct order
        intMoves = Month(Date)
        For intLoop = 1 To intMoves
            .Columns("R:R").Select
                Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove '###Error here
            .Columns("F:F").Select
                Selection.Cut Destination:=Columns("R:R")
            .Columns("F:F").Select
                Selection.Delete Shift:=xlToLeft
        Next intLoop
    End If

End With

'Save new file (next line commented-out for testing)
'appExcel.ActiveWorkbook.SaveAs FileName:=strPathFile, ConflictResolution:=xlOtherSessionChanges
'Close Excel
appExcel.ActiveWindow.Close (False)

'Cleanup
rst.Close
Set rst = Nothing
Set dbs = Nothing
Set wks = Nothing
Set wbk = Nothing
appExcel.Quit   'Not sure if this line is necessary
Set appExcel = Nothing

End Sub

Error occurs on this line:

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

and 'Selection' appears to be 'Nothing'.

I've tried all sorts of variations and amendments to the syntax - I suspect I need to be more specific with the selection of column R, but I don't know how. Incidentally, when the code fails, column R on the spreadsheet is selected. I'm tempted just to hide the command button on the form after it has been clicked, but fear this would be a cop-out and certainly wouldn't help my understanding.

Answer 1

appExcel.Selection

Selection is not part of the Access object model. But you should try to avoid using select/activate where possible. For example:

.Columns("F:F").Select
Selection.Delete Shift:=xlToLeft

would be better written as:

.Columns("F:F").Delete Shift:=xlToLeft

How to avoid using Select in Excel VBA