Run time error 2391 when exporting Access table using VBA in Excel

[hoyinc]

Hi have been looking at a bunch of threads on the net and none of them
have seem to provide a good work around for my problem.

I am writing a module in Excel that will open an ADO connection with
Access and export a table into an excel spreadsheet. However the
program always faults at the Docmd.transferspreadsheet section with a
runtime error 2391 "F8 field destination field not at spreadsheet", the
fields in my destination xls are identical to the fields in the access
table that I am exporting from.

Here is my code:

Sub test()
Dim AccessObj As Object
Dim Accessdb As String, MonthlyGL_excel As String

MonthlyGL_test = "G:\FP&A\2006\Monthly Analysis\Reports By
Division\MonthlyGL_test.xls"
Accessdb = "G:\FP&A\2006\Monthly Analysis\Reports By Division\Monthly
Noninterest Division Report.mdb"

Set AccessObj = CreateObject("Access.Application")
With AccessObj
.opencurrentdatabase Accessdb
.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"MonthlyGL_export", MonthlyGL_test, True
.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Div_Lookup", MonthlyGL_test, True
.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Subcat_Lookup", MonthlyGL_test, True
.DoCmd.Close acForm, Accessdb, acSaveno
End With
End Sub

Any help would be appreciated

Thanks

HY

 

[John Nurick]

IME this always indicates some issue with the field names. Is there
anything unusual about the field names, especially the name of the 8th
field (e.g. does it contain any spaces or special characters?)?