how to import multiple Excel files into multiple Access tables?

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I am using the following code to create an Access table on the fly, and
import an Excel file into the Access table that is created:
Function ImportExcelFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "C:\Documents and Settings\ThinkPad\Desktop\Import\"

'Name the table
strTable = "tablename"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

strFile = Dir()
Loop

MsgBox "Done with Import"
End Function



Thanks Ken!! This works fine. I am now trying to figure out how to import
multiple Excel files into multiple tables. I read, in this very discussion
group that it is possible, but I can’t figure out how to actually do it. I
tried this sample code (below) and modified it a bit:
Function ImportTextFiles()

Dim strFile As String
Dim strFolder As String
Dim strTable As String
Dim strPathFile As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = False
strTable = "tablename"

strPathFile = "C:\Documents and Settings\ThinkPad\Desktop\Import\"
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0

'strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

strFile = Dir$()
Loop


End Function

The code fires, but doesn’t actually do anything. What am I doing wrong?
Also, I would very much like to know how to import several .txt files into
several Access tables. I suspect it will be very, very, very similar to the
process of importing several .xls files, but since I can’t get that working,
I can’t test the process of importing several .txt files.

Any assistance would be greatly appreciated.

Regards,
Ryan---
 
The "trick" here is to change the value of your strTable variable before the
code imports a file. strTable should contain the table name that is to
receive the data from the file.

From where do you get a list of tables to be used? Or do you want to
manually enter the table name for each file?
 
Here is code to import all text files (.txt extension) from a single folder;
it's very similar to how the code that imports .xls files from a single
folder:


Public Sub pfImport()
On Error GoTo Err_F

Dim strPathFile as String, strFile as String, strPath as String,
strSpec as String
Dim strTable as String, ynFieldName as Boolean
ynFieldName = False
strPath = "C:\Documents\"
strSpec = "NameOfImportSpecification" ' Put your name here
strTable = "tablename"
strFile = Dir(strPath & "*.txt")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferText acImportDelim, strSpec, strTable,
strPathFile, ynFieldName
' Uncomment out the next code step if you want to delete the
file after it's imported
' Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub

Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Ken, thanks for the help yesterday and thanks again for the code today. Both
solutions worked great. One more question; final one for the weekend.
In your response above, you said 'do you want to
manually enter the table name for each file...' Yes, I would definitely
like to see the code for this. I am trying to imagine how this will run. I
suspect one way would be to use an InputBox, right. I just can't figure out
how to set it up exactly. A user could have a half dozen Excel files in a
folder, and he/she wants to import all, or the user could have a dozen Excel
files in the folder, and he/she wants to import all of those, or maybe the
user has a couple dozen Excel files in the folder... I would think you just
need to loop through all the files and import each one into a table, and name
each using some convention such as Table1, Table2, Table3, etc. Could you
help me set that up?

Thanks for everything!!
Ryan---
 
Yes, you could use an InputBox:

Function ImportExcelFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "C:\Documents and Settings\ThinkPad\Desktop\Import\"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
'Name the table
strTable = InputBox("Enter table name for file """ & strPathFile & """")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

strFile = Dir()
Loop

MsgBox "Done with Import"
End Function



Or if you want to use generic table numbering (Table1, Table2, etc.)

Function ImportExcelFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim lngCounter As Long

blnHasFieldNames = True
'Name the generic table
strTable = "Table"
lngCounter = 0

strPath = "C:\Documents and Settings\ThinkPad\Desktop\Import\"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
lngCounter = lngCounter + 1


' Uncomment out these next code steps if you want to delete records
' from the table before you import data to it
' Dim tdf As DAO.TableDef
' Dim dbs As DAO.Database
' Set dbs = CurrentDb
' For Each tdf in dbs.TableDefs
' If tdf.Name = strTable & lngCounter Then
' dbs.Execute "DELETE * FROM " & strTable & lngCounter, dbFailOnError
' Exit For
' End If
' Next tdf
' dbs.Close
' Set dbs = Nothing


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable & lngCounter, strPathFile, blnHasFieldNames

strFile = Dir()
Loop

MsgBox "Done with Import"
End Function


With the generic table method, you'll need to be sure that the table, if it
already exists, is empty before you do the import. This can be done by
running a delete query on that table if you'd like (see optional steps I put
in the above code).
 
WOW!!! That is some super powerful code!! I will save this, and make a
backup, and put both files in a safe place.

Sorry for the delayed response; I've been tremendously busy here.


Thanks for everything Ken!!
Ryan--


BTW, do you have a book? Where can I learn this stuff? I've read several
books on Access, and skimmed through many others, and I didn't even know this
kind of stuff could be done...
 
ryguy7272 said:
WOW!!! That is some super powerful code!! I will save this, and make a
backup, and put both files in a safe place.

Sorry for the delayed response; I've been tremendously busy here.


Thanks for everything Ken!!
Ryan--


BTW, do you have a book? Where can I learn this stuff? I've read several
books on Access, and skimmed through many others, and I didn't even know
this
kind of stuff could be done...
Click to expand...


No, I have not written a book, but many others who're more knowledgeable
than I about ACCESS have. I suggest these two books as starters:

ACCESS 2003
http://www.amazon.com/Microsoft®-Of...=sr_1_1?ie=UTF8&s=books&qid=1226681934&sr=1-1

ACCESS 2007
http://www.amazon.com/Microsoft®-Of...=sr_1_1?ie=UTF8&s=books&qid=1226681973&sr=1-1

These two books have a lot of code examples that will show you some methods
similar in scope to the ones I've given to you.


I do have a website, but it's been a work in progress so far....<smile>.
See my signature for the link.

Good luck.
 
This works really well. What do I need to do to keep the name of the excel file as the name of the table in access?
 
Just the filename without the path and the ".xls" parts? Post the code that
you're using and we'll show you how to do that.
 
Back
Top