Excel Export Efficiency

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a mod that exports data into excel for me. It runs 49 separate
parameter queries and copies the data directly into excel. Here is an example
of the fist two queries that I am running

-------------------------------------------------
Set qdf = db.QueryDefs("Destination1")
qdf.Parameters("Destination") = varDestCode

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
strRange = "A6:F25"
objSht.Range(strRange).ClearContents
objSht.Range(strRange).CopyFromRecordset rs

Set qdf = db.QueryDefs("Destination2")
qdf.Parameters("Destination") = varDestCode

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
strRange = "A32:F51"
objSht.Range(strRange).ClearContents
objSht.Range(strRange).CopyFromRecordset rs
------------------------------------------------

Is there a better way to do this? Each query is called
Destination1...2....3....49. The parameter is set by a dropdown box on the
form. If you can imagine, doing this 49 times is a bit redundant.

Any ideas?
 
There is but I need to ask another question which might thwart your plans.

I notice that you are selecting specific ranges to place your queries' data.
Are these set distances apart? IE the first set is placed into row 6 and the
next into row 32. Will each set always be 26 rows apart? if so the the
follwoing doe will work for you, other wise you'll have to do it the hard
unless you want a lesson in arrays *shakes head*. Also I've added extra Dim
statements you may need for the variables I've added.

Any questions email me: mr dot smith at trearc dot com

Good luck.

****************************

Dim i As Integer
Dim strQueryName As String
Dim intStartRow As Integer
Dim intEndRow As Integer

intRow = 6
intEndRow = 25

For i = 1 To 49

strQueryName = "Destination" & i

Set qdf = db.QueryDefs(strQueryName)
qdf.Parameters(strQueryName) = varDestCode

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
strRange = "A" & intStartRow & ":F" & intEndRow
objSht.Range(strRange).ClearContents
objSht.Range(strRange).CopyFromRecordset rs

intRow = intRow + 26
intEndRow = intEndRow + 26

Next i

****************************
 
Agreed.

This is only (or rather, particularly) necessary if the ranges are not even
distances apart. I thought I'd spare the OP the hassle of having to get
their head around that too. However you demo shows it a in way esay enough
to digest.

Cheers.
 
Back
Top