Export Filtered Form Results to an Excel Spreadsheet (1 Viewer)

kengooch

Member
Local time
Today, 05:56
Joined
Feb 29, 2012
Messages
137
So I have a form that allows the user to filter by multiple criteria in Multi-Select List boxes.
Once the records are filtered, I need to export them to an excel spreadsheet.
I have the following code...

Code:
Private Sub bToExcel_Click()
'Setup SQL
  Dim strSQL As String
  strSQL = Me.RecordSource
  strSQL = "SELECT " & strSQL & " WHERE " & Me.Filter
  With CurrentDb.QueryDefs("qEvntComplete")
     .SQL = strSQL
  End With
'Now export the query  with critera to excel
  Dim strOutFile    As String
  strOutFile = "\\R04MWVNAS21\Occupational Health\MSTools\Custom Reports\" & Format(Date, "yyyymmdd") & " Event-Staff Custome Report.xlsx"
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
        "qryExport", strOutFile, True
End Sub

I get an error on the .SQL = strSQL line 7

ExcelExpEr.PNG


What am I missing here? Or is there a better way to save this out to an excel sheet.

Thanks,
Ken
 
Last edited by a moderator:

theDBguy

I’m here to help
Staff member
Local time
Today, 05:56
Joined
Oct 29, 2018
Messages
21,357
Hi Ken. If you have filtered recordset, you can use the CopyFromRecordset method to quickly transfer that filtered information into and Excel spreadsheet.
 

Ranman256

Well-known member
Local time
Today, 08:56
Joined
Apr 9, 2015
Messages
4,339
your example shows 26 , a number, but you put quotes around it like its a string.
is the field a string or number?
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:56
Joined
Sep 21, 2011
Messages
14,041
What happens when the recordsource is a select in itself?
Put the sql into a string variable, then you can debug.print it, then use in place of the sql string, when you get it right?
 

kengooch

Member
Local time
Today, 05:56
Joined
Feb 29, 2012
Messages
137
your example shows 26 , a number, but you put quotes around it like its a string.
is the field a string or number?
Yes it is actually a lookup field and that is the unique key for that record
 

kengooch

Member
Local time
Today, 05:56
Joined
Feb 29, 2012
Messages
137
Hi Ken. If you have filtered recordset, you can use the CopyFromRecordset method to quickly transfer that filtered information into and Excel spreadsheet.
I tinked with this and found some help the web... but I couldn't make it work either
 

bastanu

AWF VIP
Local time
Today, 05:56
Joined
Apr 13, 2010
Messages
1,401
Maybe something like this:

Code:
Dim rs as DAO.Recordset
Dim oApp As Excel.Application
Dim oWT As Excel.Workbook
Dim oWS As Excel.Worksheet

Set rs= Me.RecordsetClone

Set oApp = GetObject("Excel.Application")
          If Err.Number <> 0 Then Set oApp = CreateObject("Excel.Application")
    With oApp
        .Visible = True       
        On Error GoTo 0
        .Workbooks.Add
        .Workbooks(1).Activate
        Set oWT = .ActiveWorkbook
          Set oWS = oWT.ActiveSheet
        'use CopyFromRecordset
        oWS.Range("A1")..CopyFromRecordset rs 'your recordset set at the start of procedure
        End With

Set rs=Nothing
 

kengooch

Member
Local time
Today, 05:56
Joined
Feb 29, 2012
Messages
137
I tinked with this and found some help the web... but I couldn't make it work either
i've tried so many different things... I don't even know where to go... wish that it was as simple as sending it to a report like

Code:
 DoCmd.OpenReport "rStfCmpltChkLstSrtd", acViewReport, , vSetFilters

vSetFilters is the filter that I build for the user from 5 Multiselect List Boxes, so there could be 10 or 15 or more filter conditions.

vSetFilters is the filter string that filters the qStaff table to show only the records the user wants based upon their filter selections.
Getting that filter (vSetFilters) to then pass only the filtered recordset from qStaff to the spreadsheet is what I am trying to achieve.

I have two other reports that send full query's to excel and they work. But I don't know how to get the filter to work on them. Here is the one that exports all the staff. If I could somehow append the vSetFilters filter to the string below it would work.

