Import multiple ranges from EXCEL to multiple tables

[MikeF]

Hello,

Need to set up a VBA sub-routine in Access that will import seven named
ranges [Rng1 thru Rng7] from one Excel workbook into seven different tables
in Access [tbl1 thru tbl7].

*** There are numerous Excel workbooks. All contain the same range names
though. ***

The seven tables in Access are always the same, and have been constructed to
accept the named ranges from those Excel workbooks without errors.

If anyone assist, it would be much appreciated.
Although I'm at an average level with VBA in Excel, am very new to it in
Access.

Thanx in advance.

Regards,
- Mike

 

[fredg]

Hello,

Need to set up a VBA sub-routine in Access that will import seven named
ranges [Rng1 thru Rng7] from one Excel workbook into seven different tables
in Access [tbl1 thru tbl7].

*** There are numerous Excel workbooks. All contain the same range names
though. ***

The seven tables in Access are always the same, and have been constructed to
accept the named ranges from those Excel workbooks without errors.

If anyone assist, it would be much appreciated.
Although I'm at an average level with VBA in Excel, am very new to it in
Access.

Thanx in advance.

Regards,
- Mike

Look up the TransferSpreadsheet method, and it's arguments, in VBA
help. You may need to change the False to True in the below code.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"YourAccessTableName", "c:\MyFolder\SpreadsheetName1.xls", False,
"SheetName!A3:C12"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"YourAccessTableName", "c:\MyFolder\SpreadsheetName2.xls", False,
"SheetName!A3:C12"

etc... importing each worksheet, one at a time.

substitute your table name, path and file names, as well as the
worksheet and range.

 

[MikeF]

Fred,
Thanx for the reply.

Is there any way to initiate those commands, where it would bring up a
"browse to spreadsheet" dialog box, I would navigate to and select the
desired sheet, then the routine would handle all seven imports consecutively
without asking again?

Regards,
- Mike

Hello,

Need to set up a VBA sub-routine in Access that will import seven named
ranges [Rng1 thru Rng7] from one Excel workbook into seven different tables
in Access [tbl1 thru tbl7].

*** There are numerous Excel workbooks. All contain the same range names
though. ***

The seven tables in Access are always the same, and have been constructed to
accept the named ranges from those Excel workbooks without errors.

If anyone assist, it would be much appreciated.
Although I'm at an average level with VBA in Excel, am very new to it in
Access.

Thanx in advance.

Regards,
- Mike

Look up the TransferSpreadsheet method, and it's arguments, in VBA
help. You may need to change the False to True in the below code.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"YourAccessTableName", "c:\MyFolder\SpreadsheetName1.xls", False,
"SheetName!A3:C12"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"YourAccessTableName", "c:\MyFolder\SpreadsheetName2.xls", False,
"SheetName!A3:C12"

etc... importing each worksheet, one at a time.

substitute your table name, path and file names, as well as the
worksheet and range.

 

[Piet Linden]

Fred,
Thanx for the reply.

Is there any way to initiate those commands, where it would bring up a
"browse to spreadsheet" dialog box, I would navigate to and select the
desired sheet, then the routine would handle all seven imports consecutively
without asking again?

Regards,
 - Mike

You could specify the directory using the BrowseFolder API or the file
using the OpenSaveFile API.

 

[MikeF]

Piet,

Thank you for the reply.

I'm uncertain exactly how to do that.
Could you provide an example?

Regards,
- Mike

 

[Piet Linden]

Piet,

Thank you for the reply.

I'm uncertain exactly how to do that.
Could you provide an example?

Regards,
 - Mike

You can use the OpenFileAPI to browse for Excel files. you can find
an example at http://www.mvps.org/access/api/api0001.htm

You can also use it to select multiple files at a time. But if all
the files you want are in a single directory, you can use BrowseFolder
and then just process all the XL files in it. Just use Dir

 

[MikeF]

Thank you, will give it a go.

You can use the OpenFileAPI to browse for Excel files. you can find
an example at http://www.mvps.org/access/api/api0001.htm

You can also use it to select multiple files at a time. But if all
the files you want are in a single directory, you can use BrowseFolder
and then just process all the XL files in it. Just use Dir