Exporting Query Data to Excel

[Jay]

Hi all,

My question is about sending data to Excel. I've got code to accomplish
what I need to do, but my issue is that it's incredibly slow. Currently, I
use VBA in Access 97 to copy the contents of a query over to Excel and create
a Pivot Table and Chart. The goal is to make report generation uniform for
all users and because although Pivot Tables/Charts are helpful, not everyone
is able to create them quickly or they just don't want to.

The code I've cobbled together from examples on this forum works, but it
takes 5 to 7 minutes for it to run. The speed is not changed if Excel is
already open. Users could be retrieving anywhere from 100 rows to 20,000.
It's possible there is no way to speed up the process, but I'd like to think
there is something I can do to clean it up a bit. I DoCmd.TransferText works
very fast, so I'd like to get something like that working. However, it's
also doing something much simpler than I am. The workbook has not been
created/saved when Access opens Excel. It opens as a new (unsaved) workbook
with default name (ie Book6).

Here's the basics of my code. If there are any glaring issues, or if you
see anything I can do better, please let me know.

' Rename sheets as needed
xlWbk.Sheets("Sheet1").Name = "data"
xlWbk.Sheets("Sheet2").Name = "pivot"
' Set sheets
Set xlWshtData = xlWbk.Worksheets("data")
Set xlWshtPivot = xlWbk.Worksheets("pivot")

' Set the column headings
xlWshtData.Cells(1, 1).Value = "DTL_DATE"
.....
xlWshtData.Cells(1, 16).Value = "FIRST_PASS"

With qdfExcel
.SQL = "SELECT DTL_DATE, ................. " & _
"DATEPART(" & Chr(34) & "ww" & Chr(34) & ", DTL_DATE) as
WEEK, MONTH(DTL_DATE) as MO, YEAR(DTL_DATE) as YR " & _
"FROM qryReportRequested"
Set rstData = .OpenRecordset()
End With

rowNum = 2

' Cycle through the records
rstData.MoveFirst
Do While Not rstData.EOF

xlWshtData.Cells(rowNum, 1).Value = rstData!DTL_DATE
......
xlWshtData.Cells(rowNum, 16).Value = rstData!FIRST_PASS

rstData.MoveNext
rowNum = rowNum + 1
Loop
rstData.Close
dbs.Close

' Set range used for Pivot
xlWshtData.Range(xlWshtData.Cells(1, 1), xlWshtData.Cells(1,
16).End(xlDown)).Name = "AllData"
' Create Pivot
xlWbk.PivotCaches.Add(xlDatabase, "AllData").CreatePivotTable "", "pivotPT",
, xlPivotTableVersion10
' Add Pivot to workbook
xlWbk.Charts.Add

' set the Data Fields (there's about 6 of these)
With xlWbk.ActiveChart.PivotLayout.PivotTable.PivotFields("TRY")
.Orientation = xlPageField
End With

' Display workbook using Excel
xlApp.Visible = True

Alright, that's the end of it. I'm asking for a lot (read my code, fix my
code), but maybe someone can see some stupid move on my part.

Any and all suggestions are appreciated.

Thanks,
Jay

 

[Robert_DubYa]

Without going through your code have you thought about creating a macro to
export to Excel? You could use the shell command to open that file in Excel
on the same event you call your macro with after the macro.

RW

 

[Jay]

I never thought about it, because I don't know how to do that.
But I'll have to look into it.

Thanks for the idea.

Jay