Export multiple queries into a single spreadsheet in different range of cells

[ria.arora]

Dear All,

I need to export multiple queries into a single spreadsheet in different range of cells. Means one query need to be exported from B2:E2 and second query need to be exported from B10:E10. In this way need to export 18 queries' result into one sheet only on different name range.

If anyone has sample VBA code then please help to post it.

Any help would be appreciated. I am using Access 2007 and need to export data into Excel 2003 format.


Thanks a lot for the help.

Regards
Ria

 

[Alansidman]

This should help you.

http://accessblog.net/2006/07/export-to-excel-range.html

Alan

 

[ria.arora]

Hi Alan,

Sorry I did not get the solution on the blog. Not sure if that link is correct.

If you have the sample code then pls post it in this.

Thanks

 

[Alansidman]

I think that the link must be broken. I have a cached copy of the blog. Here is the text of that blog.

See attached

Cross Post at
http://www.accessforums.net/access/...-into-single-spreadsheet-different-25549.html

Also please read this article.

http://www.excelguru.ca/content.php?184

 

[ria.arora]

Got error Item not found in this collection.

/C

cntRow = 2
cntCol = 2
Do While Not rsTmp.EOF
lngCol = 0
For Each varField In rsTmp.Fields
' appExcel.Cells(cntRow, 1) = rsTmp!Status
' appExcel.Cells(cntRow, 2) = rsTmp!DBT_RM_NAME
' appExcel.Cells(cntRow, 3) = rsTmp!GMIS_CODE
' appExcel.Cells(cntRow, 4) = rsTmp!PBC
' appExcel.Cells(cntRow, 5) = rsTmp!USER_LAST
' appExcel.Cells(cntRow, 6) = rsTmp!USER_FIRST

oBook.Worksheets("Sheet1").Cells(cntRow + 1, cntCol + 1) = IIf(cntRow = 0, rsTmp.Fields(cntCol).Name, varField.Value)
cntCol = cntCol + 1
Next
If cntRow > 0 Then rsTmp.MoveNext
cntRow = cntRow + 1
Loop

/C

 

[ria.arora]

Got error:

Run-time error '3265':

Item not found in this collection.