Export to excel with multiple tabs

[Jen]

Hi,

I am currently working on an export to excel that should go to multiple tabs
- named after the branches on the query. The problem is that while debugging,
it comes up with an error stating that rst.BranchName does not exist. I have
double checked the spelling on the field in the query being used as the
recordset & it is correct. It is not a primary key, but there are only 6 and
each is unique ... what did I do wrong?

Here is the VB that I altered from a previous post here:

Option Compare Database

Public vHelpline As String

Sub Export_Helpline()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vWorkbook As String

vWorkbook = "N:\ADMIN\Jennifer SM\Helpline Info\Helpline Report.xls"

Set db = CurrentDb
Set rst = db.OpenRecordset("qryBranchesGrouped")
Do Until rst.EOF
vHelpline = rst.BranchName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHelplineCallsAllByWeek_Crosstab, vWorkbook, , vHelpline
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Function Selected_Helpline()
Selected_Helpline = vHelpline

End Function

Thank you!

 

[Jeanette Cunningham]

Jen,
Here is some code I posted recently on this newsgroup. It will need some
tweaking to suit your purposes.
The code exports several tables to excel.
The table names are stored in a table called tblExport.
The code opens a recordset on tblExport to get the name of the table to
export, and then opens a recordset on the table/query to export the data
from each table.
It loops to the next table/query name and exports its data to the next
worksheet.

With your code you need to place the line
Option Explicit
at the top of your code module, just under the line
Option Compare Database

to make it easier for you to pick up typos with your variable names

As you want to export several different lots of data, it would be easier to
create a separate query for each lot.
The method you posted for getting each column of the query to export will
not work.
Have a go with this code and post back if you have questions.


Public Function MyExportMulti()
On Error GoTo FunctionErr
Dim objXLApp As Object
Dim objXLws As Object
Dim db As DAO.Database
Dim rstCopy As DAO.Recordset
Dim rstMain As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strWsName As String 'name of worksheet
Dim strQueryName As String
Dim strFirstCell As String
Dim strRange As String

Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"
strFirstCell = "A5"

'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.activeworkbook.SaveAs (strPath)

Set db = DBEngine(0)(0)
'Open a recordset on the table with query and worksheet names
Set rstMain = db.OpenRecordset("tblExport")
'make sure at start of table
rstMain.MoveFirst
' Use the recordset as a base
With rstMain
' Process until end of file
Do While Not .EOF

'get the name of the query
strQueryName = rstMain("QueryN")
'get the name of the worksheet
strWsName = rstMain("WorksheetN")
' Open a recordset on the query for the data to export
Set rstCopy = db.OpenRecordset(strQueryName)
' If there are no records, return an error and exit function
If rstCopy.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLws = objXLApp.activeworkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLws.Activate
' Ask Excel to copy the data from the recordset starting
with cell A5
objXLws.Range("A5").CopyFromRecordset rstCopy
'close the 1st recordset
rstCopy.Close
Set rstCopy = Nothing

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLws = objXLApp.activeworkbook.Worksheets(strWsName)
'format cells
With objXLws.Cells
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.LineStyle
= _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.ColorIndex
= _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If

rstMain.MoveNext
Loop
End With


'**error handling, in the function exit - make sure you set the object
'references to nothing as shown below.

FunctionExit:
' Hide warnings on the spreadsheet
objXLApp.DisplayAlerts = False
' Save the workbook
objXLApp.activeworkbook.Save
' Turn spreadsheet warnings back on
objXLApp.DisplayAlerts = True
' Make it visible
objXLApp.Visible = True

Set objXLws = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rstMain.Close
If Not rstCopy Is Nothing Then
Set rstCopy = Nothing
End If
If Not rstMain Is Nothing Then
Set rstMain = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If

Exit Function

FunctionErr:
MsgBox Err.Description & " " & Err.Number
Resume FunctionExit
End Function

 

[Jeanette Cunningham]

Jen,
If you are looking for a simpler alternative using transfer spreadsheet,
here are some notes.

