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 |
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 |