Solved Export crosstab query with VBA (1 Viewer)

[Cris VS]

Hi,

I wanted to ask if there is a way that I could export this crosstab query (Thread on crosstab query) to Excel using VBA along with some attributes of each index and depending on the Event (using a parameter).

Thanks

 

[theDBguy]

What have you tried? Have you tried the TransferSpreadSheet or the CopyFromRecordset method?

 

[Cris VS]

This is the code I have used with the corresponding references set:

Private Sub ExportToExcel_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 rs1 As DAO.Recordset
 
'Variables for spreadsheet population
Dim nrow, ncol As Long
    
On Error GoTo SubError
DoCmd.Hourglass True
    
    Set db = CurrentDb()
    
    Set qdf1 = db.QueryDefs("QuerySQL1")
    qdf1!ParEvent = [Forms]![ELE-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
            'Formatting commands
            'Data filling
            .Range("C2").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
    rs1.Close
    qdf1.Close

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

It works in the sense that it opens Excel and plots the data of the specified event. The problem is that the rows of the crosstab don't match the correct attributes of the indexes. I guess I just don't know how to create a query or similar that links each row of the crosstab query to the attributes that I want to export for each index.

 

[Pat Hartman]

Normally you would use TransferSpreadsheet to transfer the query's data to Excel. It is one line of code and therefore much simpler.

I don't understand the bit about "indexes". Excel doesn't have indexes. That is an attribute of a table and you are exporting a query. If you are talking about the sequence of the rows, try adding an order by clause to the query to get the rows in the order you want them to appear.

 

[Cris VS]

Hi, the crosstab query I want to export was defined in a previous thread:

crosstab query (Thread on crosstab query)

Here I defined a field called "Index". Now what I want to do is to export this query (among others that are not relevant nor related to this one). I have tried giving the same order of clauses and it doesn't work. I have checked that the fields exported from the query are the correct ones but they just don't export in the same order...

 

[Pat Hartman]

Did you manually move the columns around? Access remembers when you do this. If you can't get the columns back in the correct order, then rebuild the query from scratch.

 

[bastanu]

Hi Cris,

Please have a look at the attached sample that uses the raw data and creates an Excel file with a pivot table similar to your crosstab. The advantage of that is that it includes the raw data on a separate sheet and also supports the basic Excel pivot functionality like drill-down.

Please note that I modified your Table1 table to remove the lookups on the Event and Type fields as they can cause issues-see http://access.mvps.org/access/lookupfields.htm

It uses my free utility available here: http://forestbyte.com/ms-access-utilities/fba-pivot-table-designer/

Cheers,

 

[Cris VS]

Did you manually move the columns around? Access remembers when you do this. If you can't get the columns back in the correct order, then rebuild the query from scratch.

I have tried this, even copy-pasting the query and hiding the original fields to only show the "related ones" but still does not put it in the same order...

 

[Pat Hartman]

Create a NEW query with the columns in the order you want.

 

[Cris VS]

Hi Cris,

Please have a look at the attached sample that uses the raw data and creates an Excel file with a pivot table similar to your crosstab. The advantage of that is that it includes the raw data on a separate sheet and also supports the basic Excel pivot functionality like drill-down.

Please note that I modified your Table1 table to remove the lookups on the Event and Type fields as they can cause issues-see http://access.mvps.org/access/lookupfields.htm

It uses my free utility available here: http://forestbyte.com/ms-access-utilities/fba-pivot-table-designer/

Cheers,

Thank you very much for this. I studied it thoroughly but still can't find a way to put the crosstab query rows in the same order as the attributes of each index when exporting it...

 

[Cris VS]

Solved, with a bit of patience and doing everything in the correct order as Pat said... Thanks for the help!

 

[Pat Hartman]

You're welcome