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

End Sub
[/blue]

 

[kjv1611]

This more a VBA question than a table question. I know it's talking about the table format, but you're working in VBA.

So for future reference, this type question should go here:
forum705

Now, that said... in Excel, is your table/sheet in the correct format? For isntance, does it have anything above the header row?

If it is correct, then perhaps you should try something else. You could, for instance, create a recordset object in Access, and an Excel application object, workbook object, and worksheet object, and then basically loop through the spreadsheet, adding records to the recordset in Access. If you want to go this route, I'd suggest going ahead and just creating the table in Access, unless you need to create a new table each time. In that case, you can create the table in VBA, and then import the data. Also, Set a Reference to the Microsoft Excel Object Library

To get you started, if that's how you want to go, this is how you'd go about it (assuming the table is already there):

Sub ImportExcel
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim xlApp As New Excel.Application
  Dim wb As Excel.Workbook
  Dim ws as Excel.Worksheet
  Dim x as Long [GREEN]'Excel Row[/GREEN]
  Dim y as Integer [GREEN]'Excel Column (and related to field number in Access)[/GREEN]

  Set db = CurrentDb
  Set rs = db.("MyTable")
  Set wb = xlApp.ActiveWorkbook
  Set ws = wb.ActiveSheet  

[GREEN]  'Assuming Data (not column headers) begins on Row 2
  'Assuming Data begins in Column A in Excel worksheet[/GREEN]
  For x = 2 to ws.Range("A65536").End(xlUp).Row
    rs.AddNew 'Add New Record to recordset
    For y = 1 to ws.Range("ZZ1).End(xlToLeft).Column
      rs.Fields(y-1) = ws.Cells(x,y).Value
    Next y
    rs.Update 'Save new record to table
  Next x

[GREEN]'Cleanup[/GREEN]
  If ws Is Nothing Then Else Set ws = Nothing
  If wb Is Nothing Then Else Set wb = Nothing
  If xlApp Is Nothing Then Else Set xlApp = Nothing
  If rs Is Nothing Then Else rs.Close: Set rs = Nothing
  If db Is Nothing Then Else db.Close: Set db = Nothing

End Sub

I've not tested this, just putting it together as I type... so you may run into errors, but hopefully it'll give you an idea as to an alternative solution.

To use, paste the code in an Access Module, debug for any errors, and give it a test run... of course, renaming the objects to be accurate to your workbook and database.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Darrylles]

Hi tamer,

Is this more a problem with Windows in general?

http://support.microsoft.com/kb/924074

ATB

Darrylles

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.