Problems w/code to export from Access to Excel

[mpm32]

Hi,

I am trying to export a query to an exixting Excel file on an existing sheet starting at cell A3 with no column names from Access 2000. I can't pull from Excel because the file belongs to another dept and can not be altered. I have tried many pieces of code I have found from the web. I have these refs selected in this order; VB for Apps, MS Access 9.0 Object Library, OLE Automation, MS Dataobjects 2.1 Library, MS Excel 9.0 Object Library, MS DAO Object Library.

This is what I am trying to use;

Private Sub Command21_Click()

'Copy records to a named range
'on an existing worksheet on a
'workbook
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Working Template"
Const conWKB_NAME = "C:\pbfstax000000.xls"
Const conRANGE = "A3"

  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset("2000DailyExportQuery", dbOpenSnapshot)
  With objXL
    .Visible = True
    [COLOR=Red]Set objWkb = .Workbooks.Open(conWKB_NAME)[/color]
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    objSht.Range(conRANGE).CopyFromRecordset rs
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub

I get this error - Method 'open' of object 'Workbooks' failed.

I thought it might of been an issue with the DAO.Database and DAO.Recordset so I tried this;

Private Sub Command21_Click()

'Copy records to a named range
'on an existing worksheet on a
'workbook
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Working Template"
Const conWKB_NAME = "C:\pbfstax000000.xls"
Const conRANGE = "A3"

  Set db = CurrentDb
  Set objXL = New Excel.Application
  [COLOR=Red]Set rs = db.OpenRecordset"2000DailyExportQuery", dbOpenSnapshot)[/color]
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    objSht.Range(conRANGE).CopyFromRecordset rs
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub

When I run that I get type mismatch.

Can anyone please help me with this code? I am sure it is something simple that I am missing but after working on this for the past 2-weeks I still can't see it.

Thanks for any assistance,

Mark

 

[ChrisHarvey]

I've done this before using the same versions as you and it works fine.

The only line I'm concerned with is the

Set objWkb = .Workbooks.Open(conWKB_NAME)

Remove the With and specifically name it instead

Set objWkb = objXL.Workbooks.Open(conWKB_NAME)

Just in case its getting the wrong reference.

 

[mpm32]

Thanks for the reply. I think I've got it sorted now. It turns out the file I was trying to export to was corrupted. Probably from one of my other attempts at the coding. I deleted the file and copied in a fresh version and it seems to be working.

Thanks again,

Mark