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]

And you can still type?

I've got trouble even typing and using my laptop with it's incredibly helpful touchpad.

 

[TR21Mark]

Just for anyone who may use this post as a reference, to hide the security warning about saving the filetype I suppressed by adding -4143 at the end of the save line

objXLBook.SaveAs (conPath & "JobComp.xls"), -4143

 

[GeofW]

What does that warning look like?

 

[TR21Mark]

see this post


http://www.mrexcel.com/forum/showthread.php?t=363314