Exporting Access objects to different excel worksheets

  • Thread starter Thread starter Joel Wiseheart
  • Start date Start date
J

Joel Wiseheart

If you are exporting tables from Access to Excel, how do
you specify to put table1 in access on sheet1 in Excel
and table2 in Access on sheet2 of the same Excel file?

Every time I use the OutputTo method, it deletes the old
spreadsheet, and puts the new results on sheet1 again.
There must be something I'm missing.

Thanks!
 
Use the TransferSpreadsheet method of DoCmd. It will write onto new
worksheets when a worksheet already exists in the file.
 
Ken, I have a similar question. I have a report that is
grouped by Region. Is there a way to transfer each region
to its own tab on the same workbook? Do I need to transfer
a query instead? Any advice would be great. Thanks!
 
You'll need to use a loop to create a query that will select records based
on the region name, do the TransferSpreadsheet action, then loop around for
each region name. For example:


Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strCriterion(4) As String
Dim intLoop As Integer
Dim strSQL As String, strQ As String

strCriterion(0) = "RegionName1"
strCriterion(1) = "RegionName2"
strCriterion(2) = "RegionName3"
strCriterion(3) = "RegionName4"
strCriterion(4) = "RegionName5"

Set dbs = CurrentDb

For intLoop = 0 To 4
strQ = strCriterion(intLoop)
strSQL = "SELECT * FROM Table_Name WHERE [RegionName] = '" &
strCriterion(intLoop)
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
' dbs.QueryDefs.Append qdf
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQ,
"C:\Ken.xls", False
' dbs.QueryDefs.Delete strQ
Set qdf = Nothing
Next intLoop
dbs.Close
Set dbs = Nothing
 
Sorry --- left in some wrong stuff...

here is the correct code example:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strCriterion(4) As String
Dim intLoop As Integer
Dim strSQL As String, strQ As String

strCriterion(0) = "RegionName1"
strCriterion(1) = "RegionName2"
strCriterion(2) = "RegionName3"
strCriterion(3) = "RegionName4"
strCriterion(4) = "RegionName5"

Set dbs = CurrentDb

For intLoop = 0 To 4
strQ = strCriterion(intLoop)
strSQL = "SELECT * FROM Table_Name WHERE [RegionName] = '" &
strCriterion(intLoop)
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQ,
"C:\Ken.xls", False
dbs.QueryDefs.Delete strQ
Set qdf = Nothing
Next intLoop
dbs.Close
Set dbs = Nothing


--
Ken Snell
<MS ACCESS MVP>


Ken Snell said:
You'll need to use a loop to create a query that will select records based
on the region name, do the TransferSpreadsheet action, then loop around for
each region name. For example:


Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strCriterion(4) As String
Dim intLoop As Integer
Dim strSQL As String, strQ As String

strCriterion(0) = "RegionName1"
strCriterion(1) = "RegionName2"
strCriterion(2) = "RegionName3"
strCriterion(3) = "RegionName4"
strCriterion(4) = "RegionName5"

Set dbs = CurrentDb

For intLoop = 0 To 4
strQ = strCriterion(intLoop)
strSQL = "SELECT * FROM Table_Name WHERE [RegionName] = '" &
strCriterion(intLoop)
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
' dbs.QueryDefs.Append qdf
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQ,
"C:\Ken.xls", False
' dbs.QueryDefs.Delete strQ
Set qdf = Nothing
Next intLoop
dbs.Close
Set dbs = Nothing


--
Ken Snell
<MS ACCESS MVP>


michael c said:
Ken, I have a similar question. I have a report that is
grouped by Region. Is there a way to transfer each region
to its own tab on the same workbook? Do I need to transfer
a query instead? Any advice would be great. Thanks!
Click to expand...
Click to expand...
 
Ken, this is great. Thanks! I'll give it a try.
-----Original Message-----
Sorry --- left in some wrong stuff...

here is the correct code example:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strCriterion(4) As String
Dim intLoop As Integer
Dim strSQL As String, strQ As String

strCriterion(0) = "RegionName1"
strCriterion(1) = "RegionName2"
strCriterion(2) = "RegionName3"
strCriterion(3) = "RegionName4"
strCriterion(4) = "RegionName5"

Set dbs = CurrentDb

For intLoop = 0 To 4
strQ = strCriterion(intLoop)
strSQL = "SELECT * FROM Table_Name WHERE [RegionName] = '" &
strCriterion(intLoop)
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQ,
"C:\Ken.xls", False
dbs.QueryDefs.Delete strQ
Set qdf = Nothing
Next intLoop
dbs.Close
Set dbs = Nothing


--
Ken Snell
<MS ACCESS MVP>


You'll need to use a loop to create a query that will select records based
on the region name, do the TransferSpreadsheet action,
Click to expand...
then loop around
for
each region name. For example:


Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strCriterion(4) As String
Dim intLoop As Integer
Dim strSQL As String, strQ As String

strCriterion(0) = "RegionName1"
strCriterion(1) = "RegionName2"
strCriterion(2) = "RegionName3"
strCriterion(3) = "RegionName4"
strCriterion(4) = "RegionName5"

Set dbs = CurrentDb

For intLoop = 0 To 4
strQ = strCriterion(intLoop)
strSQL = "SELECT * FROM Table_Name WHERE [RegionName] = '" &
strCriterion(intLoop)
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
' dbs.QueryDefs.Append qdf
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQ,
"C:\Ken.xls", False
' dbs.QueryDefs.Delete strQ
Set qdf = Nothing
Next intLoop
dbs.Close
Set dbs = Nothing


--
Ken Snell
<MS ACCESS MVP>


Ken, I have a similar question. I have a report that is
grouped by Region. Is there a way to transfer each region
to its own tab on the same workbook? Do I need to transfer
a query instead? Any advice would be great. Thanks!

-----Original Message-----
Use the TransferSpreadsheet method of DoCmd. It will
write onto new
worksheets when a worksheet already exists in the file.

--
Ken Snell
<MS ACCESS MVP>

"Joel Wiseheart"
Click to expand...
wrote in message
If you are exporting tables from Access to Excel, how do
you specify to put table1 in access on sheet1 in Excel
and table2 in Access on sheet2 of the same Excel file?

Every time I use the OutputTo method, it deletes the old
spreadsheet, and puts the new results on sheet1 again.
There must be something I'm missing.

Thanks!


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top