Code:
DoCmd.OutputTo acOutputQuery, "qStaff-Complete", acFormatXLSX, "\\R04MWVNAS21\Occupational Health\MSTools\Custom Reports\VaxTrack Complete Export\" & vDate & " StaffTrack Database Complete Staff Records Export.xlsx", True, ""

I know enough about VBA to be dangerous and less about SQL
 

kengooch

Member
Local time
Today, 05:56
Joined
Feb 29, 2012
Messages
137
Maybe something like this:

Code:
Dim rs as DAO.Recordset
Dim oApp As Excel.Application
Dim oWT As Excel.Workbook
Dim oWS As Excel.Worksheet

Set rs= Me.RecordsetClone

Set oApp = GetObject("Excel.Application")
          If Err.Number <> 0 Then Set oApp = CreateObject("Excel.Application")
    With oApp
        .Visible = True     
        On Error GoTo 0
        .Workbooks.Add
        .Workbooks(1).Activate
        Set oWT = .ActiveWorkbook
          Set oWS = oWT.ActiveSheet
        'use CopyFromRecordset
        oWS.Range("A1")..CopyFromRecordset rs 'your recordset set at the start of procedure
        End With

Set rs=Nothing
I'm guessing that the following line is the key line of code
Code:
oWS.Range("A1")..CopyFromRecordset rs
the two periods generate an error.
My Form is named fStafflstMSLB it is based on a query named qStaff and the filter string I build is assigned to vSetFilters
How do these 3 elements fit into the code you are suggesting?
 

bastanu

AWF VIP
Local time
Today, 05:56
Joined
Apr 13, 2010
Messages
1,401
Sorry, just one period needed (wrote it in Notepad without the help of Intellisense :)). The three names you mention are irrelevant for the code I suggested if the code runs on the fStafflstMSLB, the Me covers that:
Set rs= Me.RecordsetClone. The form's recordset would be filtered at this point so no need to worry about its source (the query) or the applied filter.
Note that CopyFromRecordset would only place the data in the Excel sheet, the field names need to be added separately, here is an update that does that (and saves your workbook):
Code:
Dim rs as DAO.Recordset
Dim oApp As Excel.Application
Dim oWT As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim strOutFile    As String
Dim i as Integer

strOutFile = "\\R04MWVNAS21\Occupational Health\MSTools\Custom Reports\" & Format(Date, "yyyymmdd") & " Event-Staff Custome Report.xlsx"

Set rs= Me.RecordsetClone

Set oApp = GetObject("Excel.Application")
          If Err.Number <> 0 Then Set oApp = CreateObject("Excel.Application")
    With oApp
        .Visible = False
     .ScreenUpdating = False
        .DisplayAlerts = False       
        On Error GoTo 0
        .Workbooks.Add
        .Workbooks(1).Activate

        Set oWT = .ActiveWorkbook
          Set oWS = oWT.ActiveSheet
        
    'use CopyFromRecordset
    'first add the field names
    For i = 0 To rs.Fields.Count - 1
             oWS.Cells(1,i+1).Value= rs.Fields(i).Name
    Next
    oWS.Cells(2,1).CopyFromRecordset rs'your recordset set at the start of procedure
      
    'Save the workbook
    oWT.SaveAs Filename:=strOutFile

        .ScreenUpdating = True
        .DisplayAlerts = True
     End With


oApp.Visible = True
AppActivate "Microsoft Excel"
Set oApp = Nothing

Set rs=Nothing
Cheers,
 

kengooch

Member
Local time
Today, 05:56
Joined
Feb 29, 2012
Messages
137
Sorry, just one period needed (wrote it in Notepad without the help of Intellisense :)). The three names you mention are irrelevant for the code I suggested if the code runs on the fStafflstMSLB, the Me covers that:
Set rs= Me.RecordsetClone. The form's recordset would be filtered at this point so no need to worry about its source (the query) or the applied filter.
Note that CopyFromRecordset would only place the data in the Excel sheet, the field names need to be added separately, here is an update that does that (and saves your workbook):
Code:
Dim rs as DAO.Recordset
Dim oApp As Excel.Application
Dim oWT As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim strOutFile    As String
Dim i as Integer

