INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

External Table is Not in the Expected Format

External Table is Not in the Expected Format

(OP)
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 "External Table is Not in the Expected Format". 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?


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

RE: External Table is Not in the Expected Format

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: Microsoft: Access Modules (VBA Coding)

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):

CODE

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 'Excel Row
  Dim y as Integer 'Excel Column (and related to field number in Access)

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

  'Assuming Data (not column headers) begins on Row 2
  'Assuming Data begins in Column A in Excel worksheet

  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

'Cleanup
  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

RE: External Table is Not in the Expected Format

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close