Export the underlying Records from a Filtered Report to Excel

[kengooch]

I have been exporting reports to excel, but it brings over all the formating. Is it possible to just send the data that has been filtered in ascii text only to a excel workbook? Here is the code I am currently using. The Form from which the data comes is filtered by a series of 5 multi-select list boxes that builds a filter as items in the various unbound list boxes is select. The variable that stores this string is vIndvSel and that is passed to a variable vSetFilters the form automatically updates using the following code
Private Sub mslBox1_Click() Me.Form.Filter = vSetFilters Me.Form.FilterOn = True Me.Form.Refresh End Sub

Once the user has the filtered records they want they Click a button that Says "Export to Excel" that has the following code.

Private Sub bToExcel_Click() 'Export Custom Report to Excel '- - - - - - - - - - - - - - - 'Show Popup fProcessing.Caption = "Export Query to Excel" fProcessing.vCurProcs = "Copying Staff Records to Excel" fProcessing.Show vbModeless fProcessing.Repaint 'Put your process code here vDate = Format(Date, "yyyymmdd") vPath = "S:\CITC\MS-Tools\Reports\" vFileNm = vDate & " " & "Staff Custom Report.xlsx" [B] DoCmd.OutputTo acOutputForm, "fStaffListMSLB", acFormatXLSX, vPath & vFileNm, True[/B] 'Close Popup fProcessing.Hide '- - - - - - - - - - - - - - - End Sub

I really just want to export the underlying data. The form is based on the qStaff query from the tStaff table.
So hopefully there is a way to pass qStaff with the filter above so that it just gives me the raw data without all the report headers and formating etc.

 

[Gasman]

Use TransferSpreadsheet on the query with your filter as criteria.

See here how a template query is the base query with no criteria and criteria added.

 

[theDBguy]

You could also try using the CopyFromRecordset method. Just a thought...

 

[kengooch]

You could also try using the CopyFromRecordset method. Just a thought...

Not sure how to go about that. I looked it up on the Web... If you have any hints I would appreciate them.

 

[Pat Hartman]

The least amount of code would be using the transferSpreadsheet method. Select the query with the criteria in it and export the query. That's ONE line of code. Doesn't get any better than that.

 

[Gasman]

The least amount of code would be using the transferSpreadsheet method. Select the query with the criteria in it and export the query. That's ONE line of code. Doesn't get any better than that.

That is the problem Pat, the criteria is dynamic, being the form filter.

 

[Pat Hartman]

I'll add that to the reasons why I never use filters, even with a Jet/ACE BE. Given that most of my BE's are SQL Server, filtering locally causes excess data to be transferred over the LAN and effectively prevents the Server from doing the heavy lifting and so makes the SQL Server version of the app almost always slower than the Jet/ACE version.

You can always save a querydef that concatenates the criteria from the filter and export that saved querydef.

 

[Gasman]

You can always save a querydef that concatenates the criteria from the filter and export that saved querydef.

That was the method I suggested.

 

[Pat Hartman]

Sorry, I didn't notice the link.

 

[kengooch]

The least amount of code would be using the transferSpreadsheet method. Select the query with the criteria in it and export the query. That's ONE line of code. Doesn't get any better than that.

The problem is the query is modified by the Multiselect list boxes that give the user the ability to do dynamic record selection. For example, they can look at records from a specific year or quarter, they can choose by disease type, by the physician providing the care, by the first onset of the disease, etc. I use 5 multi-select list boxes to filter the records as noted above. Then the filtered records are displayed on screen in the form in a list with a button to take the to the specific patient compete record if needed. the report can be printed as a short list with key details or as a complete list with all record details. I would like to be able to send the filtered query to excel without the formating headers and other stuff that the reports provide. I was hopeful that somehow the one line of code that generates the report could also be used to create a temporary query that could then be dumped to excel.

   [B] DoCmd.OutputTo acOutputForm, "fStaffListMSLB", acFormatXLSX, vPath & vFileNm, True[/B]

 

[Gasman]

I think you can copy report to excel, but the layout would be the same.
I was just thinking of adding the criteria to you query sql, as shown in that link I posted ages ago.

 

[Isaac]

Not sure how to go about that. I looked it up on the Web... If you have any hints I would appreciate them.

+1

CopyFromRecordset is the bomb....

it's a strange beast that sort of makes you wonder "am I coding against the Access or the Excel object model right now ??" -

Used in Access vba, mixed in with vba that goes against Excel automation.

i.e.

dim excelapp as object, wb as object, ws as object, rng as object, rs as dao.recordset
set excelapp=createobject("excel.application")
set wb=excelapp.workbooks.open("path")
set ws=wb.sheets(1)
set rng = ws.range("a1")
set rs = currentdb.openrecordset("name of query or sql command")
rng.CopyFromRecordset rs

Note, this gives you without column headers, but you can loop through the rs.fields collection to place them there first, then target A2 instead.

 

[Gasman]

That does not take note of filtered records?
Would copying from recordsetclone take account of any filter?

 

[theDBguy]

That does not take note of filtered records?
Would copying from recordsetclone take account of any filter?

The way I use it for filtered forms is something like.

Set rs = Me.RecordsetClone
...
xlWS.CopyFromRecordset rs
...

 

[Gasman]

The way I use it for filtered forms is something like.

Set rs = Me.RecordsetClone
...
xlWS.CopyFromRecordset rs
...

So the recordset/recordsetclone does take any filter into account?

 

[theDBguy]

So the recordset/recordsetclone does take any filter into account?

That seems to be the case for me. Can't explain it though...