data to different worksheets based on criteria

[slaga9]

I am using access to export a parameter query into excel.
Upon the arrival of the data on the first workbook, I was wondering if it were possible to have the data duplicated onto other worksheets based on some criteria (location).

example...all of the entries on the DB have a location.
In access if I ran the query to see all the work from a certain week and clikc export, each entry would have 1-5 for a location. on the following work pages I want to find all of the 1's, all of the 2's and so on.

Does that make sence?
Is that possible?

thanks for your time
SL

 

[SkipVought]

SL,

Unless there are compelling reasons for chopping up your data into multiple sheets, I'd suggest what I do myself:

Query results in one sheet using AutoFilter or some other control, to select the relevant subset(s) of data. And don't give the the excuse that your users can't make such selection(s).

Short of that, knock yourself out, by running the query multiple times to multiple sheets. They STILL have to select some sheet. But, in the process, you will have multiplied your maintenance efforts.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[slaga9]

thanks for the reply.
However, I really need to the data to go to different sheets on the click.
If I were around I would use your recommendation, however I am returning to school in a few weeks and I know that based on the people who will take over generating the recovery report, it would not be possible for them to do so. Also, having the data separated on click would save a lot of time.

I have decided I wish to pursue solving this through access. I am not sure if this is an area you are familiar with but I thought it would be worth asking.

Currently this code (below) exports the entire query results to the ms access file.
Now, each entry has a site, each site has its own worksheet (named as the site)
Do you know what is the best way to have the data go to a worksheet based on its site?
Thanks so much for your time,
I really appreciate your first response, and any additional information is highly valued.
SL

Private Sub exportcmd_Click()
On Error GoTo err_Handler
   Const cTabTwo As Byte = 1
   ' Excel object variables
   
   Dim appExcel As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim prm As DAO.Parameter
   Dim rstOutput As DAO.Recordset
   Dim sOutput As String
   
    Set db = CurrentDb
    Set qdf = db.QueryDefs("paraquery")
        For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    Set rstOutput = qdf.OpenRecordset
    
   DoCmd.Hourglass True
   
   ' set to break on all errors
   Application.SetOption "Error Trapping", 0

     ' start with a clean file built from the template file
   sOutput = CurrentProject.Path & "\salary recovery template.xls"
   
   ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
   Set appExcel = Excel.Application
   Set wbk = appExcel.Workbooks.Open(sOutput)
   Set wks = appExcel.Worksheets(cTabTwo)
   appExcel.Visible = True
   'Take the records in the current form and dump to Excel
   wks.Range("A11").CopyFromRecordset rstOutput
  
exit_Here:
   ' Cleanup all objects  (resume next on errors)
   On Error Resume Next
   Set wks = Nothing
   'You may want to save the workbook here
   Set wbk = Nothing
   Set appExcel = Nothing
   rstOutput.Close
   Set rstOutput = Nothing
   DoCmd.Hourglass False
' Exit exportcmd_Click
   
err_Handler:
   ExportRequest = Err.Description
   ' Me.lblMsg.Caption = Err.Description
   'Resume exit_Here
End Sub

 

[SkipVought]

You could do this in about 5 minutes in Excel using MS Query via Data > Get External Data ...

Do it for one sheet for the criteria 1.

Copy the sheet 4 times.

Then change each other criteria.

Or, better yet, make it a Parameter Query, based on the vlaue in A1 on the sheet. Put the query results in A3.

Copy this sheet 4 times, and simply change the criteria in A1 on each other sheet.


VOLA!

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[JerryKlmns]

slaga9

I read your code and noticed that there is no

appExcel.Quit

command before your destroying of that object. I would strongly recommend to do so. Also, follow Skip 's excel-way to do that task. If you were to use the previous code, you would filter your recordset for each criteria value and .CopyFromRecordset to a new sheet every time.