fill one spreadsheet with 2 recordsets

[rwei]

I am trying to use copyFromRecordset to fill one single spreadsheet with TWO separate SQL recordsets.

How do I do this? Or can I do this at all? Thanks.

 

[PHV]

Copy 1st recordset
Find the last row
Copy 2nd recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rwei]

Thanks for the quick answer.

I guess I understand the concept, but can't tranlate it into actual codes - or make it to work right.

I can copy the 1st recordset successfully, and find the last row, but when I copy the 2nd recordset, the previous load disappears from the spreadsheet. What's the right way to copy the 2nd recordset?

 

[PHV]

Can you please post the code you have so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rwei]

It will be something like this:

sSQL = "SELECT * FROM tblMarket1"
Set adoRsA = CreateObject("ADODB.Recordset")
adoRsA.CursorLocation = 3 'adUseClient
adoRsA.CursorType = 1 'adOpenKeyset
adoRsA.LockType = 3 'adLockOptimistic
adoRsA.Open sSQL, adoCnn

LastRow = adoRsA.RecordCount + 1
S1.Worksheets.Add SS1.Worksheets SS1.Worksheets.Count),,1
SS1.ActiveSheet.Name = adoRsA("Market")

SS1.ActiveSheet.Cells.copyfromRecordset adoRsA
adoRsA.Close
Set adoRsA = Nothing

sSQL = "SELECT * From tblMarket2 "
Set adoRsB = CreateObject("ADODB.Recordset")
adoRsB.CursorLocation = 3 'adUseClient
adoRsB.CursorType = 1 'adOpenKeyset
adoRsB.LockType = 3 'adLockOptimistic
adoRsB.Open sSQL, adoCnn

< HOW DO I ADD THE 2ND RECORDSET HERE????? >

SS1.ActiveSheet.Cells(LastRow,1).copyfromRecordset adoRsB
SS1.ActiveSheet.Range("A2").Select
SS1.ActiveWindow.FreezePanes = True

 

[PHV]

You may try something like this:
With SS1.ActiveSheet
For i = 0 To adoRsA.Fields.Count - 1
.Cells(1, i + 1).Value = adoRsA.Fields(i).Name
Next
.Range("A2").CopyFromRecordset adoRsA
LastRow = adoRsA.RecordCount + 1
adoRsA.Close
Set adoRsA = Nothing
sSQL = "SELECT * FROM tblMarket2"
Set adoRsB = CreateObject("ADODB.Recordset")
adoRsB.CursorLocation = 3 'adUseClient
adoRsB.CursorType = 1 'adOpenKeyset
adoRsB.LockType = 3 'adLockOptimistic
adoRsB.Open sSQL, adoCnn
.Cells(LastRow + 1, 1).CopyFromRecordset adoRsB
adoRsB.Close
Set adoRsB = Nothing
.Range("A2").Select
End With
SS1.ActiveWindow.FreezePanes = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rwei]

I tried with your example, then modified a bit (I use Cells, instead of Range during the 1st copy):

With SS1.ActiveSheet
For i = 0 To adoRsB.Fields.Count - 1
.Cells(1,i+1).Value = adoRsB.Fields(i).Name
Next
.Cells.copyFromRecordset adoRsB
LastRow = adoRsB.RecordCount + 1
adoRsB.Close
Set adoRsB = Nothing
Set adoRsC = CreateObject("ADODB.Recordset")

adoRsC.CursorLocation
adoRsC.CursorType = 1
adoRsC.LockType = 3
adoRsC.Open sGLSQL, adoCnn
msgbox lastrow
.Cells(LastRow+1,1).CopyFromRecordset adoRsC
adoRsC.Close
Set adoRsC = Nothing
.Range("A2").Select
End With
SS1.ActiveWindow.FreezePanes = True

With this above code, I see the 1st copy worked fine, then when 2nd copy 'erase' the 1st copy and only fills the A2 cell with 1st column name of the 2nd recordset.

Any idea? Thanks.