Solved

Import Excel Spreadsheet into Access via VBA

Posted on 2008-09-29
12
1,088 Views
Last Modified: 2013-11-27
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
Comment
Question by:matheinjoe
  • 6
  • 6
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

the codes are good...

what is the value in Me.txtFileName?
0
 

Author Comment

by:matheinjoe
Comment Utility
The Value in Me.txtFileName is the path to my file.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
can you post the value?   "c:\myfolder\myexcel.xls"

is this happening to all other files that you select ?
0
 

Author Comment

by:matheinjoe
Comment Utility
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
try just using the default, use this line


DoCmd.TransferSpreadsheet acImport, , "Import", Me.txtFileName, True
 
0
 

Author Comment

by:matheinjoe
Comment Utility
Same Error
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
from vba code window

DEBUG >Compile

did you get an error ? if yes, paste the error message and pertinent info
0
 

Author Comment

by:matheinjoe
Comment Utility
No Error Message When I Select Debug>Compile.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:matheinjoe
Comment Utility
That didn't work either.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
can you attach your excel file and db.

check the Attach File below
0
 

Author Comment

by:matheinjoe
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now