strOutFile = "\\R04MWVNAS21\Occupational Health\MSTools\Custom Reports\" & Format(Date, "yyyymmdd") & " Event-Staff Custome Report.xlsx"

Set rs= Me.RecordsetClone

Set oApp = GetObject("Excel.Application")
          If Err.Number <> 0 Then Set oApp = CreateObject("Excel.Application")
    With oApp
        .Visible = False
     .ScreenUpdating = False
        .DisplayAlerts = False      
        On Error GoTo 0
        .Workbooks.Add
        .Workbooks(1).Activate

        Set oWT = .ActiveWorkbook
          Set oWS = oWT.ActiveSheet
       
    'use CopyFromRecordset
    'first add the field names
    For i = 0 To rs.Fields.Count - 1
             oWS.Cells(1,i+1).Value= rs.Fields(i).Name
    Next
    oWS.Cells(2,1).CopyFromRecordset rs'your recordset set at the start of procedure
     
    'Save the workbook
    oWT.SaveAs Filename:=strOutFile

        .ScreenUpdating = True
        .DisplayAlerts = True
     End With


oApp.Visible = True
AppActivate "Microsoft Excel"
Set oApp = Nothing

Set rs=Nothing
Cheers,
Thanks so much... I copied this code to my database and got an error message.
 

Attachments

  • Code error.PNG
    Code error.PNG
    30.3 KB · Views: 314

kengooch

Member
Local time
Today, 05:56
Joined
Feb 29, 2012
Messages
137
So I finally got this to work... but when it creates the spreadsheet it creates unusual formating. The rows become 3 lines tall, the background is white and it brings over the borders. Anythoughts on a command I could add to the line to bring it over unformatted?
Code:
'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 = "\\R04MWVNAS21\Occupational Health\MSTools\Custom Reports\Reports by Staff\"
    vFileNm = vDate & " " & "Staff Custom Report.xlsx"
    DoCmd.OutputTo acOutputForm, "fStaffListMSLB", acFormatXLSX, vPath & vFileNm, True
'Close Popup
    fProcessing.Hide
'- - - - - - - - - - - - - - -
End Sub

I can't express how grateful I am for all of you who have given input and who continue to help me with this project. I REALLY appreciate the help!!
 

bastanu

AWF VIP
Local time
Today, 05:56
Joined
Apr 13, 2010
Messages
1,401
What line of the code gave you the error? Can you please post the entire procedure as it is in your db?

In your last post you are using OutputTo to output the actual form and I don't think there is a way to alter how Access does it, forms are not really meant to be exported.
EDIT: I have cleaned up the code and tested it, seems to be working OK now:
Code:
Dim rs As DAO.Recordset
Dim oApp As Excel.Application
Dim oWT As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim strOutFile    As String
Dim i As Integer

strOutFile = "\\R04MWVNAS21\Occupational Health\MSTools\Custom Reports\" & Format(Date, "yyyymmdd") & " Event-Staff Custome Report.xlsx"

Set rs = Me.Recordset 'Clone

Set oApp = CreateObject("Excel.Application")
    With oApp
        .Visible = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        'On Error GoTo 0
        .Workbooks.Add
        .Workbooks(1).Activate

        Set oWT = .ActiveWorkbook
          Set oWS = oWT.ActiveSheet
        
    'use CopyFromRecordset
    'first add the field names
    For i = 0 To rs.Fields.Count - 1
             oWS.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next
    
    oWS.Cells(2, 1).CopyFromRecordset rs 'your recordset set at the start of procedure
      
    'Save the workbook
    oWT.SaveAs FileName:=strOutFile

        .ScreenUpdating = True
        .DisplayAlerts = True
     End With


oApp.Visible = True

Set oApp = Nothing

Set rs = Nothing
Cheers,
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:56
Joined
Jul 9, 2003
Messages
16,244
I wondered if this might be of interest:-

Show/Hide & Export to Excel​


You can get a free copy with the following discount code:-
100% Discount = voe5p1i
 

Users who are viewing this thread

Top Bottom