Import Excel ranges - clarify

[MikeF]

Posted this earlier, but now have some clarifying code below, converted from
an Access macro then modified for this example.

Previous post:
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


As follows is the converted, modfied macro.
Thinking a bit like Excel vba, which I'm much more familiar with, see
between the tilde-strings.
Believe I just need to have the "which spreadsheet" dialog box open, choose
the Excel sheet/path, and assign the MyExcelFile variable to it.
Signed - Desperate for this Solution ...!!! <g>


Function ImportExcel ()
On Error GoTo ImportExcel_Err

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT ALL CODE HERE FOR – MyExcelFile = Open “Get External Data – Get
Spreadsheet†dialog box to choose what file will become “MyExcelFile†in this
variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "MyExcelFile", True,
"Rng1"
DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

 

[Ken Snell MVP]

See reply in your original thread.

 

[MikeF]

Jim,
Thanx for the reply, but...
I do not have the most important part of the code.
Please see INSERT ALL CODE HERE.

The code I have works with *one* static Excel file.
There are hundreds of them, in different directories.

Need Access to open a "Which Excel file do you want to be the MYEXCELFILE?"
dialog box.
.... Again, the most important part of the puzzle I can't seem to piece
together.

If you have any ideas, they would be sincerely appreciated.
Thanx,
-Mike

It looks to me like you have the solution already - simply get the name of
the spreadsheet file, then execute the transferspreadsheet commands as you
have them, making sure you have the appropriate range names and table names.
Have you tried testing this? Try it out in a test DB - looks like it should
work once you get the code in to get the filename. Are you looking for the
code to get the filename? I don't use the 'standard' Access file dialog, I
use one I got somewhere else, so you wouldn't want to use mine. But your code
looks good for importing the data.

Is this code you'll be running repeatedly over time? If so, do you always
want to use the same table names? I think that if you import to an existing
table that already has data in it, it will append the data. So if you want to
overlay the data that was in the table, you'd have to run some queries to
delete the data in the tables first.

Posted this earlier, but now have some clarifying code below, converted from
an Access macro then modified for this example.

Previous post:
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

As follows is the converted, modfied macro.
Thinking a bit like Excel vba, which I'm much more familiar with, see
between the tilde-strings.
Believe I just need to have the "which spreadsheet" dialog box open, choose
the Excel sheet/path, and assign the MyExcelFile variable to it.
Signed - Desperate for this Solution ...!!! <g>

Function ImportExcel ()
On Error GoTo ImportExcel_Err

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT ALL CODE HERE FOR – MyExcelFile = Open “Get External Data – Get
Spreadsheet†dialog box to choose what file will become “MyExcelFile†in this
variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "MyExcelFile", True,
"Rng1"
DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

 

[MikeF]

Jim, thanx for the elaboration.
I'm not yet familiar with Class Modules, and need to read a bunch more.
That having been said, will attempt to piece together your suggestions and
see if I can make it happen.
Uncertain if you can see any ways to simplify or take me thru this in steps,
but do appreciate your input.
Again, thank you.
- Mike

I hadn't looked at your other post. I use the Getz code that Ken suggested.
Once you've created a new class module with the Getz code, it's simple enough
to open the file dialog. I just created a function called GetFileName:

Public Function GetFileName(ByVal filterType As String, ByVal openMsg As
String) As String

Dim cd As CommonDlg

Set cd = New CommonDlg
cd.DialogTitle = openMsg
cd.filter = filterType
cd.ShowOpen
GetFileName = cd.fileName
Set cd = Nothing

End Function

the 'filterType' argument is used for the filter - I have constants defined
for that, and when I want to get a filename I pass the appropriate filter
type

Public Const filterAccess As String = "*.mdb|*.MDB"
Public Const filterExcel As String = "*.xls|*.XLS"
Public Const filterText As String = "*.txt|*.TXT|*.csv|*.CSV"
Public Const filterCSV As String = "*.csv|*.CSV|*.txt|*.TXT"

'openMsg' is the title that will appear on the tiel bar of the open dialog. I
vary that depending on exactly what I'm retrieving the filename for. SO for
your purposes you could define the filterExcel constant, then when you call
the function use something like

myFileName = GetFileName(filterExcel, "Please select the spreadsheet")

or whatever you want for the title. If they cancelled then the function
returns a null string.

Posted this earlier, but now have some clarifying code below, converted from
an Access macro then modified for this example.

Previous post:
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

