Exporting multiple queries to Excel VBA prob

[J4mieG]

Hello, my first post, please can somebody help me with the following problem ?

I am writing a program which will allow me to export data from multiple queries into excel,( by calling the fields from each DAO recordset rather than transferspreadsheet method ). However, everytime I reach the 6th recordset to open and obtain data, the code fails....

Error message : Runtime error 3061
Too Few Parameters. Expected 1.

and it breaks on the following line of code :

Set Rst6 = Dbs.OpenRecordset("QrySalesbyChannelDlr")

Is there a limit to the number of recordsets you can access in a query

Any help greatly appreciated as deadline for this program passed

'Territory report generator

Dim Dbs As DAO.Database
Dim ObjXL As Excel.Application
Dim ObjWkb As Excel.Workbook
Dim ObjSht As Excel.Worksheet
Dim Rst1, Rst2, Rst3, Rst4, Rst5 As DAO.Recordset
Dim Rst6, Rst7, Rst8, Rst9, Rst10 As DAO.Recordset
Dim Dir, File, FullDir As String
Dim IntC1 As Integer


Dir = "W:\Maz Urban Science\Development"
File = "SchoolReportTemplate.xls"
FullDir = Dir & "\" & File

DoCmd.OpenQuery "QrySelectTerritory"

Set Dbs = CurrentDb
Set Rst1 = Dbs.OpenRecordset("TblSelectTerritory")


Select Case MsgBox("Confirm you wish to generate School Report files ?", vbYesNo, "School Report Files")
Case vbYes
DoCmd.Hourglass True


Rst1.MoveFirst
Do Until Rst1.EOF

Me.TxtDealer = (Rst1![DealerNo])

'Run the Cross tab make table queries now !

DoCmd.OpenQuery "QrySVOCtabData", acViewNormal
DoCmd.OpenQuery "QryTerSVOCtabData", acViewNormal
DoCmd.OpenQuery "QryNatSvoCtabData", acViewNormal
DoCmd.OpenQuery "QryDlrSalesbyMthCtabData", acViewNormal

Set ObjXL = New Excel.Application
Set ObjWkb = ObjXL.Workbooks.Open(FullDir)


'========================================================

Set Rst2 = Dbs.OpenRecordset("QrySVOCtab")
Rst2.MoveFirst

Set ObjSht = ObjWkb.Worksheets("Sales")

With ObjSht
.Cells(21, 3) = (Rst2![jan])
.Cells(21, 4) = (Rst2![feb])
.Cells(21, 5) = (Rst2![mar])
.Cells(21, 6) = (Rst2![apr])
.Cells(21, 7) = (Rst2![may])
.Cells(21, 8) = (Rst2![jun])
.Cells(21, 9) = (Rst2![jul])
.Cells(21, 10) = (Rst2![aug])
.Cells(21, 11) = (Rst2![sep])
.Cells(21, 12) = (Rst2![Oct])
.Cells(21, 13) = (Rst2![nov])
.Cells(21, 14) = (Rst2![dec])
End With

Rst2.MoveNext

With ObjSht
.Cells(22, 3) = (Rst2![jan])
.Cells(22, 4) = (Rst2![feb])
.Cells(22, 5) = (Rst2![mar])
.Cells(22, 6) = (Rst2![apr])
.Cells(22, 7) = (Rst2![may])
.Cells(22, 8) = (Rst2![jun])
.Cells(22, 9) = (Rst2![jul])
.Cells(22, 10) = (Rst2![aug])
.Cells(22, 11) = (Rst2![sep])
.Cells(22, 12) = (Rst2![Oct])
.Cells(22, 13) = (Rst2![nov])
.Cells(22, 14) = (Rst2![dec])
End With

Rst2.Close


'=======================================================
Set Rst3 = Dbs.OpenRecordset("QryTerSvOCtab")
Rst3.MoveFirst

Set ObjSht = ObjWkb.Worksheets("Data")

With ObjSht
.Cells(3, 2) = (Rst3![jan])
.Cells(3, 3) = (Rst3![feb])
.Cells(3, 4) = (Rst3![mar])
.Cells(3, 5) = (Rst3![apr])
.Cells(3, 6) = (Rst3![may])
.Cells(3, 7) = (Rst3![jun])
.Cells(3, 8) = (Rst3![jul])
.Cells(3, 9) = (Rst3![aug])
.Cells(3, 10) = (Rst3![sep])
.Cells(3, 11) = (Rst3![Oct])
.Cells(3, 12) = (Rst3![nov])
.Cells(3, 13) = (Rst3![dec])
End With

Rst3.MoveNext

With ObjSht
.Cells(4, 2) = (Rst3![jan])
.Cells(4, 3) = (Rst3![feb])
.Cells(4, 4) = (Rst3![mar])
.Cells(4, 5) = (Rst3![apr])
.Cells(4, 6) = (Rst3![may])
.Cells(4, 7) = (Rst3![jun])
.Cells(4, 8) = (Rst3![jul])
.Cells(4, 9) = (Rst3![aug])
.Cells(4, 10) = (Rst3![sep])
.Cells(4, 11) = (Rst3![Oct])
.Cells(4, 12) = (Rst3![nov])
.Cells(4, 13) = (Rst3![dec])
End With

