Refine Code to Not Require Excel 12.0

[detrie]

All
I need to run this routine without adding the reference to use of Excel 12.0. This will be running on machines with Office 2007 as well as Office 2003 (Access2007Runtime)
The routine throws a Compile Error: User-defined type not defined at "Dim objXLBook As Excel.Workbook"

Any ideas on how to modify this code?

Private Sub btnOutToExec_Click()

On Error GoTo ExecHandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook

Dim db As DAO.Database

    Set db = CurrentDb

    conPath = GetPath(db.Name)

    'delete the spreadsheet
    Kill conPath & "MySpreadsheet.xls"

    ' create a workbook from the template
    Set objXLApp = New Excel.Application
    Set objXLBook = objXLApp.Workbooks.Open(conPath & "GL Executive and Operational TEMPLATE.xlt")
    'objXLApp.Visible = True

    objXLBook.SaveAs (conPath & "GLT Executive and Operational Report.xls")
    objXLBook.Close

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ExecReportData", conPath & "GL Executive and Operational Report.xls", True


MsgBox "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf & "where the application sits for ""GL Executive and Operational Report.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
ExecHandleError:
    Select Case Err.Number
        Case 3265
            Resume Next
        Case 1004
            Set objXLBook = objXLApp.Workbooks.Open(conPath & "Generic1.xlt")
            Resume Next
        Case 53
            Resume Next
        Case 75
            Resume Next
        Case Else
            MsgBox Err.Description, vbExclamation, _
             "Error " & Err.Number
    End Select
    Resume ProcDone
End Sub

 

[Trevor G]

Try changing this to an Object as well

Dim objXLBook As Excel.Workbook

Dim objXLBook As Object

 

[detrie]

Thanks Trevor...
That got me to
Should objXLApp also be an object?

Set objXLApp = New Excel.Application

 

[boblarson]

Thanks Trevor...
That got me to
Should objXLApp also be an object?

Set objXLApp = New Excel.Application

Yes, it should.

So,

Dim objXLApp As Object
 
Set objXLApp = CreateObject("Excel.Application")

 

[detrie]

Thanks Bob / Trevor..

Here is the end result. I've included the recommendations you guys made and I added a piece at the end to open the file


Once I fire off the code, I get an Excel message.

The following features cannot be saved in macro-free workbooks:

VB project

To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.

To continue saving as a macro-free workbook, click Yes.

THis will be running on both Office 2007 machines and Office 2003 with Access2007 runtime machines.

Clicking YES continues a flawless export.
Is there a way to "Continue saving as a macro free workbook" without hitting yes?

Private Sub btnOutToExec_Click()


On Error GoTo ExecHandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Object
DoCmd.Hourglass True

'Dim db As DAO.Database

    Set db = CurrentDb

    conPath = GetPath(db.Name)

    'delete the spreadsheet
    Kill conPath & "GL Executive and Operational Report.xls"

    ' create a workbook from the template
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.workbooks.Open(conPath & "GL Executive and Operational TEMPLATE.xlt")
    'objXLApp.Visible = True

    objXLBook.SaveAs (conPath & "GL Executive and Operational Report.xls")
    objXLBook.Close

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExecReportData", conPath & "GL Executive and Operational Report.xls", True

DoCmd.Hourglass False

        Dim strFile As String
        strFile = (conPath & "GL Executive and Operational Report.xls")
        Set objXLApp = CreateObject("Excel.Application")
        objXLApp.Visible = True
        Set objXLBook = objXLApp.workbooks.Open(strFile)
        


'MsgBox "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf & "where the application sits for ""GL Executive and Operational Report.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
ExecHandleError:
    Select Case Err.Number
        Case 3265
            Resume Next
        Case 1004
            Set objXLBook = objXLApp.workbooks.Open(conPath & "Generic1.xlt")
            Resume Next
        Case 53
            Resume Next
        Case 75
            Resume Next
        Case Else
            MsgBox Err.Description, vbExclamation, _
             "Error " & Err.Number
    End Select
    Resume ProcDone
End Sub

 

[boblarson]

You can try placing this in the part before the Save As:

objXLApp.DisplayAlerts = False

and then in the ProcDone

objXLApp.DisplayAlerts = True

Not sure if that will work or not, but it might.

On another note - why are you saving it, closing it, and then reopening it? If you are going to have it open, just save it using the Save As. No need to close it.

 

[detrie]

Hi Bob,

This worked !! thanks

objXLApp.DisplayAlerts = False
and then in the ProcDone
objXLApp.DisplayAlerts = True

Also...
When I commented out the Close I get an Error 3010 .. 'Table ExecReportData' already exists

 

[boblarson]

Hi Bob,

This worked !! thanks


Also...
When I commented out the Close I get an Error 3010 .. 'Table ExecReportData' already exists

Okay, when I re-read your code I see that you are starting a new document from a template and then populating that one with the TransferSpreadsheet code. So I guess that may be how it has to happen.

 

[detrie]

Resolved!!!!

Right..
I do thank you guys for your help!