Easiest way to import?

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Ay ideas on the easiest way to import data from excell files. Situation:
12 laptops exporting data via excel files to floppy disks. File names will
be different that are to be imported. Right now I am using "Get external
data" , "Import" select the file and import into an existing table. This is
time comsuming. I tried to create a macro (Transfer spreadsheet) to do
this, but you have to have the exact same file name to import. I have
various file names, so it doesn't work the way I wan it to...Any Ideas,,
Maybe a form or something?...Thanks...Randy
 
Randy - you made the same post in the macros group and I replied to you
there. It's best to stay with a single group and a single thread so that all
can see/contribute while knowing all that has been posted.
------------
Depending upon how you need to "select" the file names (manually enter
the name; have ACCESS just go get all files on that disketter). You'll want
to use the TransferSpreadsheet action as the basis for the method.

Post back with more info about how you want to get the filenames.
 
Iv'e tried the Transfer Spreadsheet method, but you have to "Enter the full
path of filename to import from" next to file name. This doesn't work if I
have different file names on a disk. Iv'e tried "A:\" but that doesn't
work. The file name on the disk must match exactly. Thats my problem.
Thanks...Randy
 
OK - what you want to do is very doable, but you'll need to use VBA code
instead of a macro. If that would be acceptable, post back and then I'll
provide some ideas for you. Would you want to get all EXCEL (".xls") files
from the A drive? Or just those with "MyString" in the name?
 
I'm not sure what "MyString" in the name is, but i do want to import all
excel files on the A drive. The scenario is 6 or 7 disks each with one
excel file but each named differently...Thanks for your help...
 
Something like this (loops through all the .xls files on A drive) will
import each query into a separate table (named ImportTable1, ImportTable2,
etc.) in the database, and then you can use append queries to copy them into
a final table:

Dim strFile As String, lngCt As Long
lngCt = 0
strFile = Dir("A:\*.xls")
Do While strFile <> ""
lngCt = lngCt + 1
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "ImportTable" & lngCt, _
"A:\" & strFile
strFile = Dir()
Loop
 
Hit the return key too soon.

I am assuming that you would want to use a macro to run the code that I just
posted. As such, put the code as a public function in a regular module (name
the module basImporting):

Public Function ImportTheXLSFiles()
Dim strFile As String, lngCt As Long
lngCt = 0
strFile = Dir("A:\*.xls")
Do While strFile <> ""
lngCt = lngCt + 1
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "ImportTable" & lngCt, _
"A:\" & strFile
strFile = Dir()
Loop
End Function

Then, in your macro, use the RunCode action and put ImportTheXLSFiles as the
name of the function that is to be run.
 
Back
Top