I've tried to research why I'm getting this but haven't found anything useful
Here's the code I'm using which I copied from somewhere else
Expand|Select|Wrap|Line Numbers
- Public Sub CreateExcelInfo()
- 'Set reference to Microsoft Excel Object library
- 'Set reference to Microsoft ActiveX DataObject 2.x
- Const sFileNameTemplate As String = "C:\download\count template.xlsx"
- Dim oExcel As New Excel.Application
- Dim WB As New Excel.Workbook
- Dim WS As Excel.Worksheet
- Dim rng As Excel.Range
- Dim objConn As New ADODB.Connection
- Dim objRs As New ADODB.Recordset
- Dim vData As Variant
- Dim sSQL As String
- Set objConn = CurrentProject.Connection
- sSQL = "Select * from T count sheet output" 'This has to be the name of the query your report is using to display data
- With objRs
- .Open sSQL, objConn, adOpenStatic, adLockReadOnly
- vData = .GetRows()
- .Close
- End With
- With oExcel
- .Visible = True
- 'Create new workbook from the template file
- Set WB = .Workbooks.Add(sFileNameTemplate)
- With WB
- Set WS = WB.Worksheets("T count sheet output") 'Replace with the name of actual sheet
- With WS
- Set rng = .Range("A2") 'Starting point of the data range
- rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData)
- End With
- End With
- .Quit
- End With
- 'clean up
- Set oExcel = Nothing
- Set objRs = Nothing
- Set objConn = Nothing
- Set vData = Nothing
- End Sub