Import all worksheets from excel file to access table

[foomanlinux]

I use this command to import an excel file located at PathFileName.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "cashrf", PathFileName, True

But when i do so it only imports the first worksheet, how can I import all worksheets?

 

[Philem]

Hullo, and welcome to the board! One assumes there are many sheets in the CASHRF book that you need to deal with. Going on that assumption:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,"cashrf", PathFileName, "sheet1!A1:IV65535"

Adjust the section saying SHEET1 and the range to suit your needs. You may want to declare a variable for the sheet name, and loop the code until you reach the last sheet in the book, or last element in the array.

HTH

P

 

[foomanlinux]

is sheet1 one and example or does it represent the first sheet in the excel file.
could i then use sheet2 for the second?

 

[Philem]

Sheet1 was used for purposes of illustration. Use the actual sheet name.

HTH

P

 

[foomanlinux]

Thats the problem. They may change. is there a way to incrument though them or just have them all imported?

 

[Philem]

Just declare an array, plug in the sheet names, and instead of Sheet1, reference the array.

Bye.

P

 

[foomanlinux]

I understand that, and thank you for your help so far. But the problem is i have about 30 files to import and the people who created the files use different sheet names here and there, and I would like to keep this automated so that I don't have to know the names of the sheets, and even if i did they may be different for each excel file. All i know is that each time there is 4 of them.

 

[Philem]

Hrm. I'm sure somebody will come up with something faster than I will, but, I'll think on this over the weekend. I'd recommend, at this point, using Access as an automation agent. That way, Access could open the workbook, loop through each sheet in the book scrapping the info you need, then move to the next sheet. All this is very possible, but, a bit long-winded to explain. You can do a search on the 'net for Access Automation, and come up with some help. As I said, I'll think on this over the weekend, and post back on Monday.

<wave> Off to the departmental picnic, I am! Have a great weekend!



P

 

[foomanlinux]

Thanks for your help. I think i found a function that will pull the sheet names. I could use this with the info you have given me. If it works i will post the function.