Hey Jez.
You might want to consider the following...
re-define your subroutine to take 2 arguements...
like so,
Sub GetASVNDistrict(sQRY as String, targetCells as Range )
....
end sub
where sQRY is the SQL statement you list down below.
Whatever calls this routine needs to put this together instead of
in the body of this sub.
Same thing goes for the parameter targetCells ... pass the routne
a reference to a cell like this :
call GetASVNDistrict(sQRY, Sheet4.Range("B5") ) ' this is just an
example...
The relevant statements in the original body are :
rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet4.Range("B5").CopyFromRecordset rsDW
change these statements to reflect the input parameters... like so,
rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
targetCells.CopyFromRecordset rsDW
you're utilizing the .CopyFromRecordset sub which you can apply to
*any* range. In the example you cite, it goes to a static location -
Sheet4.Range("B5") ... if you cange it to an input parameter, you can
tell the sub to reference anywhere you want. The tradeoff is - the calling
function (sub) has to supply that info. (same thing goes for the SQL
statement.)
Hope this helps,
Chad
"Jez" wrote:
Hi, This is my vb code to bring data from access to excel. How is it possible
to duplicate this and bring in more than 1 sql and paste elsewhere on the
sheet? this below works for the one sql written, how can I write another sql
and paste on another cell in the sheet?
Sub GetASVNDistrict()
On Error GoTo Err:
strDWFilePath = "H:\NCHO\Housing Services\Data Warehouse\HSG Data
Warehouse.mdb"
Set cnnDW = New ADODB.Connection
Set rsDW = New ADODB.Recordset
Sheet4.Range("B5:BB22").ClearContents
cnnDW.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDWFilePath & ";"
sQRY = "TRANSFORM Count([qryNoAccess(byAppt)].WRNumber) AS
CountOfWRNumber " & _
"SELECT [qryNoAccess(byAppt)].CouncilName " & _
"FROM [qryNoAccess(byAppt)] " & _
"WHERE ((([qryNoAccess(byAppt)].BANumber) < 'HSG0008 20') And
(([qryNoAccess(byAppt)].AppointmentOutcomeID) = 'N') And
(([qryNoAccess(byAppt)].ActionTypeID) = 'AS')) " & _
"GROUP BY [qryNoAccess(byAppt)].CouncilName " & _
"PIVOT [qryNoAccess(byAppt)].Week"
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet4.Range("B5").CopyFromRecordset rsDW
rsDW.Close
cnnDW.Close
Set rsDW = Nothing
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error,
vbCritical, "HSG NA Trending"
MsgBox VBA.Err
End Sub
thanks :-)
Jez