As follows is the converted, modfied macro.
Thinking a bit like Excel vba, which I'm much more familiar with, see
between the tilde-strings.
Believe I just need to have the "which spreadsheet" dialog box open, choose
the Excel sheet/path, and assign the MyExcelFile variable to it.
Signed - Desperate for this Solution ...!!! <g>

Function ImportExcel ()
On Error GoTo ImportExcel_Err

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT ALL CODE HERE FOR – MyExcelFile = Open “Get External Data – Get
Spreadsheet†dialog box to choose what file will become “MyExcelFile†in this
variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "MyExcelFile", True,
"Rng1"
DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

 

[MikeF]

Ken,
Thanx, I now have the Getz code, as pasted below.
Again, I'm ok with Excel vba, but still very much learning the Access side.
Please bear with me.
So my questions are ...
1. Will the converted macro below work for my purposes, providing the proper
code is inserted before all the imports?

2. If so, and if I put the Getz code pasted below in front of the import
section, how do I assign the variable MyExcelFile to whichever workbook I
navigate to?

Regards,
- Mike


Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

See reply in your original thread.
--

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

Posted this earlier, but now have some clarifying code below, converted
from
an Access macro then modified for this example.

Previous post:
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


As follows is the converted, modfied macro.
Thinking a bit like Excel vba, which I'm much more familiar with, see
between the tilde-strings.
Believe I just need to have the "which spreadsheet" dialog box open,
choose
the Excel sheet/path, and assign the MyExcelFile variable to it.
Signed - Desperate for this Solution ...!!! <g>


Function ImportExcel ()
On Error GoTo ImportExcel_Err

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT ALL CODE HERE FOR - MyExcelFile = Open "Get External Data - Get
Spreadsheet" dialog box to choose what file will become "MyExcelFile" in
this
variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "MyExcelFile", True,
"Rng1"
DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

 

[Ken Snell MVP]

The Getz code does not go in the same module with the other code.

From database window, select Modules. Click on New Module. A new regular
module window will open. Paste the entire Getz code into that module. Save
the module, naming it basDialog or something like that.

Now go back to the original module (I assume that this is a form module).
The code should work.

--

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

Ken,
Thanx, I now have the Getz code, as pasted below.
Again, I'm ok with Excel vba, but still very much learning the Access
side.
Please bear with me.
So my questions are ...
1. Will the converted macro below work for my purposes, providing the
proper
code is inserted before all the imports?

2. If so, and if I put the Getz code pasted below in front of the import
section, how do I assign the variable MyExcelFile to whichever workbook I
navigate to?

Regards,
- Mike


Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

See reply in your original thread.
--

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

Posted this earlier, but now have some clarifying code below, converted
from
an Access macro then modified for this example.

Previous post:
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


As follows is the converted, modfied macro.
Thinking a bit like Excel vba, which I'm much more familiar with, see
between the tilde-strings.
Believe I just need to have the "which spreadsheet" dialog box open,
choose
the Excel sheet/path, and assign the MyExcelFile variable to it.
Signed - Desperate for this Solution ...!!! <g>


Function ImportExcel ()
On Error GoTo ImportExcel_Err

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT ALL CODE HERE FOR - MyExcelFile = Open "Get External Data -
Get
Spreadsheet" dialog box to choose what file will become "MyExcelFile"
in
this
variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "MyExcelFile", True,
"Rng1"
DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

 

[MikeF]

Ok, did all that exactly.
Thank you.
We're getting there.

I get to the dialog box, choose the file [it's a .xlsm file, as you'll see
in the code below], and then get this error msg:

The MS Office Access database engine could not find the file
"D:\Access\strPathFile.XLSX". Make sure the object exists and name/path are
spelled correctly.

It seems that the code needs to assign a variable [preferably MyExcelFile],
to strFilePath for it to work. But will defer to you.

Sincerely appreciate everyone's assistance!!!!
- Mike

As follows is my code:

Function ImportExcel()
On Error GoTo ImportExcel_Err

'INSERT ALL CODE HERE FOR - MyExcelFile = Open "Get External Data - Get
Spreadsheet" dialog box to 'choose what file will become "MyExcelFile" in
this variable.

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 = True
strBrowseMsg = "Select the EXCEL file:"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xlsm)", "*.xlsm")
strPathFile = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:=strBrowseMsg, _
Flags:=ahtOFN_HIDEREADONLY)
If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
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



DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "strPathFile", True, "
Rng1 "
'DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

The Getz code does not go in the same module with the other code.

From database window, select Modules. Click on New Module. A new regular
module window will open. Paste the entire Getz code into that module. Save
the module, naming it basDialog or something like that.

