469,602 Members | 1,818 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

Export 1 Query Using Criteria to Multiple Excel Worksheets

I am trying to export data to Excel from a query in Access. There is a unique "Sales Region ID" and various "Territory#'s" for each Region. I would like one excel workbook for each region, and a worksheet for each Territory # assigned to that region. I do something similiar with a report that I programmatically convert to a pdf and it works fine, but I'm having a little trouble with the excel export. Can someone give me some advice? (Below is my code at it's current state.) Thanks.

Expand|Select|Wrap|Line Numbers
  1. ------------------------------------------------------------------------------------------------------------------
  2. Function GenerateMedicareFacilityByRegionExcelReport()
  4. Dim rsRegion As DAO.Recordset
  5. Dim rsTerritory_Number As DAO.Recordset
  6. Dim qd As DAO.QueryDef
  8. 'Create a dummy query for dynamic use
  9. Set qd = CurrentDb.QueryDefs("qryDummy")
  11. 'Get the Region
  12. Set rsRegion = CurrentDb.OpenRecordset("Select Distinct [Sales Region ID] From tblSalesRegions")
  14. If rsRegion.EOF And rsRegion.BOF Then
  15.   'No Regions found action
  16.   Exit Function
  17. End If
  19. 'For each Region we loop for the Territories:
  20. While Not rsRegion.EOF
  22.    Set rsTerritory_Number = CurrentDb.OpenRecordset("Select Distinct [Territory#] From tblTerritories Where [Sales Region ID]=" & rsRegion![Sales Region ID])
  24.    If rsTerritory_Number.EOF And rsTerritory_Number.BOF Then
  25.         'no Territory# found action = continue
  26.    End If
  28.    While Not rsTerritory_Number.EOF
  29.         qd.SQL = "Select * From qryMedicareUnits_by_Facility_Patient_And_Region Where [Sales Region ID]=" & rsRegion![Sales Region ID] & " AND Territory#=" & rsTerritory_Number![Territory#]
  30.         'Here the filled query is used for writing to an excel worksheet named to Region and Territory#
  31.         DoCmd.TransferSpreadsheet 1, 8, "qryDummy", "C:\" & rsRegion![Sales Region ID] & "-" & rsTerritory_Number!Territory# & ".xls", True
  33.      rsTerritory_Number.MoveNext
  35.   Wend
  37.      rsRegion.MoveNext
  39. Wend
  41. Set rsRegion = Nothing
  42. Set rsTerritory_Number = Nothing
  44. End Function
Oct 24 '08 #1
3 5209
32,200 Expert Mod 16PB
I believe the TableName parameter used in TransferSpreadsheet is used to specify the worksheet name.

You need to take the Territory number OUT of the filename, and rename qryDummy to something representing Territory# for each iteration through the loop.

I would consider making a copy of qryDummy to start with though. This can be deleted at the end of the process. Otherwise, if it is ever cancelled or interrupted then qryDummy may not be available next time (It may be there but with a Territory# for the name).
Oct 25 '08 #2
32,200 Expert Mod 16PB
A (sort of) continuation of this question can be found at Export multiple queries to multiple sheets in excel.

Probably best continued in here on balance, but for a similar problem, please see the linked thread.
Oct 28 '08 #3
32,200 Expert Mod 16PB

From reading your latest post (in the other thread) in full I get the impression that you're asking for exactly what is in my post #2 in here.

I know the interface for this is not the most intuitive in the world (a parameter for specifying a worksheet name would not go amiss), but that's what we're stuck with I'm afraid.

If you explain what you find confusing I can hopefully rephrase it (My explanations are not always the easiest to understand I know - even though I do try to make them so).

You'll see this isn''t simply about the code. It also relies on items existing in your database before we start.
Oct 28 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Anthony Cuttitta Jr. | last post: by
7 posts views Thread by Vanessa | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.