External table is not in the expected format

[Indigo]

My company is preparing to move from Office 2003 to Office 2010. I am testing Excel and Access files and encountered this error when running the following code in Access:

Sub ExportSpreadsheet()
On Error GoTo HandleError
    
Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
Dim strFile As String
Dim strPath As String
strPath = "J:\SILO FILES\2011 SILO KPI TRACKING"
    'find the folder where the database resides
    strFile = CurrentDb.Name
    strPath = Mid(strFile, 1, Len(strFile) - Len(Dir(strFile)))
    
    'delete the workbook if it already exists
    Kill strPath & "RepeatProblemSolving.xls"
    
    ' create a workbook from the template
    Set objXLApp = New Excel.Application
    Set objXLBook = objXLApp.Workbooks.Open(strPath & _
        "RPSChartTemplate.xlt")
    ' save and close the workbook
    objXLBook.SaveAs (strPath & "RepeatProblemSolving.xls")
    objXLBook.Close
    ' export queries to newly create workbook
    DoCmd.TransferSpreadsheet acExport, , "qryExport", strPath & _
        "RepeatProblemSolving.xls", True
    DoCmd.TransferSpreadsheet acExport, , "qryBodyMgmt", strPath & _
        "RepeatProblemSolving.xls", True
    DoCmd.TransferSpreadsheet acExport, , "qryBodyTM", strPath & _
        "RepeatProblemSolving.xls", True
   
    
    'open and close the workbook again to refresh the chart
    Set objXLBook = objXLApp.Workbooks.Open(strPath & _
        "RepeatProblemSolving.xls")
    objXLBook.Save
    objXLBook.Close
 
ProcDone:
    ' Clean up objects
    Set objXLBook = Nothing
    Set objXLApp = Nothing
    
ExitHere:
    Exit Sub
HandleError:
    Select Case Err.Number
        Case 1004 'a template does not exist
            MsgBox "There is no template for this chart."
            Resume ProcDone
        Case 53 'Excel file cannot be found to delete
            Resume Next
        Case Else
            MsgBox Err.Description, vbExclamation, _
                "Error " & Err.Number
            Resume ProcDone
    End Select
End Sub

I have tested this a few times and get the same results. The query does not export at all or the resulting file created by the template is completely corrupted. Now I read something in another thread about headers and differences between 2003 and 2010, but if someone could point me in the right direction as to where I can find the solution? Thanks,

 

[Indigo]

So, what I did was go to my .xlt file and format the worksheets as tables in Excel 2010 and it worked. Now my issue is the linked worksheets do not automatically update... :-(