when you use TransferSpreadsheet to export a query without specifying a
worksheet name in the Range argument, and the workbook already contains a
worksheet with the same name as the query, the new data will replace the
data on the existing sheet. If you want to export a query with the same
name multiple times so it creates multiple worksheets in the same
workbook, you have to specify the worksheet name each time you call
TranferSpreadsheet (or change the name of the query each time).

To use the above, create a separate query from qryHelplineCallsAllByWeek for
each lot of data you want to put in the worksheet.

Jeanette Cunningham

 

[Jen]

Thank you Jeanette,

However if I read the previous post correctly (the one I got the original
code from), you should theoretically be able to use a table (or query) to
give each worksheet/tab it's name thus eliminating the need for multiple
queries. I want this export to be dynamic so that a new query does not need
to be created every time we open a new branch (as my users will not
understand how to do this).

What I want to do is this - I want the export to read the branches from
either a table or query & make a tab for each branch that contains the data
for that branch. This is a crosstab query, so I want it to overwrite the
information that was there previously with the new updated info. There must
be a way to do this, I'm just not aware of how. Any additional thoughts?

 

[Jen]

Sorry, I put this at the wrong level.

Thank you Jeanette,

However if I read the previous post correctly (the one I got the original
code from), you should theoretically be able to use a table (or query) to
give each worksheet/tab it's name thus eliminating the need for multiple
queries. I want this export to be dynamic so that a new query does not need
to be created every time we open a new branch (as my users will not
understand how to do this).

What I want to do is this - I want the export to read the branches from
either a table or query & make a tab for each branch that contains the data
for that branch. This is a crosstab query, so I want it to overwrite the
information that was there previously with the new updated info. There must
be a way to do this, I'm just not aware of how. Any additional thoughts?

Thank you!

 

[Jeanette Cunningham]

Jen,
making sure I understand your request.
There is 1 crosstab query with info for several branches of a business
The number of branches is dynamic
You want the spreadsheet to have a tab for each branch

I haven't tried this with a crosstab query - which is most likely a problem
because of the way a crosstab generates its data.

Here's the plan for export.
Use the crosstab query to produce a select query with all the info for each
branch on a single row.
You could easily do this by setting up a temp table.
Set up the temp table so there is one field for each detail about a branch.
The table will have one record for each branch.
Empty the temp table using a delete query.
Append the data from the crosstab into the temp table.
Open a recordset on the temp table.
Export the first record to the first tab in the worksheet.
Do a Move Next to export the second record to the next tab in the worksheet.
And continue until the end of the recordset.
Post back if you have more questions.

Jeanette Cunningham

 

[Jen]

Hi Jeanette,
Your assessment is correct, except for the fact that there is not one record
per branch - there are anywhere from 5 to 100+ records per branch (one for
each representative). These are weekly statistics by rep, but need to be on
separate spreadsheets for organizational purposes. I am going to still try
your idea though - I'm wondering if I do a temp table as you suggested, can I
use a linked query with just the branch information to pull over the names of
the spreadsheets - sort of like a search for all the records with xx branch
name...? Do you think this would work? To be honest, I'm still very very new
to writing code - I have an idea of how to apply what I've seen, but writing
it is way beyond me. Thank you so much for all of your help!

Jen

 

[Jeanette Cunningham]

Jen,
After the data is in the temp table, you can run a query that gets the ID
for each branch in the temp table.
You could open a recordset on this query - call it rstBranchID
and then open a recordset on the temp table - call it rstBranchDetails
Use the BranchID from rstBranchID to select matching records in
rstBranchDetails and move these records to the first tab of the spreadsheet.
Do a Move Next on rstBranchID, get the matching records in rstBranchDetails
for the second BranchID and move these records to the second tab of the
spreadsheet and so on till all records for all BranchID's in rstBranchID
have been exported.

We can help with the code.

Jeanette Cunningham

 

[gllincoln]

Hi Jen,

