Import Workbook with Multiple Sheets into Database

[elham a]

I have an Excel workbook with multiple sheets (22)! that I would like to import into Access Tables for manipulation. I'd like to load them all to the same table if possible, so long as I know what the name of the sheet the records came from originally.

Is there any systematic way of doing this?

[C CSR]

Here's the only way I know. My understanding is that you have to repeat the DoCmd on each "sheet" in the workbook. Its also tricky because:

1)The fieldnames have to match exactly if you import each sheet into the same table (and change the False to True below if you have the first row as matching fieldnames; otherwise it will use A1, B1, etc.);
2) The range doesn't work if you just specify "Sheetx" without a "!xy:xy!" part.
3) If importing to same table it appends the data, BUT: table must be prepared to handle all the fields you might import.
(ex: importing sheet1 with 3 fields creates the table. If you then try to import sheet2 with 4 fields: error; the 4th field doesn't exist.)
4) If you create the table in advance, you set the conditions by which the worksheets must adhere.

You'll have to play around with it to get it right. Good luck.

Sub spreadtest()
'FIRST SHEET
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "NewImport", "c:\MYFOLDER\TESTIMPORT.XLSX", False, "Sheet1!A1:C8"

'SECOND SHEET
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "NewImport", "c:\MYFOLDER\TESTIMPORT.XLSX", False, "Sheet2!A1:C8"
End Sub

[C CSR]

the template is:

TransferSpreads heet(TransferTy pe, SpreadsheetType , TableName, FileName, HasFieldNames, Range, UseOA)

[C CSR]

This is lengthy:

You would need to create a table with unique fields for every unrelated column of data: F1,F2,F3, etc. If some of the data from different sheets belongs in the same column then give that column on each sheet the same "Firstrow" name.
example from Workbook:
Sheet1: F1,F2,F3; Sheet2: F1,F2,F3,F4; Sheet3: F2,F3;
Scenario: Sheet2 has the all the fields corresponding to the table. Sheet1 has the first 3; Sheet3 has only the 2nd and 3rd fields. These are the only columns that the table will accept.
You can still run the code I presented earlier with this setup, however, you must set your ranges in the DoCmd arguments to precisely include only the columns the table has available. You don't have to have a particular column in the sheet (sheet3 only has F2 & F3), the datafields in the table would just be empty for those specific records as they are appended, after the import.
You have to set your ranges properly for each sheet in the DoCmd statement though (you don't have to create an empty F1 or F4 for sheet3 in the example above). If you have an F1 and an F4 you can't set the range to !A1:!D8 if there is irrelevant data in columns B or C that won't convert or does not have a heading (F2,F3). This is where it goes trial an error. You also have to consider that if you pull 2 different ranges from the same sheet they may not align as records in the table.
There's more, but the last thing I want to add before I test anything else, is that to "know" which sheet the data comes from when you're looking at the table, you need to establish a "key" column. Example:
each sheet has a column1 with a key like: Sheet1 or sheet2 that follows all the way down to the end of your data for that sheet. So, as each sheet is appended, the Column1 field would indicate its origin. (You should also create a primary key for the table itself, like an autonumber. Then your fields would be:
1) Pkey (auto) 2)Column1 (sheet) 3) F1 4) F2 5) F3 6) F4.
This is one way to do this, and there may be a shortcut here or there, aside from programming the whole thing. Keep us posted.

[C CSR]

To clarify, the table will accept F1, F2, F3 & F4 or any combination thereof from the sheets.

[NeoPa]

My inclination was to delete this question as it is so poorly asked. Even now it's quite unclear what the OP requires. However, due to the quality of some of the replies I will try to tidy it up instead (More work - deserved by C CSR rather than OP). This will likely mean rewording the original post to make as much sense of it as I can. Watch this space.

NB. In future, reporting the question instead of answering it would be preferred. That way we can encourage posters to post questions that make sense and are much easier to work with and for others to learn from.

[NeoPa]

elham a:
Is there any systematic way of doing this?

There may be, but you'd need to explain what information is available for you to work with before we could consider how to use that information to produce code that matches your situation. How do you know which sheets to import for instance?

Other information that is important and should have been included in any question you posted is whether or not the separate worksheets in the workbook all have the same columns of data.

When you know what you have to work within, you can create code to process through that (It may be a list or a Collection or whatever). You may choose to import the data successively into the same table directly, or you may choose to work through a temporary table where you check the data imported, before transferring the validated data into your eventual table.

Frankly, it's hard to help you when you haven't even managed to post a question properly to start off with.

[C CSR]

It is hit and miss with some of these questions. I would truthfully need more info to resolve the issue expediently. Will conform. Thanks NeoPa.