Importing Data from Excel Spreadsheet/Text files

DanAuber · May 9, 2000

I'm using macros to import Excel and Text files into Access, using pre-saved parameters. Unfortunatley I'm finding it difficult to train the people loading the data to give the files the names I am expecting. Is there an easy way to make the import process prompt for file names - at which point users would have to type in the full path and file name of the file they wished to load. Thereafter the load would use the predefined parameters. Any help gratefully received Dan Auber <a href=mailto

anAuber@aol.com>DanAuber@aol.com</a> <a href=

http://members.aol.com/_ht_a/danauber/myhomepage/>Dan

Auber's Home Page</a>

famousb · May 10, 2000

Dan, I have the same issues myself.  I require people to report certain information to me in a certain format (which i'm lucky to have them get right), and i like to have a certain name to the file for the same reason. However, even through the use of VB i've not been able to find a way for the user to get prompted for the pathname. but i am a self taught VB hacker, and i'm sure that someone out there has a way to do it.  It could be much like calling up the import wizard window to prompt for file location. If you ever figure it out, i'd love to know how you got it to work. Best of luck. Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

famousb · May 10, 2000

I'm sorry, i just figured it out.  check this out: Dim Message, Default, MyValue As String     Dim Title     Message = "Enter the Excel File name including drive

ath" 'Set Prompt     Default = ""     MyValue = InputBox(Message, Title, Default)     Docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel97,"YourTableName",MyValue put this in the OnClick property of the button (or whateevr you use) to start the macro (from the build function choose Code Builder).  change "YourTableName" to the name of the table you want the data to go into.  make sure you keep the quotes. that is for an Excel spreadsheet, for a text file replace the last line with: DoCmd.TransferText acImportFixed, "YourSpecName", "YourTableName", MyValue this should work for you. Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

DanAuber · May 10, 2000

Thanks for that Brian How does the VB know to use the correct import specification (in text import in particular - the spec holds data separator and field formats) Dan Dan Auber <a href=mailto

anAuber@aol.com>DanAuber@aol.com</a> <a href=

http://members.aol.com/_ht_a/danauber/myhomepage/>Dan

Auber's Home Page</a>

DanAuber · May 10, 2000

Sorry - Brian - it was staring me in the face..... Dan Auber <a href=mailto

anAuber@aol.com>DanAuber@aol.com</a> <a href=

http://members.aol.com/_ht_a/danauber/myhomepage/>Dan

Auber's Home Page</a>

famousb · May 10, 2000

No problem Dan, actually here is something else really cool you might want to try if your users generally have a set number of files they need to import (say three):     Dim Message, Default As String     Dim MyValue1, MyValue2, MyValue3 As String     Dim Title     Message = "Enter the file name and path." 'Set Prompt     Default = ""     MyValue1 = InputBox(Message, Title, Default)     MyValue2 = InputBox(Message, Title, Default)     MyValue3 = InputBox(Message, Title, Default)     DoCmd.TransferText acImportFixed, "YourSpecName", "YourTableName", MyValue1     DoCmd.TransferText acImportFixed, "YourSpecName", "YourTableName", MyValue2     DoCmd.TransferText acImportFixed, "YourSpecName", "YourTableName", MyValue3 tihs way they can put them all in, and then walk away while they are waiting. Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

sherree · May 10, 2000

Hi Dan, My name is Sherree I am almost having the same problem you're having, I wrote the sample code Brian showed you.  It does not work you have to specify the file name and enclose it with quotes.  I am trying  to import multiple excel worksheets into an Access table. This code works but can only import one  file at a time. DoCmd.TransferSpreadsheet acImport, 8, "Employees", "A:\Excel\EncounterForms.xls", True DoCmd.Close 8 is the code for Excel97 you can write this instead of 8 acSpreadsheetTypeExcel 97 True  allows you to use the first row of the spreadsheet as field names You can also specify the range of the excel spreadsheet by typing "A1:F20" after True if you leave it blank as I did it will read all files. "Employees" is the name of the table in access I hope this was useful, If you have any suggestions for me, I greatly appreciate it.

DanAuber · May 10, 2000

Thanks Guys Dan Auber <a href=mailto

anAuber@aol.com>DanAuber@aol.com</a> <a href=

http://members.aol.com/_ht_a/danauber/myhomepage/>Dan

Auber's Home Page</a>

harryrich · May 10, 2000

When exporting an Excell spreadsheet from VB I found that if you omit the file name in the call Access gives the user a dialog box to select the folder and override the default output file name taken from the table or query.  You might see if the import works as well.

sherree · May 11, 2000

Thanks harryrich, I 've tried your advice, but it does not work.  I get this error message: The action or method requires a File Name argument. You tried to use the TransferSpreadsheet or TransferText action or method. In the File Name argument, enter a file name.

famousb · May 11, 2000

sheree, are you saying that you can still not get the import text to work for you? This code does work Dim Message, Default, MyValue As String     Dim Title     Message = "Enter the Excel File name including drive

ath" 'Set Prompt     Default = ""     MyValue = InputBox(Message, Title, Default)     Docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel97,"YourTableName",MyValue you just need to replace "YourTableName" with the name of the table that you want to import the data into. I've used it several times, and i've also used the TransferText method several times. Actually, i just finished figuring out some code for it to link the text file, then run an append query  (actually done through SQL - it was my first true SQL trial) to append a random sample of data to an existing Access table. If you have trouble getting the above statement to work let me know, i'd be glad to help (the prompt for file name of it was yours to begin with anyway) Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

sherree · May 11, 2000

Brian, I've tried you're advice once more.  And guess what it works! Thank you very much

Now I have to try an excel file that has multiple spreadsheets to import.

famousb · May 11, 2000

i'm not sure how to designate which spreadsheet from an Excel file to import, but i'm willing to bet it's where you put the range. ie. "Sheet1:A1:F12" but don't quote me on that part. i'd be interested to know if you figure it out. Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

DuncanToms · Jan 11, 2001

I am trying to import excel spreadsheets into an existing access table , but keep getting the error:

"an error has occured trying to import the file d:\ .... .xls. The file was not imported"

1. The access table is not keyed,
2. The access table fields are of length 255 and text
3. The excel field are all text, and are of length 5 , 10, 10.

prachisharma · Jun 29, 2004

Hi,

Any idea how to do the same thing from VB code. The Docmd function is not available in VB.Its working from access but i wanna do it from my VB application.

thanks..
Pr

dbrackeen · Apr 29, 2005

I know this has been asked to death, but I know nothing about VB and am trying to get these to work.

Basically I need to import a spreadsheet everyday into access. The spreadsheet has a diff name everday (for the date) and every month it is put into a new folder on the network. I would like to be able to choose the file to be imported and cannot get the suggestions made on the forum to work.

Does anyone have something new to report on this? We are using Access 2002. Typing the entire path everyday would also be a hassle because it is embedded about 8-10 folders deep.

Thank you!!!
Dianna

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Importing Data from Excel Spreadsheet/Text files

DanAuber

IS-IT--Management

famousb

Programmer

famousb

Programmer

DanAuber

IS-IT--Management

DanAuber

IS-IT--Management

famousb

Programmer

sherree

Technical User

DanAuber

IS-IT--Management

harryrich

Programmer

sherree

Technical User

famousb

Programmer

sherree

Technical User

famousb

Programmer

DuncanToms

Programmer

prachisharma

Programmer

dbrackeen

MIS

Similar threads

Part and Inventory Search

Sponsor