Importing Excel tables to Access Kevin 14.07.2006 20:07:02 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? -- Thanks a lot!

RE: Importing Excel tables to Access Klatuu 14.07.2006 20:49:01 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 "Kevin" wrote: [Quoted Text] > 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? > -- > Thanks a lot!

RE: Importing Excel tables to Access Kevin 14.07.2006 22:09:01 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 -- Thanks a lot! "Klatuu" wrote: [Quoted Text] > 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 > > "Kevin" wrote: > > > 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? > > -- > > Thanks a lot!

RE: Importing Excel tables to Access Kevin 14.07.2006 22:14:02 what kind of code I should add on in order to prevent the same table being imported repeatly?

RE: Importing Excel tables to Access Klatuu 18.07.2006 12:53:01 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. "Kevin" wrote: [Quoted Text] > what kind of code I should add on in order to prevent the same table being > imported repeatly?

RE: Importing Excel tables to Access Kevin 18.07.2006 14:55:03 Error 3708, The microsoft jet database can not find the input table or query "My AppendQuery"

RE: Importing Excel tables to Access Klatuu 18.07.2006 15:00:01 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. "Kevin" wrote: [Quoted Text] > Error 3708, The microsoft jet database can not find the input table or query > "My AppendQuery"

RE: Importing Excel tables to Access Kevin 18.07.2006 17:52:01 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! "Klatuu" wrote: [Quoted Text] > 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. > > "Kevin" wrote: > > > Error 3708, The microsoft jet database can not find the input table or query > > "My AppendQuery"

RE: Importing Excel tables to Access Klatuu 18.07.2006 18:32:02 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" wrote: [Quoted Text] > 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! > > > "Klatuu" wrote: > > > 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. > > > > "Kevin" wrote: > > > > > Error 3708, The microsoft jet database can not find the input table or query > > > "My AppendQuery"

RE: Importing Excel tables to Access Kevin 18.07.2006 19:05:01 one more, is it possible to read the files in the sub folders? -- Thanks a lot! "Klatuu" wrote: [Quoted Text] > 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" wrote: > > > 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! > > > > > > "Klatuu" wrote: > > > > > 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. > > > > > > "Kevin" wrote: > > > > > > > Error 3708, The microsoft jet database can not find the input table or query > > > > "My AppendQuery"

RE: Importing Excel tables to Access Klatuu 18.07.2006 20:36:01 You will have to start a new loop with the Dir function using a path name to the sub directory for each subdirectory. "Kevin" wrote: [Quoted Text] > one more, is it possible to read the files in the sub folders? > -- > Thanks a lot! > > > "Klatuu" wrote: > > > 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" wrote: > > > > > 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! > > > > > > > > > "Klatuu" wrote: > > > > > > > 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. > > > > > > > > "Kevin" wrote: > > > > > > > > > Error 3708, The microsoft jet database can not find the input table or query > > > > > "My AppendQuery"