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