Access 2007 to excel 2003

[TR21Mark]

I have been searching for some clarification to some issues I am having.

I have an application that is being converted to Access 2007 front end and will be using the runtime engine and a Sagekey install package. The existing application is in Access 2003. All of our users have 2003 version installed on their computer. One of the primary reasons for the upgrade is the rich text fields, and will eventually moving to SQL backend and to Sharepoint in the near future.

Anyway, because I have Office 2007 only on my development pc, and can not reference the 2003 version, I am running into this issue with the Transfer Spreadsheet method. I want to be able to still export to an Excel 2003 template. Issue I am having is the code will not compile and get thrown on this line:

Set objXLApp = CreateObject("Excel.Application")

Here is the code I have have:

Option Compare Database
Option Explicit

Sub exportspreadsheet()
    ' Comments:
    ' Params  :
    ' Modified:
    
    On Error GoTo HandleError
    
    Dim objXLApp As Object
    Set objXLApp = CreateObject("Excel.Application")
    Dim objXLBook As Excel.Workbook
    Dim conPath As String
    
    conPath = "C:\Export\"
    
    'delete the spreadsheet
    Kill conPath & "JobComp.xls"
    
    ' create a workbook from the template
    Set objXLApp = New Excel.Application
    Set objXLBook = objXLApp.Workbooks.Open(conPath & "JobComp.xlt")
    'objXLApp.Visible = True
    
    objXLBook.SaveAs (conPath & "JobComp.xls")
    objXLBook.Close
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTransfer1", conPath & "JobComparison.xls", True
    
    MsgBox "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf & "where the application sits for ""JobComp.xls"""
    
ProcDone:
    On Error Resume Next
    
    Let's clean up our act
    Set qdf = Nothing
    Set db = Nothing
    Set rs = Nothing
    Set objResultsSheet = Nothing
    Set objXLBook = Nothing
    Set objXLApp = Nothing
    
ExitHere:
    Exit Sub
HandleError:
    Select Case Err.Number
        Case 53
            Resume Next
        Case 75
            Resume Next
        Case Else
            MsgBox Err.Description, vbExclamation, _
             "Error " & Err.Number
    End Select
    Resume ProcDone
End Sub

Any suggestions?

 

[Norie]

I don't see how the version difference is causing the problem.

In fact as far as I can see there is nothing version specific in your code.

Mind you I just noticed that you appear to be creating 2 instances of Excel.

One here:

Set objXLApp = CreateObject("Excel.Application")

and a second one here.

Set objXLApp = New Excel.Application

The 2nd one might cause problems if things aren't referenced properly.

What is the error message you are getting?

 

[TR21Mark]

I am trying to compile the module. I am getting compile error - User type not defined

 

[GeofW]

What object does it highlight with the compile error?

 

[TR21Mark]

On this line

Dim objXLBook As Excel.Workbook

However, I went an deleted up to the 'AS' so I could get the intellesense dropdown, Excel is not in the list. My references are set for Office 12 Library, Access, and Excel 12 Library.

The Excel.Workbook seems to be the problem so far, as it wont let me step through it.

 

[Norie]

If that's the problem don't use it.

Take a look at late-binding, you already seem to be sort of using it with CreateObject.

 

[GeofW]

You seem to have the right references checked. As Murf said, you do not need that 2nd instance of Excel.

However my app is Access 2003 to Excel 2003. In Access 2003 I have a reference to the Micorsoft Excel 11.0 Object Library (2003).

Shoot - just remembered, I don't have Access 2007.

 

[TR21Mark]

Man, something cooky is going on today. I went and unchecked all of my references and closed out Access. Went back in and reselected them and it is working fine with no issues.


Thank you all so much for your time and help, but looks like a possible glitch on my end.

 

[Norie]

GeofW

Who is Murf?

 

[GeofW]

sorry norie i meant you (holding a 17lb cat with 1 arm)