Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I use 2 diff. recordsets without opening another db connec
Please go through my code below:
This is my objective: I am trying to execute two different SQL statements and populate the output of each statement in two worksheets - Sheet1, Sheet2. I am able to populate Sheet1 with the information. However, I see a blank Sheet2 when this macro executes. Can anyone tell me what the problem is? Thanks much for your time! __________________________________________________ ______________ Public Sub test() Dim db As Database, rs As Recordset Dim targetrange As Range Set db = OpenDatabase("c:\abc.mdb", False, True, "MS Access;PWD=12345") Set rs = db.OpenRecordset("select * from goal") Set targetrange = Range("a1") Set targetrange = targetrange.Cells(1, 1) For i = 0 To rs.Fields.Count - 1 targetrange.Offset(0, i).Value = rs.Fields(i).Name Next ' write recordset colCounter = 0 rowCounter = 1 targetrange.Offset(rowCounter, colCounter).CopyFromRecordset rs Sheets("Sheet2").Select Set rs = db.OpenRecordset("select territory from goal") Set targetrange = Range("a1") Set targetrange = targetrange.Cells(1, 1) For i = 0 To rs.Fields.Count - 1 targetrange.Offset(0, i).Value = rs.Fields(i).Name Next ' write recordset colCounter = 0 rowCounter = 1 targetrange.Offset(rowCounter, colCounter).CopyFromRecordset rs Set rs = Nothing db.Close Set db = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I use 2 diff. recordsets without opening another db connec
your main problem is that you select the worksheet, whereas you need to activate it. it could/should be done without activating anything: Public Sub test() Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim i As Integer Set dbs = DAO.OpenDatabase("c:\abc.mdb", False, True, "MS Access;PWD=12345") Set rst = dbs.OpenRecordset("select * from goal") With ThisWorkbook.Worksheets("sheet1").Range("A1") 'headers For i = 0 To rst.Fields.Count - 1 .Offset(0, i).Value = rst.Fields(i).Name Next 'records .Offset(1, 0).CopyFromRecordset rst End With Set rst = dbs.OpenRecordset("select territory from goal") With ThisWorkbook.Worksheets("sheet2").Range("A1") 'headers For i = 0 To rst.Fields.Count - 1 .Offset(0, i).Value = rst.Fields(i).Name Next ' write recordset .Offset(1, 0).CopyFromRecordset rst End With rst.Close: Set rst = Nothing dbs.Close: Set dbs = Nothing End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam vrk1 wrote : Please go through my code below: This is my objective: I am trying to execute two different SQL statements and populate the output of each statement in two worksheets - Sheet1, Sheet2. I am able to populate Sheet1 with the information. However, I see a blank Sheet2 when this macro executes. Can anyone tell me what the problem is? Thanks much for your time! __________________________________________________ ______________ Public Sub test() Dim db As Database, rs As Recordset Dim targetrange As Range Set db = OpenDatabase("c:\abc.mdb", False, True, "MS Access;PWD=12345") Set rs = db.OpenRecordset("select * from goal") Set targetrange = Range("a1") Set targetrange = targetrange.Cells(1, 1) For i = 0 To rs.Fields.Count - 1 targetrange.Offset(0, i).Value = rs.Fields(i).Name Next ' write recordset colCounter = 0 rowCounter = 1 targetrange.Offset(rowCounter, colCounter).CopyFromRecordset rs Sheets("Sheet2").Select Set rs = db.OpenRecordset("select territory from goal") Set targetrange = Range("a1") Set targetrange = targetrange.Cells(1, 1) For i = 0 To rs.Fields.Count - 1 targetrange.Offset(0, i).Value = rs.Fields(i).Name Next ' write recordset colCounter = 0 rowCounter = 1 targetrange.Offset(rowCounter, colCounter).CopyFromRecordset rs Set rs = Nothing db.Close Set db = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plotting column chart whch colors the bars diff for diff comm | Charts and Charting in Excel | |||
SUMIF formula required to search for 2 diff values in 2 diff colum | Excel Worksheet Functions | |||
how you make links between diff. cells on diff. work sheets | Excel Worksheet Functions | |||
opening diff. worksheets in opened workbook | Excel Programming | |||
ADO Recordsets | Excel Programming |