Now go back to the original module (I assume that this is a form module).
The code should work.

--

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

Ken,
Thanx, I now have the Getz code, as pasted below.
Again, I'm ok with Excel vba, but still very much learning the Access
side.
Please bear with me.
So my questions are ...
1. Will the converted macro below work for my purposes, providing the
proper
code is inserted before all the imports?

2. If so, and if I put the Getz code pasted below in front of the import
section, how do I assign the variable MyExcelFile to whichever workbook I
navigate to?

Regards,
- Mike


Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

See reply in your original thread.
--

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


Posted this earlier, but now have some clarifying code below, converted
from
an Access macro then modified for this example.

Previous post:
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


As follows is the converted, modfied macro.
Thinking a bit like Excel vba, which I'm much more familiar with, see
between the tilde-strings.
Believe I just need to have the "which spreadsheet" dialog box open,
choose
the Excel sheet/path, and assign the MyExcelFile variable to it.
Signed - Desperate for this Solution ...!!! <g>


Function ImportExcel ()
On Error GoTo ImportExcel_Err

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT ALL CODE HERE FOR - MyExcelFile = Open "Get External Data -
Get
Spreadsheet" dialog box to choose what file will become "MyExcelFile"
in
this
variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "MyExcelFile", True,
"Rng1"
DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

 

[MikeF]

Jim,

Now have the Getz code properly in a separate module.

Tried your suggestion below in another module, ie pasted the whole thing in
and commented out any text.
It wants to run a macro.
Obviously I'm doing something wrong, and need some clarification on exactly
where to put what part of it.

Also, big question here ... Is it possible to run this from the open Excel
workbook?
Ie, pass the activeWorkbook file and path name to a vba variable from Excel
vba, then have Excel vba run Access' TranserSpreadsheet macros?

Thanx,
-Mike

I hadn't looked at your other post. I use the Getz code that Ken suggested.
Once you've created a new class module with the Getz code, it's simple enough
to open the file dialog. I just created a function called GetFileName:

Public Function GetFileName(ByVal filterType As String, ByVal openMsg As
String) As String

Dim cd As CommonDlg

Set cd = New CommonDlg
cd.DialogTitle = openMsg
cd.filter = filterType
cd.ShowOpen
GetFileName = cd.fileName
Set cd = Nothing

End Function

the 'filterType' argument is used for the filter - I have constants defined
for that, and when I want to get a filename I pass the appropriate filter
type

Public Const filterAccess As String = "*.mdb|*.MDB"
Public Const filterExcel As String = "*.xls|*.XLS"
Public Const filterText As String = "*.txt|*.TXT|*.csv|*.CSV"
Public Const filterCSV As String = "*.csv|*.CSV|*.txt|*.TXT"

'openMsg' is the title that will appear on the tiel bar of the open dialog. I
vary that depending on exactly what I'm retrieving the filename for. SO for
your purposes you could define the filterExcel constant, then when you call
the function use something like

myFileName = GetFileName(filterExcel, "Please select the spreadsheet")

or whatever you want for the title. If they cancelled then the function
returns a null string.

Posted this earlier, but now have some clarifying code below, converted from
an Access macro then modified for this example.

Previous post:
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

As follows is the converted, modfied macro.
Thinking a bit like Excel vba, which I'm much more familiar with, see
between the tilde-strings.
Believe I just need to have the "which spreadsheet" dialog box open, choose
the Excel sheet/path, and assign the MyExcelFile variable to it.
Signed - Desperate for this Solution ...!!! <g>

Function ImportExcel ()
On Error GoTo ImportExcel_Err

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT ALL CODE HERE FOR – MyExcelFile = Open “Get External Data – Get
Spreadsheet†dialog box to choose what file will become “MyExcelFile†in this
variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "MyExcelFile", True,
"Rng1"
DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

 

[MikeF]

Jim, no problem.
Thank you!!
I've stored both your modules.
And yes, Ken's works now, after I took the quotations off strFilePath as per
your suggestion.

And "my" code, ie the multiple TransferSpreadsheet lines, all work as well.
It's a bit of a nail-biter when it's running, as there is nothing to display
progress, but ... it certainly works.

Have to say though, it just seems like *A LOT* of complex code to merely
open a "which file?" dialog box.
Especially after the easy one or two lines in Excel vba.

One picky thing [asking for the world here!!] --- the Getz code is rather
old, before the dialog boxes had "Places" on the left, as in an easy way to
get to your much-used items.
.... Is there anyway of changing the old dialog box that comes up to the new
2007-style one that sports "Places" in the left pane???

