matheinjoe
asked on
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
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
ASKER
The Value in Me.txtFileName is the path to my file.
can you post the value? "c:\myfolder\myexcel.xls"
is this happening to all other files that you select ?
is this happening to all other files that you select ?
ASKER
Me.txtFileName = "C:\Documents and Settings\jmathei\Desktop\O
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Same Error
from vba code window
DEBUG >Compile
did you get an error ? if yes, paste the error message and pertinent info
DEBUG >Compile
did you get an error ? if yes, paste the error message and pertinent info
ASKER
No Error Message When I Select Debug>Compile.
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
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
ASKER
That didn't work either.
can you attach your excel file and db.
check the Attach File below
check the Attach File below
ASKER
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?
the codes are good...
what is the value in Me.txtFileName?