Export from Access 2007 to Multiple Worksheets in Excel

[Erick C]

I am having a problem, and I am hoping that someone may be able to
help me out. I currently have a table and a crosstab query that I
would like to put into different worksheets in the same spreadsheet.
This is the first part of my dilemma.
Now, here is the tricky part. I am making the Access database for
different users, who have NO Access or VBA experience at all. I have
made a form with buttons that a user can press in order to import
certain files into Access tables. When they press a button it opens a
dialog box that lets them select the folder and file to start the
import, and then it loops through all of the files in the folder and
imports the information. I copied the code from another discussion
forum that I ran into. I would like to use the same methodology for
my export. I would like to make a button on the form that allows the
user to choose the file that will have the info imported into
different worksheets, but I do not want the loop like I have now. The
current code loops through all files in the folder, I only want to
pick one file. Here is my current code attached to my form buttons:

Private Sub Command13_Click()
Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select a subinventory report to begin...", _
Flags:=ahtOFN_HIDEREADONLY)
strDirectory = Left(strInputFileName, _
InStrRev(strInputFileName, "\"))
strFileName = Dir(strDirectory & "*.xls")

Do While strFileName <> vbNullString
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Subinventory Report", _
strDirectory & strFileName, True
strFileName = Dir()
Loop

End Sub

Can my request be done, and if so can someone point me in the right
direction? Please bear in mind I am still a novice when it comes to
VBA, but I am trying to learn as much as possible.

Any information that can be provided would be greatly appreciated.

 

[Ken Snell [MVP]]

See the various code examples on my web page for EXCEL exporting:
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

I think you'll find a few of them are pertinent to your setup.

 

[Erick C]

Ken -
Thank you for the link. I have tried to make a new code for the
button that opens up the dialog box to select a file and then export
the table data to a particular named worksheet. I get the dialog box
to open, but when I select the file I gett the error message box
saying "No file was selected."
Can someone please help me and let me know what I have missed? Is the
problem how I am placing the name of the worksheet in the DoCmd? Here
is the code:

Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:=" Please Select The Analysis File To Export
Quantities…", _
Flags:=ahtOFN_HIDEREADONLY)
strDirectory = Left(strInputFileName, _
InStrRev(strInputFileName, "\"))
strFileName = Dir(strDirectory & "*.xls")

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

strTable = "Subinventory Item Crosstab"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, “Subinv Quantities”

Any help would be appreciated.
Thank you again, everybody.

 

[Ken Snell [MVP]]

Assuming that you want to import just the one file that you select, change
these code lines:

strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:=" Please Select The Analysis File To Export
Quantities…", _
Flags:=ahtOFN_HIDEREADONLY)
strDirectory = Left(strInputFileName, _
InStrRev(strInputFileName, "\"))
strFileName = Dir(strDirectory & "*.xls")

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



to this:

strPathFile = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:=" Please Select The Analysis File To Export
Quantities…", _
Flags:=ahtOFN_HIDEREADONLY)
If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If
strDirectory = Left(strPathFile, _
InStrRev(strPathFile, "\"))

--

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





Ken -
Thank you for the link. I have tried to make a new code for the
button that opens up the dialog box to select a file and then export
the table data to a particular named worksheet. I get the dialog box
to open, but when I select the file I gett the error message box
saying "No file was selected."
Can someone please help me and let me know what I have missed? Is the
problem how I am placing the name of the worksheet in the DoCmd? Here
is the code:

Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:=" Please Select The Analysis File To Export
Quantities…", _
Flags:=ahtOFN_HIDEREADONLY)
strDirectory = Left(strInputFileName, _
InStrRev(strInputFileName, "\"))
strFileName = Dir(strDirectory & "*.xls")

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

strTable = "Subinventory Item Crosstab"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, “Subinv Quantities”

Any help would be appreciated.
Thank you again, everybody.

 

[Erick C]

Ken -
Thank you again for your assistance. Unfortunately, I still have a
mistake in my code. There is something wrong in my DoCmd. When I
select the file that I want to export the table data to I get an error
message saying "Run-time error '2498': An expression you entered is
the wrong data type for one of the arguments." If I press the debug
button in the message box then it highlights the DoCmd portion of the
code.

Here is the code that I currently have:

Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
strPathFile = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:=" Please Select The Analysis File To Export
Quantities…", _
Flags:=ahtOFN_HIDEREADONLY)
If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If
strDirectory = Left(strPathFile, _
InStrRev(strPathFile, "\"))

strTable = "Subinventory Item Crosstab"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, "Subinv Quantities"

End Sub

So, I am picking the file that I would like to export the data to, and
in that file I want the data to be put into a tab called "Subinv
Quantities". What am I doing wrong in this section?

 

[Ken Snell [MVP]]

Try changing the DoCmd step to this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, , "'Subinv Quantities$'"

But using the Range argument for exporting to an EXCEL file is fraught with
possible problems. See this article:
Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

It's better to have your query named the way you want the spreadsheet to be
named. Create a query named "Subinv Quantities", and have it return all
fields and all records from your "Subinventory Item Crosstab" table/query.
Then use the "Subinv Quantities" for the export:

strTable = "Subinv Quantities"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile

--

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



Ken -
Thank you again for your assistance. Unfortunately, I still have a
mistake in my code. There is something wrong in my DoCmd. When I
select the file that I want to export the table data to I get an error
message saying "Run-time error '2498': An expression you entered is
the wrong data type for one of the arguments." If I press the debug
button in the message box then it highlights the DoCmd portion of the
code.

Here is the code that I currently have:

Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
strPathFile = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:=" Please Select The Analysis File To Export
Quantities…", _
Flags:=ahtOFN_HIDEREADONLY)
If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If
strDirectory = Left(strPathFile, _
InStrRev(strPathFile, "\"))

strTable = "Subinventory Item Crosstab"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, "Subinv Quantities"

End Sub

So, I am picking the file that I would like to export the data to, and
in that file I want the data to be put into a tab called "Subinv
Quantities". What am I doing wrong in this section?

 

[Erick C]

Ken -
Thank you again. I have made the changes that you suggested and it
now works. It seems that I was missing a comma in my DoCmd line
(found after I looked at your response), but I also renamed the table
and the export name so they both match as you suggested. Everything
seems to be working just fine.

Thank you again for all of your assistance, it is greatly
appreciated. Hopefully it will be a little while before I have to
post another question.

Erick