Solved Export report to Excel with VBA (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:48
Joined
Feb 19, 2002
Messages
42,976
You don't need all that code to export to Excel. You just need a single line using the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qYourQueryName YourFileName

populate the two variables with the query name and the filename

In the query, add a Where clause that selects what you want. If it is the current record you are viewing, then it would be something like:

Where RecID = Forms!myForm!txtRecID
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:48
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious, have you checked out the CopyFromRecordset method?

Sent from phone...
 

Eugene-LS

Registered User.
Local time
Tomorrow, 00:48
Joined
Dec 7, 2018
Messages
481
Hi. Just curious, have you checked out the CopyFromRecordset method?
I use this method a lot when outputting reports to Excel (filling down a template) - it works just fine
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:48
Joined
Oct 29, 2018
Messages
21,358
Hm-m-m ....
How should I know abaute application that i have never seen?
Need to take a look at the app specifics!
I was talking about the code the OP posted. Can you see if the CopyFromRecordset could apply to that code?
 

Cris VS

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
75
You don't need all that code to export to Excel. You just need a single line using the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qYourQueryName YourFileName

populate the two variables with the query name and the filename

In the query, add a Where clause that selects what you want. If it is the current record you are viewing, then it would be something like:

Where RecID = Forms!myForm!txtRecID
The thing is that as I want to format the output Excel document and assign different queries in different parts of the sheet I do need to use the code. I have built the queries that will be exported (with the query wizard and then query design, no VBA) with a where clause that selects the parameter, just as you mention.

The issue is that when I run the VBA code, I get error "Too few parameters, expected 1", which I am guessing that might have to do with not having defined the parameter in the VBA code as well as (or instead of) in the query...

This is why I asked about this
I have read it has to be done using parameters but I don't know how to declare/use them in VBA.

Thank you very much :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:48
Joined
Feb 19, 2002
Messages
42,976
You are building the SQL in VBA so you would embed any parameters meaning that there would never be any prompting.
 

Cris VS

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
75
I have finally managed to do all. Thank you all for your help. I attach the code in case it is of any use to someone.

Thanks again :) :) :)

Code:
Private Sub ExportOverviewToExcel_Click()
    
'Variables to create Excel App
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

'Variables for data retrieval
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef

Dim SQL1 As String
Dim rs1 As DAO.Recordset
    
'Variables for spreadsheet population
Dim nrow, ncol As Integer
    
On Error GoTo SubError
DoCmd.Hourglass True
 
    Set db = CurrentDb()
        
    Set qdf1 = db.QueryDefs("QuerySQL1")
    qdf1!ParEvent = [Forms]![EVENTOVERVIEW]![Event]
    Set rs1 = qdf1.OpenRecordset
    
    'Check there is data to export
    
    If rs1.RecordCount = 0 Then
        MsgBox "No data available for export", vbInformation + vbOKOnly, "Excel not launched"
        GoTo SubExit
    End If
    
    Set xlApp = Excel.Application
    
    xlApp.Visible = False
        
        Set xlWorkbook = xlApp.Workbooks.Add
        Set xlSheet = xlWorkbook.Worksheets(1)
    
        With xlSheet
            
            'General formatting
            .Name = "Event Summary"
            .Cells.Font.Name = "Calibri"
            .Cells.Font.Size = 10
            .Cells.VerticalAlignment = xlCenter
            .Columns.WrapText = True
            .Columns.ColumnWidth = 20
            
            'Position counter
            nrow = 1
            ncol = 1
            
            'TITLES
            .Rows(nrow).Font.Size = 14
            .Rows(nrow).Font.Bold = True
            .Rows(nrow).VerticalAlignment = xlCenter
            .Rows(nrow).Interior.Color = RGB(255, 121, 121)
            .Cells(nrow, 1) = "EVENT DETAILS"
                        
            'SUBTITLES
            nrow = nrow + 2
            .Rows(nrow).Font.Size = 12
            .Rows(nrow).Font.Bold = True
            .Rows(nrow).Interior.Color = RGB(248, 248, 248)
            .Cells(nrow, ncol) = "Event"
            .Columns(ncol).ColumnWidth = 30
            ncol = ncol + 1
            .Cells(nrow, ncol) = "Deadline"
            .Columns(ncol).HorizontalAlignment = xlCenter
            
            'DATA FILLING
            
                .Range("A5").CopyFromRecordset rs1
            
        End With

        
xlApp.Visible = True
MsgBox "File exported successfully", vbInformation + vbOKOnly, "Export success"
          
