excel - Multiple SQL query ouput in one Worksheet -
hope 1 me problem facing. issue having difficulties in getting multiple sql queries output in same excel worksheet.
through below code able output of one/ single select query output column header. want display more 1 select query output in same worksheet.
code ---
sub databases() dim rs adodb.recordset dim cn adodb.connection dim ssql1 string ssql1 = "select sum(number_submitted)as number_submitted," & _ "mgr_grp_id,service_ci_id,location_id change_request_enum_f enum_field_cd=11834 , enum_value in (10,11)" & _ "group mgr_grp_id,service_ci_id,location_id" 'ssql2 = "select * change_request_f" set cn = new adodb.connection sheets("sheet4").select selection.clearcontents cn.open "provider=sqloledb.1; uid=userid;pwd=password;initial catalog=bmcdi_dwh;data source=vw-pun-atm-qa26" set rs = new adodb.recordset rs.cursorlocation = aduseclient rs.open ssql1, cn, adopenforwardonly, adlockreadonly, adcmdtext 'check content of rs if rs.eof msgbox ("record set empty. rs.eof = " & rs.eof) else msgbox ("total records: " & rs.recordcount) range("a1").select each qf in rs.fields range("a1").offset(0, coloffset).value = qf.name 'range("a1").offset(1, 0).value = qf.name coloffset = coloffset + 1 next qf range("a2").copyfromrecordset rs rs.close set rs = nothing end if end sub
i split out sub.
sub writers(rs adodb.recordset, startrange range) 'all code here place recordset 'the change use of startrange 'this pass upper left cell want recordset written to. end sub
then can use 2 recordset objects. rs1
, rs2
, call sub
rs1.open sql1, cn... rs2.open sql2, cn... ''check empties writers rs1, range("a1") writers rs2, range("a10")
Comments
Post a Comment