Import Several excel files into Several Access Tables

[ryguy7272]

I found this little snippet of code on the web:

Option Compare Database
Option Explicit


Function fimportAllFiles()
Dim strFileName As String
strFileName = "a" 'Need to set file name to a non-blank value so we go into
the loop.

Do While strFileName <> ""
strFileName = Dir("C:\Documents and Settings\ThinkPad\Desktop\Import\*.xls")
If strFileName <> "" Then 'A file was found
'MsgBox strFileName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "MyTable",
strFileName, True
End If
Loop

MsgBox "Done"
End Function

I tried to run it and it fails on this line:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "MyTable",
strFileName, True


I was hoping to use this to import several Excel files into a few Access
Tables. I’ve used the TransferSpreadsheet method several times, always with
great success. I am just wondering if I can import data from an Excel file
into a generic Access table, without naming all the fields in the Table, but
rather letting the fields be named on the fly (automatically) as the data is
imported from Excel. Would all fields import as Text data type? Does this
violate the principle of data integrity in Access?

The reason that I ask is because I will try to get a system at work to spit
out 2, 3, or 4 Excel files, and these files may have different numbers of
records in Columns in Excel. It’s going to be hard to set up in Access
Tables in advance, with the exact Fields, if I choose slightly different
download criteria for my Excel sheets. I just wanted to get a few Excel
files, place them in a folder, and then import all the files in that folder,
each Excel files going into a different Access table. If I have to set up
generic Tables in Access, how do I do that? Just name the first Field and
use data type Text. Will Access build additional fields to accommodate all
the columns in Excel?

I’d appreciate any help with this.


Regards,
Ryan---

 

[Ken Snell \(MVP\)]

See Import Data from All EXCEL Files in a single Folder via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFolderFiles

In your code, strFileName must contain the entire path; your code sets it to
just the filename.

 

[ryguy7272]

Thanks for the reply Ken. That site looks like a tremendously useful
resource; I just bookmarked it. Unfortunately, I am doing something wrong.
I have a form with a CommandButton on it. The button is named ‘Command0’.
This is linked to this code:

Private Sub Command0_Click()
Call fimportAllFiles
End Sub

And that calls this:

Option Compare Database
Option Explicit


Function fimportAllFiles()

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

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents and Settings\ThinkPad\Desktop\Import"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

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

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop
MsgBox "Done"
End Function


When I run the code, I get a message that says ‘Done’ but when I look at the
Table, named MyTable, nothing has been imported.

I am assuming that the error is with the design of MyTable. In design view,
I have a field Name of ‘Field1’ and the Data Type is ‘Text’. I have no other
fields in this Table; I was hoping I could get Access to import the field
names, as well as the data, from Excel. What am I doing wrong?


Ryan---

 

[ryguy7272]

Is there a way to build tables on the fly, to accomodate any tape of data
(probably in Access, set up as Text type of Memo type) which comes from
Excel? can I import each Spreadsheet into a disposable table and then run a
query on these tables? Thanks!!

Ryan---

 

[Ken Snell \(MVP\)]

You're missing a \ character at the end of this code's building of the path
string:

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

Change this line of code to this:

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

 

[Ken Snell \(MVP\)]

Yes, you can build temporary tables. However, unless you have some idea of
the table's structure, writing a query against that "unknown" table
structure will require you to figure out (by code) the structure of the
table and then (by code) to build a query to read that table's data. Do you
really need / want this much flexibility, which will require a lot of code?

One easy way to build a temporary table is to use the TransferSpreadsheet
action itself to make a new table. Just specify a table name for the "Table"
argument that does not already exist in the database; TransferSpreadsheet
will create the table for you. However, then writing a query against that
table will require you to know the table's structure -- field names, field
data types, field count, etc.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[ryguy7272]

Wow!! That is powerful!! Thanks so much Ken!!!!! I've been programming
Excel for a while now; pretty new to Access though. Sometimes I feel
inhibited in the Access environment and maybe this prevented me from seeing
that I was obviously missing the backslash character. Anyway, thanks for
your guidance and thanks for all the insight today.

Regards,
Ryan---