Importing Excel tables to Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin - 14 Jul 2006 21:07 GMT

Hi, All:

I am currently have hundreds excel tables with same colum IDs, is there an
easy way to import these tables into Access and combined them into one table?

Signature

Thanks a lot!

Reply to this Message

Klatuu - 14 Jul 2006 21:49 GMT

To make your life as easy as possible, move all the excel files to the same
directory. Then you can use the Dir() function to read through all the Excel
files in the directory and do an import on each.

One problem you will have is that when you import a spreadsheet into a
table, it overwrites the data in the table, so you will have to add a little
code to make each new spreadsheet append to the Access table.

The technique I prefer is to link to the spreadsheet rather than import it.
Then use an append query to add the records in the linked excel sheet to the
Access table.

Here is some UnTested aircode that will give you an idea:

Sub DoImports
Dim strPath as string
Dim strFileName as string

strPath = "F:\SomeDirectory\"
strFileName = Dir(strPath & "*.xls")
Do While Len(strFileName) <> 0
DoCmd.TransferSpreadsheet acLink, , "TempTable", strPath &
strFileName, True
CurrentDb.Execute("MyAppendQuery"), dbFailOnError
Docmd.DeleteObject acTable, "TempTable"
strFileName = Dir()
Loop

strFileName = Dir

> Hi, All:
>
> I am currently have hundreds excel tables with same colum IDs, is there an
> easy way to import these tables into Access and combined them into one table?

Reply to this Message

Kevin - 14 Jul 2006 23:09 GMT

Thanks, but I am VBA idiot, can you tell me what code I should add in order
to append all the improted tables (more than 1500 tables) to one?
Thanks

Signature

Thanks a lot!

> To make your life as easy as possible, move all the excel files to the same
> directory. Then you can use the Dir() function to read through all the Excel
[quoted text clipped - 30 lines]
> > I am currently have hundreds excel tables with same colum IDs, is there an
> > easy way to import these tables into Access and combined them into one table?

Reply to this Message

Kevin - 14 Jul 2006 23:14 GMT

what kind of code I should add on in order to prevent the same table being
imported repeatly?

Reply to this Message

Klatuu - 18 Jul 2006 13:53 GMT

Sorry to be so slow, I was out yesterday.

The code I originally posted will take care of all your questions. Here it
is again with comments added:

Sub DoImports
Dim strPath as string
Dim strFileName as string

'This should be the path to the directory where the spreadsheet files are
strPath = "F:\SomeDirectory\"
'This will cause the Dir function to return only excel files
strFileName = Dir(strPath & "*.xls")
'After the Dir function has returned all the matching file names, it returns
a zero
'length string ""
Do While Len(strFileName) <> 0
'Links to the file name returned by the Dir function. The table name will be
'TempTable
DoCmd.TransferSpreadsheet acLink, , "TempTable", strPath &
strFileName, True
'MyAppendQuery should be the name of the query that read from TempTable and
'Appends the records to your production table
CurrentDb.Execute("MyAppendQuery"), dbFailOnError
'Deletes the link, but not the file.
Docmd.DeleteObject acTable, "TempTable"
'Gets the next file name to process
strFileName = Dir()
'Repeats the process until Dir reutrns "" meaning there are no more *.xls
files
Loop

Where you put the code depends on you application. Usually, it would be in
the Click event of a command button on a form.

> what kind of code I should add on in order to prevent the same table being
> imported repeatly?

Reply to this Message

Kevin - 18 Jul 2006 15:55 GMT

Error 3708, The microsoft jet database can not find the input table or query
"My AppendQuery"

Reply to this Message

Klatuu - 18 Jul 2006 16:00 GMT

That is not the real name.

'MyAppendQuery should be the name of the query that read from TempTable and
'Appends the records to your production table

Change it to the name of the query you created for this.

> Error 3708, The microsoft jet database can not find the input table or query
> "My AppendQuery"

Reply to this Message

Kevin - 18 Jul 2006 18:52 GMT

Thanks and it works now. However, is it possible to let the program rememer
the files name so that it can automatically skip the file already been
inputed? FYI, some people might just input the new table into the same
folder, so as I ran the same program again, it read and input the old table
to the production again.

Signature

Thanks a lot!

> That is not the real name.
>
[quoted text clipped - 5 lines]
> > Error 3708, The microsoft jet database can not find the input table or query
> > "My AppendQuery"

Reply to this Message

Klatuu - 18 Jul 2006 19:32 GMT

You would need to create a table that would hold the names of the files that
have been imported and check the table before you do the TransferSpreadsheet.
If you find the name in the table, don't import it. If you don't find the
name, import the file and add the file name to the table.

