Import multiple excel spreadsheets into access

[jekenner]

I am building a trends analysis database that will be pulling its information from syslog files exported and saved as excel worksheets. Is there an easier way than importing each individual spreadsheet?
Also, there are three workbooks in each spreadsheet, is there a way to easily import all three simultaneously?
Thank you in advance.

 

[PantoKing]

I have done this previously by extracting the filenames using the DIR function, and writing these filenames (and paths) in a seperate table. Then using some code loop through the recordset (of the filename table) and pass each value that the recordset picks up to Docmd.TransferSpreadsheet. This works for me!

HTH

Steve

 

[HIM]

What about the multiple sheets in one file?
I am having problems with this. I only get the first sheet into access when using TransferSpreadsheet....

 

[PantoKing]

Hello,

The syntax for Docmd.transfer spreadsheet is
DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

You can specify the sheetname in the range. e.g. If you are transfering from a spreadsheet called C:\test.xls and have got 3 sheets within this to transfer into a table called tblTest you would use the following in your code (I'm assuming that the sheet names haven't been changed)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbltest", "c:\test.xls", -1, "sheet1!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbltest", "c:\test.xls", -1, "sheet2!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbltest", "c:\test.xls", -1, "sheet3!"

HTH

Steve.

 

[Guest_imported]

Steve I am transfering from Excel to access but i want to mention filename,sheetname and range even is it posible. Pls help me.

 

[Guest_imported]

Steve i got it we have to mention the sheetname and range in same place with ! separation
e.g

docmd.transferspreadsheet acimport,8,"t_user","c:\test1.xls" , -1 , "sheet1" & "!" & "A1:E12"

Thank you

 

[catalina]

I have used the TransferSpreadsheet function in the past to transfer pricing data from Excel to a quoting database. It worked very well when we had Office 97. I have been having problems with it ever since we have been upgraded to Office 2000. Has anyone else experienced a similar situation with the upgrade? Any suggestions?

Thanks

 

[PizMac]

I have the same problem - it works in 2000 but not in 97 - I can't import separate sheets in 97 - the sheet! option doesn't seem to work - say range not found - Help!