Importing Excel tables to Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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
 
what kind of code I should add on in order to prevent the same table being
imported repeatly?
 
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.
 
Error 3708, The microsoft jet database can not find the input table or query
"My AppendQuery"
 
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.
 
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.
 
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
 
one more, is it possible to read the files in the sub folders?
--
Thanks a lot!


Klatuu said:
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



Kevin said:
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.
Click to expand...
Click to expand...
 
You will have to start a new loop with the Dir function using a path name to
the sub directory for each subdirectory.

Kevin said:
one more, is it possible to read the files in the sub folders?
--
Thanks a lot!


Klatuu said:
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



Kevin said:
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.
--
Thanks a lot!


:

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"
Click to expand...
Click to expand...
Click to expand...
 
Klatuu said:
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



Kevin said:
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.
Click to expand...
Click to expand...
 
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

[QUOTE="Klatuu"]
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



Kevin said:
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.
Click to expand...
[/QUOTE]
 
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

Klatuu said:
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



Kevin said:
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.
Click to expand...
Click to expand...
 
Never mind...I figured out my error...

Thanks!

snowiii said:
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

Klatuu said:
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



Kevin said:
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.
--
Thanks a lot!


:

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"
Click to expand...
Click to expand...
Click to expand...
 
Hi all,
i am a total newbbie in access and i need your help.

I am trying to use your code to import the excels.
Though since i dont know vb i would like to ask a couple of thinks.

1.How should i modify the code to import specific sells from specific
workbooks of the imported excels?
(if you could show me for one cell i may can modify it to suit my needs)

Thank you for your time reading this.
 
Back
Top