• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1103
  • Last Modified:

Import Excel Spreadsheet into Access via VBA

I am trying to import a file from Excel into Access via a command button on a form.  I keep getting the following error:
Run-time error '3274':
External table is not in the expected format.

However if I run it with the Excel File open, it works perfectly.  

Any suggestions on how to do it without having the file open?

p.s.  See attached code for my command button

Thanks,
Matheinjoe
Private Sub cmdImport_Click()
If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
    MsgBox "please select the excel file"
    Me.cmdSelect.SetFocus
    Exit Sub
End If
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import", Me.txtFileName, True
 
End Sub

Open in new window

0
matheinjoe
Asked:
matheinjoe
  • 6
  • 6
1 Solution
 
Rey Obrero (Capricorn1)Commented:

the codes are good...

what is the value in Me.txtFileName?
0
 
matheinjoeAuthor Commented:
The Value in Me.txtFileName is the path to my file.
0
 
Rey Obrero (Capricorn1)Commented:
can you post the value?   "c:\myfolder\myexcel.xls"

is this happening to all other files that you select ?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
matheinjoeAuthor Commented:
Me.txtFileName = "C:\Documents and Settings\jmathei\Desktop\OR_TEST_DATA.xls"

It is happening on every xls file.

I found something on the web that stated to change  the "acSpreadsheetTypeExcel9 to the correct version of my Excel.  My version is 11, but I get a Compile Error: Variable Not Defined when I change that.
0
 
Rey Obrero (Capricorn1)Commented:
try just using the default, use this line


DoCmd.TransferSpreadsheet acImport, , "Import", Me.txtFileName, True
 
0
 
matheinjoeAuthor Commented:
Same Error
0
 
Rey Obrero (Capricorn1)Commented:
from vba code window

DEBUG >Compile

did you get an error ? if yes, paste the error message and pertinent info
0
 
matheinjoeAuthor Commented:
No Error Message When I Select Debug>Compile.
0
 
Rey Obrero (Capricorn1)Commented:
do a decompile

http://www.granite.ab.ca/access/decompile.htm

follow the instruction from the link

then create a blank db and import all the objects to the new db
0
 
matheinjoeAuthor Commented:
That didn't work either.
0
 
Rey Obrero (Capricorn1)Commented:
can you attach your excel file and db.

check the Attach File below
0
 
matheinjoeAuthor Commented:
Ok, as I was saving you a test file, I realized the file format was Unicode Text.  I saved it as an Excel file and it works properly now.  But this brings up another issue.  How would I import a Unicode Text file?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now