Export to Excel Error 1004 on range statement

Highlighted
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))
                
            Else
            
                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

    rst.MoveFirst

    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

        rst.MoveNext

      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


Err:

 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 

 

Error.png

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
Highlighted

@dynamictiger 

 

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))

 

Try

 

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.