Getting Error 3274 External table is not in the expected format

B

bconner

Registered User.
Local time
Today, 03:30
Joined
Dec 22, 2008
Messages
183
I have a macro that is producing the error message: '3274 External table is not in the expected format' when it gets to the line of code that exicutes the 1st Transfer Spreadsheet method. Below is the code I am using:

The only change is I.T. installed Excel 2007 along with 2003. The macro creates the named workbook but bombs when it tries to transfer the first query to it.

Any thought are greatly appreciated.


Code: 
[SIZE=3][FONT=Times New Roman]Private Sub Command3_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim User As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim xlBook As Object[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim xlApp As Object[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim GrpNumber As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim RefLoc As String[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'Capture User Name from Windows Login[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]User = Environ$("USERNAME")[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]' Create Excel Workbook to Export DeepDive Queries into[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Set xlApp = CreateObject("Excel.Application")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Set xlBook = xlApp.workbooks.Add[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]GrpNumber = Form_FrmDeepDiveConsolidatedReports.Txt_Grp.Value[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]RefLoc = Nz(Form_FrmDeepDiveConsolidatedReports.Combo_LocationName.Value, "Group Consolidated") 'NZ() Function returns a value if Null[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]xlBook.SaveAs ("C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls")[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]xlBook.Close[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]On Error GoTo SendError[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'Aetna Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Aetna AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Aetna AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Aetna AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'BCBS Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "BlueCross AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "BlueCross AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "BlueCross AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'CHAMPVA Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Champva AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Champva AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Champva AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'CHARITY Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Charity AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Charity AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Charity AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'CIGNA Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Cigna AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Cigna AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Cigna AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'CONTRACTED Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Contracted AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Contracted AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Contracted AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'HUMANA Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Humana AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Humana AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Humana AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'MEDICAID Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Medicaid AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Medicaid AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Medicaid AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'MEDICARE Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Medicare AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Medicare AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Medicare AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'Tricare Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Tricare AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Tricare AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "Tricare AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'UHC Reports[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "United AR Sum By Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "United AR by FSC", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "United AR by FSC by Rej", "C:\Documents and Settings\" & User & "\Desktop\DeepDiveReports\" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Reports.xls", True[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]DoCmd.SendObject , , , "swoodring@ameripath.com", "bconner@ameripath.com", , "Group" & " " & GrpNumber & " " & RefLoc & " " & "Deep Dive Reports are Complete", , False[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Exit Sub[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]' If Error occurs send out an email with Error number and Description[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]SendError:[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]DoCmd.SendObject , , , "swoodring@ameripath.com", "bconner@ameripath.com", , "Error Occurred with Group" & " " & GrpNumber & " " & RefLoc & " " & "DeepDive Report Exports to Excel", Err.Number & " " & Err.Description, False[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]End Sub[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
 
Sounds like incompatible references. Be sure that you have the right EXCEL library loaded via references. (Open a code window. Use Tools >> References, find the .DLL file you need in the scroll-list, check the box, close the code window.) If I recall correctly, there were a couple of header format changes between XL2003 and XL2007. So be sure that you have the newest references in Access to enable this feature.
 
You must log in or register to reply here.

Similar threads

S
Annoying "External table is not in the expected format" message
Replies
17
Views
3,149
Isaac
Isaac
D
DoCmd.TransferSpreadsheet for exporting table from another Access database than the current one
Replies
8
Views
4,947
Pat Hartman
P
J
Exporting specific fields from from results using SQL query is showing all fields.
Replies
1
Views
1,131
pbaldy
pbaldy
P
Apply Table Formatting in Excel Export
Replies
3
Views
980
pastelrain
P
P
Applying Table Formatting in Excel Export
Replies
4
Views
706
pastelrain
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom