automation error

qwertyjjj

Registered User.
Local time
Today, 11:33
Joined
Aug 8, 2006
Messages
262
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:

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.
 

Users who are viewing this thread

Back
Top Bottom