Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bring Queries from Access into Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bring Queries from Access into Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access Queries not seen in Excel | Excel Discussion (Misc queries) | |||
Access Queries into Excel | Excel Worksheet Functions | |||
Queries from Excel 2003 to Access | Excel Discussion (Misc queries) | |||
Running SQL queries for Access using Excel VBA | Excel Programming | |||
Excel Driven Access Queries | Excel Programming |