External Table is Not in the Expected Format

[tamer64]

I am having a problem with the following script when I try to transfer an Excel workbook to an Access Table I recieve the following error [red]"External Table is Not in the Expected Format"[/red]. The script will only works if the excel workbook is open. I am using Access/Excel 2007 version and the workbook has an extension of xlsm.

Could this be a problem with acSpreadsheetTypeExcel12? I need to be able to tranfer the data without having to have the excel workbook open. How do I fix this?

[blue]
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim rs, intMaxCol

Set rs = CurrentDb.OpenRecordset("Inventory", dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast:
End If

' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")

' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "C:\New Folder\Inventory.xlsm"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Inventory", sFullPath, False, "Sheet1 Totals!A1:F20"
End

With oXL
.Visible = False
.Workbooks.Open (sFullPath)
End With

ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
[/blue]

 

[UnsolvedCoding]

It looks like the problem is coming because you are telling the computer the workbook must be open when you say .workbooks.Open (sFullPath).

Try commenting out this code and see if it works.

With oXL
.Visible = False
.Workbooks.Open (sFullPath)
End With

If memory serves you don't need this at all because your path is already identified in the line above it

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Inventory", sFullPath, False, "Sheet1 Totals!A1:F20"

 

[tamer64]

I tried commenting out the code but there is no change. Any other suggestions.

 

[PHV]

Isn't the culprit here ?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Inventory", sFullPath, False, "[!]Sheet1 Totals!A1:F20[/!]"

Can you import the table manually (with no excel open) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tamer64]

I found the problem. The excel spreadsheet I was trying to import was password protected. Once I unprotected the workbook it imported just fine. Thanks.