Rst3.Close


'=====================================================

Set Rst4 = Dbs.OpenRecordset("QryNatSvoCtab")
Rst4.MoveFirst

Set ObjSht = ObjWkb.Worksheets("Data")

With ObjSht
.Cells(9, 2) = (Rst4![jan])
.Cells(9, 3) = (Rst4![feb])
.Cells(9, 4) = (Rst4![mar])
.Cells(9, 5) = (Rst4![apr])
.Cells(9, 6) = (Rst4![may])
.Cells(9, 7) = (Rst4![jun])
.Cells(9, 8) = (Rst4![jul])
.Cells(9, 9) = (Rst4![aug])
.Cells(9, 10) = (Rst4![sep])
.Cells(9, 11) = (Rst4![Oct])
.Cells(9, 12) = (Rst4![nov])
.Cells(9, 13) = (Rst4![dec])
End With

Rst4.MoveNext

With ObjSht
.Cells(10, 2) = (Rst4![jan])
.Cells(10, 3) = (Rst4![feb])
.Cells(10, 4) = (Rst4![mar])
.Cells(10, 5) = (Rst4![apr])
.Cells(10, 6) = (Rst4![may])
.Cells(10, 7) = (Rst4![jun])
.Cells(10, 8) = (Rst4![jul])
.Cells(10, 9) = (Rst4![aug])
.Cells(10, 10) = (Rst4![sep])
.Cells(10, 11) = (Rst4![Oct])
.Cells(10, 12) = (Rst4![nov])
.Cells(10, 13) = (Rst4![dec])
End With

Rst4.Close


'===================================================

Set Rst5 = Dbs.OpenRecordset("QryDlrSalesbyMthCTab")
Rst5.MoveFirst


IntC1 = 10

Do Until Rst5.EOF

Set ObjSht = ObjWkb.Worksheets("Sales")

With ObjSht
.Cells(IntC1, 19) = (Rst5![Model])
.Cells(IntC1, 20) = (Rst5![jan])
.Cells(IntC1, 21) = (Rst5![feb])
.Cells(IntC1, 22) = (Rst5![mar])
.Cells(IntC1, 23) = (Rst5![apr])
.Cells(IntC1, 24) = (Rst5![may])
.Cells(IntC1, 25) = (Rst5![jun])
.Cells(IntC1, 26) = (Rst5![jul])
.Cells(IntC1, 27) = (Rst5![aug])
.Cells(IntC1, 28) = (Rst5![sep])
'.Cells(IntC1, 29) = (Rst5![Oct])
'.Cells(IntC1, 30) = (Rst5![nov])
'.Cells(IntC1, 31) = (Rst5![dec])
End With

IntC1 = IntC1 + 1
Rst5.MoveNext

Loop

Rst5.Close


'=====================================================

Set Rst6 = Dbs.OpenRecordset("QrySalesbyChannelDlr")
Rst6.MoveFirst


IntC1 = 18

Do Until Rst6.EOF

Set ObjSht = ObjWkb.Worksheets("Data")

With ObjSht
.Cells(IntC1, 2) = (Rst5![Model])
.Cells(IntC1, 3) = (Rst5![jan])
.Cells(IntC1, 4) = (Rst5![feb])
.Cells(IntC1, 5) = (Rst5![mar])
End With

IntC1 = IntC1 + 1
Rst6.MoveNext

Loop

Rst6.Close


'=====================================================

ObjWkb.SaveAs "W:\Maz Urban Science\Development\Test\" & Rst1![DealerNo] & "PerformanceReport-" & Format(Date, "dd mmm") & ".xls"
ObjWkb.Close True


Rst1.MoveNext
Loop

Rst1.Close
Set Rst1 = Nothing


Dbs.Close
Set Dbs = Nothing

Set ObjWkb = ObjXL.Workbooks.Open(FullDir)

ObjWkb.Close

Case vbNo
End Select

MsgBox ("All actions complete")
DoCmd.Hourglass False

DoCmd.SetWarnings True

End Sub

 

[smercer]

I think you have jumped into the deep end of the pool. Have you tried having a link by going file-->Get External Data-->link Tables. You then go down to "files of type" and choose "Microsoft Excel(*.xls)".

You can then change your queries to append queries and then your done.

I have not done this before but it should work in theory.

 

[J4mieG]

Smercer - Thanks for your reply, but not sure how using link tables will export data to excel ?

I have a Excel Template, in which I want to extract data from multiple access queries into various locations on the template. It worked fine for the first 5 recordsets until I added Rst6 - now it doesnt want to work at all - with the error message as described below...

Any further suggestions ?!

 

[smercer]

Smercer - Thanks for your reply, but not sure how using link tables will export data to excel ?
...
Any further suggestions ?!