Not sure about the logic flow of the heart of your code?

How is your crosstab query being filtered so that you are getting the correct branch? Have you double-checked the content of qryBranchesGrouped? While you may have a crosstab query that groups on the branchname, unless you add something like WHERE & BranchName ='" & rst.BranchName & "';" to the query, it seems as if you would be dumping the entire crosstab query into each worksheet? Not sure why we would need the six exports in that case?

Not sure why you would need a saved query to pull a list of the branches?

I would probably use something like
Set rs = Currentdb.Openrecordset("SELECT DISTINCT BranchName FROM whateverSourceTable;")

Inserting your source table name in the above should return a recordset containing the branchnames - one of each.

The syntax you have for exporting to multiple sheets in the same workbook, just isn't going to work.
The parameters are TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

You are okay up through the FileName - but for export, none of the rest of the arguments have a purpose on export, and you must leave the Range parameter blank on export or it fails.

The Docmd.transferSpreadsheet is essentially designed to create a new workbook or overwrite an old one with a worksheet having the same exact name as your recordset's name.

What the docmd as written will do is create a worksheet "qryHelplineCallsAllByWeek_Crosstab" and rewrite it 6 times in the same sheet (after you delete that unuseful range argument that will cause your transfer to fail, and add the required quote marks around the literal value - the name of the query.)

There is another gotcha when trying to export to an existing workbook - if the new export is shorter (has fewer rows) than the old export, whatever excess old data lines exist, they won't get overwritten by the new data; the old data will remain at the bottom the worksheet. Not particularly what you might want?

I would probably create a new workbook and give it a name that used the current date (formatted for example YYMMDD) 080320_HelpLine_Report.xls, add the sheets on the fly, naming one for each of the branches. This way, if you add a branch, or (we hope not) close a branch - then your code continues to work, and you can refer back to a previous day's or week's report if questions arise (as they inevitably do). If you are constantly updating the same workbook - your readily auditable history of reports is lost.

I would create my cross-tab query building the sql on the fly, inserting the branchname filter as part of the WHERE clause, create the new query def with the name I wanted to give my worksheet, then delete the querydef after I use it. Insert your own crosstab sql into strSQL but carefully construct it so that your BranchName filter can be dynamically tacked on to the end of it. Don't forget to substitute your sourcetable in the place of MYSOURCETABLE for snagging the list of branchnames in the code line that opens the rs recordset object.

Hope this helps,
Gordon

Function ExportHelpLineXLS() as Boolean
Dim qd as QueryDef
Dim strSQL as string
Dim Workbook as string
Dim rs as DAO.Recordset

Set rs = Currentdb.OpenRecordset("SELECT DISTINCT BranchName FROM MYSOURCETABLE"
Workbook = "N:\ADMIN\Jennifer SM\Helpline Info\" & Format(Date, "YYMMDD") & "_Helpline Report.xls"

strSQL = "SELECT blah, blah, blah FROM table1 INNER JOIN " _
& "table2 ON table1.something=table2.something " _
& "Group By table1.BranchName WHERE (table1.somefield > 0)"

'murder any existing workbook with our name
If Len(DIR(Workbook)) > 0 Then Kill Workbook

'if we have no branchnames to work with, tell the user and get out of here
If rs.RecordCount = 0 Then
ExportHelpLineXLS = False
Msgbox "No BranchName available for processing", vbCritical, "NO BRANCH NAMES"
set rs = Nothing
set qd = Nothing
Exit Function
End If

'got records will travel
Do While Not rst.EOF
set qd = Currentdb.CreateQueryDef(rs!BranchName, strSQL & " AND (BranchName = '" & rs!BranchName & "';")
docmd.transferSpreadsheet acExport, acSpreadsheetTypeExcel9, Workbook, qd.Name
Currentdb.QueryDefs.Delete qd.Name
rs.MoveNext
Loop

'wash our hands after
Set rs = Nothing
Set qd = Nothing
ExportHelpLineXLS = True
End Function