VBA code for exporting an Access crosstab query into an excel template

[Yatiman Idara]

Hi everyone,

Just to put things into perspective I am new to VBA. I usually learn VBA stuff from net and then customize them to my needs. Recently I tested out VBA code to automatically update an excel template because excel offers a richer set of charts and graphs. I successfully did this with the normal Select queries. Code for one of these is below:

'*********************************************
'* CATEGORY CHART (Handmade Chart) *
'*********************************************
Private Sub cmdChart1_Click()
On Error GoTo SubError

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim SQL As String
Dim rs1 As DAO.Recordset
Dim i As Integer
'Dim strPath As String

'Show user work is being performed
DoCmd.Hourglass (True)

'*********************************************
' RETRIEVE DATA
'*********************************************
'SQL statement to retrieve data from database
SQL = "SELECT tbl1Disabled.CategoryNumber, Count(tbl1Disabled.ID) AS CountOfID, " & _
"Avg(tbl1Disabled.Payment) AS AvgOfPayment, Sum(tbl1Disabled.Payment) AS SumOfPayment " & _
"FROM tbl1Disabled " & _
"GROUP BY tbl1Disabled.CategoryNumber " & _
"HAVING (((tbl1Disabled.CategoryNumber) Is Not Null) AND ((Avg(tbl1Disabled.Payment)) Is Not Null)) "

'Execute query and populate recordset
Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

'If no data, don't bother opening Excel, just quit
If rs1.RecordCount = 0 Then
MsgBox "No data to display", vbInformation + vbOKOnly, "No Data"
GoTo SubExit
End If

'*********************************************
' BUILD SPREADSHEET
'*********************************************
'Create an instance of Excel and start building a spreadsheet
'Early Binding
Set xlApp = Excel.Application

xlApp.visible = True
'Perhaps better off giving a network location??????
Set xlBook = xlApp.Workbooks.Open("D:\New\Database\Resources\Disabled1.xlsx")
'Set xlBook = xlApp.Workbooks.Open("C:\Users\ray\Desktop\Export to Excel\BarChartExample.xlsx")
Set xlSheet = xlBook.Worksheets(1)

With xlSheet
'provide initial value to row counter
i = 23
'Loop through recordset and copy data from recordset to sheet
Do While Not rs1.EOF

.Range("B" & i).Value = Nz(rs1!CategoryNumber, "")
.Range("C" & i).Value = Nz(rs1!CountOfID, 0)
.Range("D" & i).Value = Nz(rs1!AvgOfPayment, 0)
.Range("E" & i).Value = Nz(rs1!SumOfPayment, 0)

i = i + 1
rs1.MoveNext

Loop

'Formulas for total line
'Sum items
'.Range("B" & i).Value = "Total :"
'.Range("B" & i).HorizontalAlignment = xlLeft
'.Range("C" & i).Formula = "=SUM(C23:C" & i - 1 & ")"
'.Range("C" & i).HorizontalAlignment = xlRight

'Sum items
'.Range("D" & i).Value = "Total :"
'.Range("D" & i).HorizontalAlignment = xlLeft
'.Range("E" & i).Formula = "=SUM(E23:E" & i - 1 & ")"
'.Range("E" & i).HorizontalAlignment = xlRight

End With


SubExit:
On Error Resume Next

DoCmd.Hourglass False
xlApp.visible = True
rs1.Close
Set rs1 = Nothing

Exit Sub

SubError:
MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
"An error occurred"
GoTo SubExit
End Sub


Now I am trying to replicate this process for a crosstab query as well but don't know how to go about doing it.

SQL statement for the crosstab query is provided below:

TRANSFORM Count(tbl1Disabled.ID) AS CountOfID
SELECT tbl1Disabled.Province, Avg(tbl1Disabled.Payment) AS AvgOfPayment, Count(tbl1Disabled.Payment) AS CountOfPayment
FROM tbl1Disabled
WHERE (((tbl1Disabled.CategoryNumber) Is Not Null))
GROUP BY tbl1Disabled.Province
PIVOT tbl1Disabled.CategoryNumber;


Any help in doing this would be appreciated.

Thanss

 

[Simon_MT]

You could trying linking Excel into Access.

Simon

 

[JHB]

You need a loop for the horizontal line, use the recordset Fields count.

 

[stopher]

You could trying linking Excel into Access.

Simon

I agree. This method is simple to implement and brings all the virtues of Excel pivots without any coding. Also easy for users to refresh.

 

[Yatiman Idara]

You need a loop for the horizontal line, use the recordset Fields count.

Thank you for that. As I said above, unfortunately I am a bit new to VBA. Would you or anyone else help me with the syntax for creating such a loop. As you can see from my crosstab query, I have set up the Disability Category Number as the Column count. That field has three values (1, 2, and 3).

Also I am searching the net for linking access crosstab query to excel in case I can't get my original method to work.

Thanks

 

[Yatiman Idara]

You need a loop for the horizontal line, use the recordset Fields count.

Hey there,

I think I got it working with your idea.

Basically in my code in between the lines 'Do While Not rs1.EOF' and 'Loop' I removed the .range statements and instead put .Range("A23").CopyFromRecordset rs1

Many thanks to everyone for their quick and concise replies.

 

[JHB]

Good you got it solved - good luck.