Regards,
- Mike

Well, I messed up. I should have just let Ken handle this and not replied. I
assumed he was talking about the same Getz code that I have, but now that
I've looked at the link he posted, he's not. My Getz code goes in a class
module, and the code I posted is based on that. the code Ken pointed you to
goes in a standard module, and the stuff I posted won't work with that. I
apologize for that. Didn't mean to step on Ken's toes or point you in the
wrong direction. I haven't used that particular version of Getz code.

Jim,

Now have the Getz code properly in a separate module.

Tried your suggestion below in another module, ie pasted the whole thing in
and commented out any text.
It wants to run a macro.
Obviously I'm doing something wrong, and need some clarification on exactly
where to put what part of it.

Also, big question here ... Is it possible to run this from the open Excel
workbook?
Ie, pass the activeWorkbook file and path name to a vba variable from Excel
vba, then have Excel vba run Access' TranserSpreadsheet macros?

Thanx,
-Mike

I hadn't looked at your other post. I use the Getz code that Ken suggested.
Once you've created a new class module with the Getz code, it's simple enough

[quoted text clipped - 104 lines]

End Function

 

[Ken Snell MVP]

I see in other subthread that you have got the code to work. That's good;
good luck.

--

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

Ok, did all that exactly.
Thank you.
We're getting there.

I get to the dialog box, choose the file [it's a .xlsm file, as you'll see
in the code below], and then get this error msg:

The MS Office Access database engine could not find the file
"D:\Access\strPathFile.XLSX". Make sure the object exists and name/path
are
spelled correctly.

It seems that the code needs to assign a variable [preferably
MyExcelFile],
to strFilePath for it to work. But will defer to you.

Sincerely appreciate everyone's assistance!!!!
- Mike

As follows is my code:

Function ImportExcel()
On Error GoTo ImportExcel_Err

'INSERT ALL CODE HERE FOR - MyExcelFile = Open "Get External Data - Get
Spreadsheet" dialog box to 'choose what file will become "MyExcelFile" in
this variable.

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 = True
strBrowseMsg = "Select the EXCEL file:"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xlsm)", "*.xlsm")
strPathFile = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:=strBrowseMsg, _
Flags:=ahtOFN_HIDEREADONLY)
If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
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



DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "strPathFile", True, "
Rng1 "
'DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile", True,
"Rng2"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile", True,
"Rng3"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile", True,
"Rng4"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile", True,
"Rng5"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile", True,
"Rng6"
'DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile", True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

The Getz code does not go in the same module with the other code.

From database window, select Modules. Click on New Module. A new regular
module window will open. Paste the entire Getz code into that module.
Save
the module, naming it basDialog or something like that.

Now go back to the original module (I assume that this is a form module).
The code should work.

--

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

Ken,
Thanx, I now have the Getz code, as pasted below.
Again, I'm ok with Excel vba, but still very much learning the Access
side.
Please bear with me.
So my questions are ...
1. Will the converted macro below work for my purposes, providing the
proper
code is inserted before all the imports?

2. If so, and if I put the Getz code pasted below in front of the
import
section, how do I assign the variable MyExcelFile to whichever workbook
I
navigate to?

Regards,
- Mike


Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)



:

See reply in your original thread.
--

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


Posted this earlier, but now have some clarifying code below,
converted
from
an Access macro then modified for this example.

Previous post:
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


As follows is the converted, modfied macro.
Thinking a bit like Excel vba, which I'm much more familiar with,
see
between the tilde-strings.
Believe I just need to have the "which spreadsheet" dialog box open,
choose
the Excel sheet/path, and assign the MyExcelFile variable to it.
Signed - Desperate for this Solution ...!!! <g>


Function ImportExcel ()
On Error GoTo ImportExcel_Err

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT ALL CODE HERE FOR - MyExcelFile = Open "Get External Data -
Get
Spreadsheet" dialog box to choose what file will become
"MyExcelFile"
in
this
variable.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DoCmd.TransferSpreadsheet acImport, 10, "tbl1", "MyExcelFile",
True,
"Rng1"
DoCmd.TransferSpreadsheet acImport, 10, "tbl2", "MyExcelFile",
True,
"Rng2"
DoCmd.TransferSpreadsheet acImport, 10, "tbl3", "MyExcelFile",
True,
"Rng3"
DoCmd.TransferSpreadsheet acImport, 10, "tbl4", "MyExcelFile",
True,
"Rng4"
DoCmd.TransferSpreadsheet acImport, 10, "tbl5", "MyExcelFile",
True,
"Rng5"
DoCmd.TransferSpreadsheet acImport, 10, "tbl6", "MyExcelFile",
True,
"Rng6"
DoCmd.TransferSpreadsheet acImport, 10, "tbl7", "MyExcelFile",
True,
"Rng7"

