Import same Excel ranges from many workbooks

[MikeF]

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

There are *numerous* Excel workbooks in *numerous* directories.
Therefore any routine that has "import from c:\access\etc" statically
implanted in it will not work.
Note - All Excel workbooks 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.

1. The *first* thing that has to happen ...
Initiate the routine, and an "Import from which file?" dialog box comes up,
just like a regular "File Open".
Specifically this would be the "Get External Data - Excel Spreadsheet"
window in Access.

2. Then, once I chose which Excel file to import the ranges from, the vba
routine would continue, importing ranges 1 thru 7 from that file into the
current database.

Any assistance would be sincerely appreciated.
Thank you in advance.

Regards,
- Mike

 

[Ken Snell MVP]

See this article for a starting point:

Import Data from Specific Worksheets in All EXCEL Files in a single Folder
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFldWrkFiles

This code can be tweaked to have a loop for the directories where the EXCEL
files are located. Where I have the worksheet names in the code, use Range
values instead. And I believe that you will not need the trailing $
character in the TransferSpreadsheet action for the Range argument's string.

 

[MikeF]

Chose the following to try, which is "Browse to a single EXCEL File and
Import Data from that EXCEL File via TransferSpreadsheet" in your suggested
link.

Am very familiar w/Excel vba, a lot of Access vba is new to me.
Will need to fix incrementally as needed.

The following code stalls and hits the debugger at the first
"ahtAddFilterItem", error is Compile Error: Sub or Function Not Defined.


Sub ImportExcel()

Dim strPathFile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

strBrowseMsg = "Select the EXCEL file:"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strPathFile = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:=strBrowseMsg, _
Flags:=ahtOFN_HIDEREADONLY)

If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

End Sub

See this article for a starting point:

Import Data from Specific Worksheets in All EXCEL Files in a single Folder
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFldWrkFiles

This code can be tweaked to have a loop for the directories where the EXCEL
files are located. Where I have the worksheet names in the code, use Range
values instead. And I believe that you will not need the trailing $
character in the TransferSpreadsheet action for the Range argument's string.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

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

There are *numerous* Excel workbooks in *numerous* directories.
Therefore any routine that has "import from c:\access\etc" statically
implanted in it will not work.
Note - All Excel workbooks 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.

1. The *first* thing that has to happen ...
Initiate the routine, and an "Import from which file?" dialog box comes
up,
just like a regular "File Open".
Specifically this would be the "Get External Data - Excel Spreadsheet"
window in Access.

2. Then, once I chose which Excel file to import the ranges from, the vba
routine would continue, importing ranges 1 thru 7 from that file into the
current database.

Any assistance would be sincerely appreciated.
Thank you in advance.

Regards,
- Mike

 

[Ken Snell MVP]

See second paragraph of opening explanation in that article:

Generic code to browse to a single EXCEL file, and then to import the data
from the first (or only) worksheet in that EXCEL file. This generic method
uses the Windows API to browse to a single file the code for this API (which
was written by Ken Getz) is located at The ACCESS Web (
www.mvps.org/access ).
First step is to paste all the Getz code (from
http://www.mvps.org/access/api/api0001.htm ) into a new, regular module in
your database. Be sure to give the module a unique name (i.e., it cannot
have the same name as any other module, any other function, or any other
subroutine in the database). Then use this generic code to allow the user to
select the EXCEL file that is to be imported.


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Chose the following to try, which is "Browse to a single EXCEL File and
Import Data from that EXCEL File via TransferSpreadsheet" in your
suggested
link.

Am very familiar w/Excel vba, a lot of Access vba is new to me.
Will need to fix incrementally as needed.

The following code stalls and hits the debugger at the first
"ahtAddFilterItem", error is Compile Error: Sub or Function Not Defined.


Sub ImportExcel()

Dim strPathFile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

strBrowseMsg = "Select the EXCEL file:"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strPathFile = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:=strBrowseMsg, _
Flags:=ahtOFN_HIDEREADONLY)

If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

End Sub

See this article for a starting point:

Import Data from Specific Worksheets in All EXCEL Files in a single
Folder
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFldWrkFiles

This code can be tweaked to have a loop for the directories where the
EXCEL
files are located. Where I have the worksheet names in the code, use
Range
values instead. And I believe that you will not need the trailing $
character in the TransferSpreadsheet action for the Range argument's
string.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

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

There are *numerous* Excel workbooks in *numerous* directories.
Therefore any routine that has "import from c:\access\etc" statically
implanted in it will not work.
Note - All Excel workbooks 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.

1. The *first* thing that has to happen ...
Initiate the routine, and an "Import from which file?" dialog box comes
up,
just like a regular "File Open".
Specifically this would be the "Get External Data - Excel Spreadsheet"
window in Access.

2. Then, once I chose which Excel file to import the ranges from, the
vba
routine would continue, importing ranges 1 thru 7 from that file into
the
current database.

Any assistance would be sincerely appreciated.
Thank you in advance.

Regards,
- Mike