By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Export 1 Query Using Criteria to Multiple Excel Worksheets

habby0123
P: 3
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()
  3.  
  4. Dim rsRegion As DAO.Recordset
  5. Dim rsTerritory_Number As DAO.Recordset
  6. Dim qd As DAO.QueryDef
  7.  
  8. 'Create a dummy query for dynamic use
  9. Set qd = CurrentDb.QueryDefs("qryDummy")
  10.  
  11. 'Get the Region
  12. Set rsRegion = CurrentDb.OpenRecordset("Select Distinct [Sales Region ID] From tblSalesRegions")
  13.  
  14. If rsRegion.EOF And rsRegion.BOF Then
  15.   'No Regions found action
  16.   Exit Function
  17. End If
  18.  
  19. 'For each Region we loop for the Territories:
  20. While Not rsRegion.EOF
  21.  
  22.    Set rsTerritory_Number = CurrentDb.OpenRecordset("Select Distinct [Territory#] From tblTerritories Where [Sales Region ID]=" & rsRegion![Sales Region ID])
  23.  
  24.    If rsTerritory_Number.EOF And rsTerritory_Number.BOF Then
  25.         'no Territory# found action = continue
  26.    End If
  27.  
  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
  32.  
  33.      rsTerritory_Number.MoveNext
  34.  
  35.   Wend
  36.  
  37.      rsRegion.MoveNext
  38.  
  39. Wend
  40.  
  41. Set rsRegion = Nothing
  42. Set rsTerritory_Number = Nothing
  43.  
  44. End Function
Oct 24 '08 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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

NeoPa
Expert Mod 15k+
P: 31,186
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

NeoPa
Expert Mod 15k+
P: 31,186
Heather,

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.