ImportExcel_Exit:
Exit Function

ImportExcel_Err:
MsgBox Error$
Resume ImportExcel_Exit

End Function

 

[Ken Snell MVP]

I can't remember my reason for not wanting to
use the 'built-in' Access method. I think it has some limitations, but
again
I don't remember.

Built-in Dialog box (MSODialog) requires a reference to Office xx Library,
which can cause all kinds of reference problems when the database is opened
by various ACCESS versions.

 

[MikeF]

Jim or Ken,

Now that this works, am wondering ...

Re the range to import --- is there any method of setting up criteria in the
Excel fields and importing only the records that meet that criteria?

Example -- all 7 ranges are imported, from a hundred workbooks.
As there are a few of them that will "suffer" some adjustments as time goes
by, would like to put "adj" or something of that nature in a field/column of
a record that contains an adjustment, so as not to re-import the entire 7
ranges.
Only the new recoreds.
*** I could set up another routine that imported only the records in each
range with "adj" in one field.

Before I head down this path, is this feasible???

Thanx and regards,
- Mike

Here's a link to the 'built-in' Access method:

http://support.microsoft.com/kb/279508

I'm sure this is more or less the same as the one you would have used in
Excel - it's a standard Microsoft Office tool.

Jim, no problem.
Thank you!!
I've stored both your modules.
And yes, Ken's works now, after I took the quotations off strFilePath as per
your suggestion.

And "my" code, ie the multiple TransferSpreadsheet lines, all work as well.
It's a bit of a nail-biter when it's running, as there is nothing to display
progress, but ... it certainly works.

Have to say though, it just seems like *A LOT* of complex code to merely
open a "which file?" dialog box.
Especially after the easy one or two lines in Excel vba.

One picky thing [asking for the world here!!] --- the Getz code is rather
old, before the dialog boxes had "Places" on the left, as in an easy way to
get to your much-used items.
... Is there anyway of changing the old dialog box that comes up to the new
2007-style one that sports "Places" in the left pane???

Regards,
- Mike

Well, I messed up. I should have just let Ken handle this and not replied. I
assumed he was talking about the same Getz code that I have, but now that

[quoted text clipped - 27 lines]

End Function

 

[Ken Snell MVP]

Feasible, yes. You'd have to use Automation to read the EXCEL files and
decide whether to import a range.

Probably more effective if you import the ranges, including the "criterion"
cell, and let your queries decide whether to use / not use the data.

--

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

Jim or Ken,

Now that this works, am wondering ...

Re the range to import --- is there any method of setting up criteria in
the
Excel fields and importing only the records that meet that criteria?

Example -- all 7 ranges are imported, from a hundred workbooks.
As there are a few of them that will "suffer" some adjustments as time
goes
by, would like to put "adj" or something of that nature in a field/column
of
a record that contains an adjustment, so as not to re-import the entire 7
ranges.
Only the new recoreds.
*** I could set up another routine that imported only the records in each
range with "adj" in one field.

Before I head down this path, is this feasible???

Thanx and regards,
- Mike

Here's a link to the 'built-in' Access method:

http://support.microsoft.com/kb/279508

I'm sure this is more or less the same as the one you would have used in
Excel - it's a standard Microsoft Office tool.

Jim, no problem.
Thank you!!
I've stored both your modules.
And yes, Ken's works now, after I took the quotations off strFilePath as
per
your suggestion.

And "my" code, ie the multiple TransferSpreadsheet lines, all work as
well.
It's a bit of a nail-biter when it's running, as there is nothing to
display
progress, but ... it certainly works.

Have to say though, it just seems like *A LOT* of complex code to merely
open a "which file?" dialog box.
Especially after the easy one or two lines in Excel vba.

One picky thing [asking for the world here!!] --- the Getz code is
rather
old, before the dialog boxes had "Places" on the left, as in an easy way
to
get to your much-used items.
... Is there anyway of changing the old dialog box that comes up to the
new
2007-style one that sports "Places" in the left pane???

Regards,
- Mike

Well, I messed up. I should have just let Ken handle this and not
replied. I
assumed he was talking about the same Getz code that I have, but now
that
[quoted text clipped - 27 lines]

End Function