Sub DoImports
Dim strPath as string
Dim strFileName as string

strPath = "F:\SomeDirectory\"
strFileName = Dir(strPath & "*.xls")
Do While Len(strFileName) <> 0
If IsNull((DLookup("[FileName]", "tblImportedFiles", "[FileName = '"
& strFileName & "'")) Then
DoCmd.TransferSpreadsheet acLink, , "TempTable", strPath &
strFileName, True
CurrentDb.Execute("MyAppendQuery"), dbFailOnError
Docmd.DeleteObject acTable, "TempTable"
CurrentDb.Execute("INSERT INTO tblImportedFiles( [FileName],
[ImportDate] ) Values ('" & strFilename & "', #" & Date & "#);"),
dbFailOnError
End If

strFileName = Dir()
Loop

> Thanks and it works now. However, is it possible to let the program rememer
> the files name so that it can automatically skip the file already been
[quoted text clipped - 11 lines]
> > > Error 3708, The microsoft jet database can not find the input table or query
> > > "My AppendQuery"

Reply to this Message

Kevin - 18 Jul 2006 20:05 GMT

one more, is it possible to read the files in the sub folders?

Signature

Thanks a lot!

> You would need to create a table that would hold the names of the files that
> have been imported and check the table before you do the TransferSpreadsheet.
[quoted text clipped - 37 lines]
> > > > Error 3708, The microsoft jet database can not find the input table or query
> > > > "My AppendQuery"

Reply to this Message

Klatuu - 18 Jul 2006 21:36 GMT

You will have to start a new loop with the Dir function using a path name to
the sub directory for each subdirectory.

> one more, is it possible to read the files in the sub folders?
>
[quoted text clipped - 39 lines]
> > > > > Error 3708, The microsoft jet database can not find the input table or query
> > > > > "My AppendQuery"

Reply to this Message

JIM - 26 Apr 2007 17:52 GMT

Reply to this Message

JIM - 26 Apr 2007 17:56 GMT

I am trying to do the same as mentioned in your response to import excel
tables into access. Here is my code:
Private Sub LoadNewWorkOrders_Click()
Dim strPath As String
Dim strFileName As String

strPath = "C:\Documents and Settings\CMC\New Work Orders\" 'Path to
directory where new w/os are
strFileName = Dir(strPath & "*.xls") 'Dir
function returns only Excel files

Do While Len(strFileName) <> 0 'After Dir
function returns all .xl files, it returns a zero length string ""
If isNull((DLookup("[FileName]","Work Orders","[FileName="' &
strFileName&"'"))Then
DoCmd.TransferSpreadsheet acLink, , "TempTable", strPath & strFileName, True
CurrentDb.Execute ("AppendQuery"), dbFailOnError
DoCmd.DeleteObject acTable, "Temptable"
CurrentDb.Execute("INSERT INTO Work
Orders([Filename],[ImportDate])Values("'&strFilename&'",#"&Date&"#0;"),dbFailOnError
End If

strFileName = Dir()
Loop

End Sub

The if Is Null and the CurrentDb.Execute lines do not compile. I have
something in the syntax wrong but can't find what is wrong. Can you help me
please?
Thanks, JIM

Reply to this Message

snowiii - 27 Feb 2008 19:32 GMT

Hi:

I tried this coding but after importing one file it comes back with an
error, "The database engine could not lock table "tempTable' because it is
already in use by another person or process"....Any suggestions? (Here is
the code)

Sub DoImports_Click()
Dim strPath As String
Dim strFileName As String

'This should be the path to the directory where the spreadsheet files are
strPath = "X:\Tom Snow\Temp\"
'This will cause the Dir function to return only excel files
strFileName = Dir(strPath & "*.xls")
'After the Dir function has returned all the matching file names, it returns
a zero
'length string ""
Do While Len(strFileName) <> 0
'Links to the file name returned by the Dir function. The table name will be
'TempTable
DoCmd.TransferSpreadsheet acLink, , "TempTable", strPath &
strFileName, True
'MyAppendQuery should be the name of the query that read from TempTable and
'Appends the records to your production table
CurrentDb.Execute ("TempTbltoMainTbl"), dbFailOnError
'Deletes the link, but not the file.
DoCmd.DeleteObject acTable, "TempTable"
'Gets the next file name to process
strFileName = Dir()
'Repeats the process until Dir reutrns "" meaning there are no more *.xls
files
Loop
End Sub

Reply to this Message

snowiii - 27 Feb 2008 20:27 GMT

Never mind...I figured out my error...

Thanks!

> Hi:
>
[quoted text clipped - 72 lines]
> > > > > Error 3708, The microsoft jet database can not find the input table or query
> > > > > "My AppendQuery"

Reply to this Message