A link from Access to Excel is treated like a linked table (which in turn is treated like a normal table). therefore it can then have a append query run on it.

 

[J4mieG]

Thanks Smercer, I see what you mean - however, this would only work if I had one sheet in excel ( used as a linked table ) which held all the data...
Whereas my problem is that the data needs to be inserted into the Excel template in various sheets in various locations.

I did ponder the "append all into one table on a sheet and use formulas to extract the data" method ( which you are kinda suggesting ) But this would increase the file size too much, plus there are too many fields ( over 10 queries ) plus also I would be downloading tons of unneccessary data.

Thanks again, but any other suggestions ?! ( using vba ? )

 

[smercer]

Try doing a macro, then save it as a module. You would then have the vb coding necessary for your work

 

[RoyVidar]

Does the sixth query contain any parameters?

If it for instance contains parameters from an open form, you could try something like this:

dim rs6 as dao.recordset
dim qdf as dao.querydef
dim prm as dao.parameter

set qdf=currentdb.querydefs("QrySalesbyChannelDlr")
for each prm in qdf.parameters
prm.value=eval(prm.name)
next prm
set rs6=qdf.openrecordset()

 

[J4mieG]

Smercer - "Try doing a macro, then save it as a module. You would then have the vb coding necessary for your work"

I'm not sure what you are relating to here

Hi Roy,

Thanks for your response.

Yes the 6th query does contain parameters from an open form (me.txtdealer and me.txtterritory ) - I will try your method and see if this works...do you know if there is an upper limit to the number of recordsets you can work with in one sub procedure ?

 

[RoyVidar]

I don't think there is one, at least I've never encountered one, but I don't think I've used more than three or four recordsets in one module Edit: meant routine, not module.

 

[Adrianna]

Roy is right on the money with declaring your parameters upfront. I kept getting the No Record or To Few Parameters Expected and I was pulling me hair out.

I'm using queries that are predefined, but I send 28 of them out to separate worksheets within one workbook, naming the worksheets according the the data contained, formating the headers, and bordering the data.

I did run into a problem were the name being passed to the worksheet was to long and I had to learn to declare my parameters prior to sending over the recordset, but I've never encountered any limits.

Can I ask why you are passing so many different recordsets? I actually grab the data that I need and use a nested query on an established query to set my recordset, then I use one more recordset to organize my data, and then I pass the 2nd query into the Export2Excel module. Then when the call to the module is complete I increment the first record set, set the 2nd recordset = Nothing and reuse it for another manupilation of the first dataset.

So, if there is a limit to the number of recordsets...you may want to consider reusing one of the recordsets that you'd alreadt dumped.

 

[J4mieG]

Adrianna, that sounds perfect ! ( Thanks also Roy ), I havent had a chance to try re-writing the code ( to first declare the query parameters ), but will give this a go. Sounds as though you've encountered exactly the same problem, so believe this should now work.


"Can I ask why you are passing so many different recordsets? I actually grab the data that I need and use a nested query on an established query to set my recordset, then I use one more recordset to organize my data, and then I pass the 2nd query into the Export2Excel module. Then when the call to the module is complete I increment the first record set, set the 2nd recordset = Nothing and reuse it for another manupilation of the first dataset.

So, if there is a limit to the number of recordsets...you may want to consider reusing one of the recordsets that you'd alreadt dumped."

Would it be possible though to send me an example ?

I am currently taking data from so many queries because I am building 15 graphs in the Excel template. Each graph has its range set on varying sheets on the template, and I export the data from Access into these ranges ( which inturn refreshes the graphs )...I have to do this 160 times.

Open to any ideas to speed up the code !

 

[Adrianna]

Wow J4mie,
160 refreshes sounds crazy. I use my Call to Export for several things....the original use is a "Rollup" of data by "Location" for each "IT Asset" in the database. So in essense, it loops through a predetermined "Location", looping through each IT ASSET, but Asset Category ( I have Hardware, Software, and General Information all roll into individual workbooks, with multiple worksheets), and then selects only the fields that I want visible. Then it sends the headers, exports the data, and then formats the table with borders and autofit.
Later, I changes the code to loop through several queries (varying location, varying queries) which was much simpler them my first task.
I will see if I can send a simple of the second part, but it might take a while. I could send the code....as it does not reveal data contents, but having the tables and seeing the process run is sometimes more helpful. I'm swamped at work and am being stolen away for the weekend, but let me know if code is sufficent and I will post or e-mail something.
I have to admit that there were a lot of bugs that had to be worked out....because I'd seem other people examples for multi-page exports...and low and behold...they did not work. I got all sorts of Excel errors, so there are a few elements that I was not able to make dynamic (like the number of exports). Excel 2003 (possible other versions too) does something strange with Sheet numbering. If you test for blank pages and wait to add sheets when you run out...the numbering of the sheets ends up really screwy, making is hard to call on them. I create all of the required sheets upfront....then press forward with the export.
My code isn't well documented at the moment because I just haven't had time.