I recently installed Excel 2007 and now my code is falling over.
It failes on this line:
oSheet.Range("A2").CopyFromRecordset rs
None of the references in the VB window have changed so any ideas?
I'm using DAO:
It failes on this line:
oSheet.Range("A2").CopyFromRecordset rs
None of the references in the VB window have changed so any ideas?
I'm using DAO:
Code:
Private Sub cmd_Export_Click()
'MsgBox (Me.Form.RecordSource)
'MsgBox (Forms!TabsReport!Top10Debtors_BU.Form.RecordSource)
Dim iCount As Integer
Dim strCell As String
iCount = DCount("[Company Description]", Form.RecordSource)
Application.SetOption "Show Status Bar", True
StatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")
'--------------------------------------
'Main recordset with level codes
'--------------------------------------
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim rstReceipts As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs(Me.Form.RecordSource)
'For Each prm In qdf.Parameters
' prm.Value = Eval(prm.Name)
'Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.workBook
Dim oSheet As Excel.workSheet
'uncomment these 2 lines for debugging
'oApp.Visible = True
'oApp.UserControl = True
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
'Debtor days header cells
'oSheet.Range("AA1").Value = "DD"
'oSheet.Range("AB1").Value = "'DD-1"
'oSheet.Range("AC1").Value = "'DD-2"
'oSheet.Range("AD1").Value = "'DD-3"
'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs
etc. etc.