SubExit:
    DoCmd.Hourglass False
    On Error Resume Next
    
    Set xlSheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing

    Set db = Nothing
    Set rs1 = Nothing
    Set qdf1 = Nothing
    
    rst1.Close
    qdf1.Close

    Exit Sub
    
SubError:
    MsgBox "Error number: " & Err.Number & "*" & Err.Description, vbCritical + vbOKOnly, _
    "An error occurred"
    Err.Clear
    Resume SubExit
    
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:48
Joined
Oct 29, 2018
Messages
21,358
I have finally managed to do all. Thank you all for your help. I attach the code in case it is of any use to someone.

Thanks again :) :) :)

Code:
Private Sub ExportOverviewToExcel_Click()
   
'Variables to create Excel App
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

'Variables for data retrieval
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef

Dim SQL1 As String
Dim rs1 As DAO.Recordset
   
'Variables for spreadsheet population
Dim nrow, ncol As Integer
   
On Error GoTo SubError
DoCmd.Hourglass True

    Set db = CurrentDb()
       
    Set qdf1 = db.QueryDefs("QuerySQL1")
    qdf1!ParEvent = [Forms]![EVENTOVERVIEW]![Event]
    Set rs1 = qdf1.OpenRecordset
   
    'Check there is data to export
   
    If rs1.RecordCount = 0 Then
        MsgBox "No data available for export", vbInformation + vbOKOnly, "Excel not launched"
        GoTo SubExit
    End If
   
    Set xlApp = Excel.Application
   
    xlApp.Visible = False
       
        Set xlWorkbook = xlApp.Workbooks.Add
        Set xlSheet = xlWorkbook.Worksheets(1)
   
        With xlSheet
           
            'General formatting
            .Name = "Event Summary"
            .Cells.Font.Name = "Calibri"
            .Cells.Font.Size = 10
            .Cells.VerticalAlignment = xlCenter
            .Columns.WrapText = True
            .Columns.ColumnWidth = 20
           
            'Position counter
            nrow = 1
            ncol = 1
           
            'TITLES
            .Rows(nrow).Font.Size = 14
            .Rows(nrow).Font.Bold = True
            .Rows(nrow).VerticalAlignment = xlCenter
            .Rows(nrow).Interior.Color = RGB(255, 121, 121)
            .Cells(nrow, 1) = "EVENT DETAILS"
                       
            'SUBTITLES
            nrow = nrow + 2
            .Rows(nrow).Font.Size = 12
            .Rows(nrow).Font.Bold = True
            .Rows(nrow).Interior.Color = RGB(248, 248, 248)
            .Cells(nrow, ncol) = "Event"
            .Columns(ncol).ColumnWidth = 30
            ncol = ncol + 1
            .Cells(nrow, ncol) = "Deadline"
            .Columns(ncol).HorizontalAlignment = xlCenter
           
            'DATA FILLING
           
                .Range("A5").CopyFromRecordset rs1
           
        End With

       
xlApp.Visible = True
MsgBox "File exported successfully", vbInformation + vbOKOnly, "Export success"
         
SubExit:
    DoCmd.Hourglass False
    On Error Resume Next
   
    Set xlSheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing

    Set db = Nothing
    Set rs1 = Nothing
    Set qdf1 = Nothing
   
    rst1.Close
    qdf1.Close

    Exit Sub
   
SubError:
    MsgBox "Error number: " & Err.Number & "*" & Err.Description, vbCritical + vbOKOnly, _
    "An error occurred"
    Err.Clear
    Resume SubExit
   
End Sub
Hi. Congratulations! I see you ended up using CopyFromRecordset after all. Good luck with your project.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:48
Joined
Sep 12, 2006
Messages
15,614
You don't need all that code to export to Excel. You just need a single line using the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qYourQueryName YourFileName

populate the two variables with the query name and the filename

In the query, add a Where clause that selects what you want. If it is the current record you are viewing, then it would be something like:

Where RecID = Forms!myForm!txtRecID

Hi Pat. I think the OP is trying to generate a nicely formatted report in Excel from the Access report

My preference would be to output a query, as you suggest, and let Excel experts develop a way to filter, sort, copy and present the exported query within excel in the way they want. It must be less expensive to do it that way, than getting it done in Access. (or more likely getting many versions done in Access)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:48
Joined
Feb 19, 2002
Messages
42,976
You can still do both. He was having trouble with the export so I offered an alternative that takes less code.
 

Users who are viewing this thread

Top Bottom