Export to Excel Error 1004 on range statement

Occasional Contributor

This error is proving challenging to my limited understanding of interaction between Access and Excel.


First the code:



Public Sub ExportToExcel(ByRef rst As ADODB.Recordset, filename As String, lngRstCount As Long)
On Error GoTo Err:
'    I could not use early binding in my case because my client has an older version of Excel
'      but it may not be a problem for you
'    Dim createExcel As New Excel.Application
'    Dim Wbook As Excel.Workbook
'    Dim Wsheet As Excel.Worksheet
'    Set Wbook = createExcel.Workbooks.Add
'    Set Wsheet = Wbook.Worksheets.Add

Dim wbook As Object
Dim createExcel As Object
Dim Wsheet As Object
Dim fieldIdx As Integer
Dim rowIdx As Integer
Dim rstCount As Long

    Set createExcel = CreateObject("Excel.Application")

    Set wbook = createExcel.Workbooks.Add

    Set Wsheet = wbook.Worksheets.Add
    ' writing column headers

    For fieldIdx = 0 To rst.Fields.Count - 1         
            If IsNumeric(rst.Fields(fieldIdx).Name) Then
                Wsheet.Cells(1, fieldIdx + 1).Value = cssGetHeader(CLng(rst.Fields(fieldIdx).Name))
                Wsheet.Cells(1, fieldIdx + 1).Value = rst.Fields(fieldIdx).Name
            End If

    Next fieldIdx

    '' looping through rows and writing in spreadsheet
    'rstCount = rst.RecordCount
    If (lngRstCount > 0) Then


    For rowIdx = 0 To lngRstCount - 1

      For fieldIdx = 0 To rst.Fields.Count - 1

        Wsheet.Cells(rowIdx + 2, fieldIdx + 1).Value = rst(fieldIdx).Value

        Next fieldIdx


      Next rowIdx

    End If    
    With Wsheet    
    Dim rng As Variant
        Set rng = .Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
        .ListObjects.Add(SourceType:=xlSrcRange, _
        Source:=rng.Address, _
        xlListObjectHasHeaders:=xlYes _
        ).Name = "myTable"

    End With

    wbook.SaveAs filename
    wbook.Close True
    Set rng = Nothing    
    Set wbook = Nothing
    Set Wsheet = Nothing

    Set wbook = createExcel.Workbooks.Open(filename)    
    createExcel.Visible = True
    Set wbook = Nothing    
    Set Wsheet = Nothing
    Set createExcel = Nothing   

    Exit Sub


 Select Case Err.Number

    Case 32755

             MsgBox "Press Cancel button"

    Case 1004

             MsgBox Err.Number & " Cannot save file '" & filename & "' (is it open?)" & " MS Err: " & Err.Description

             wbook.Close False

    Case Else

         MsgBox Err.Number & " " & Err.Description

 End Select

    Set createExcel = Nothing

    Set wbook = Nothing

    Set Wsheet = Nothing

End Sub



When I open the Access application the first time this works no issue to export one recordset to Excel.  


However if I then try and run the code again, it generates 



on line:

Set rng = .Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))


Which I read as being caused by:

# 3 – VBA Run Time Error 1004: Select Method of Range class failed:

This usually occurs when we try to select the cells of other than active sheet without making the sheet select or active.

according to the site I found that made the most sense to me https://www.wallstreetmojo.com/vba-1004-error/

The suggested fix seems to rely on naming the range.  However I am very much unfamiliar with using excel from Access and am unclear how to understand or undertake this ... or for that matter if it can be done the way I am trying to do it?


1 Reply



Access knows nothing about Excel, so using Range doesn't makes sense to it, instead you need to always prefix everything with an Object that you defined earlier (Excel, WorkBook, WorkSheet).  So instead of


Set rng = .Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))




Set rng = .Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell))


You may also like to look at https://www.devhut.net/2017/03/15/ms-access-vba-export-recordset-to-excel/ as an alternative approach if need be.


Also, I just noticed you are using Objects, so Late binding, thus you should be declaring all the Excel constants that you use


Const xlLastCell = 11

Const xlSrcRange = 1

Const xlYes = 1



and if you are using Late Binding (which is always a good idea) be sure you don't have the Excel Reference selected.