Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where do I paste in the following Access SQL code into my routine?
Access code:
SELECT AXA_BILLING1.[Profile Name], Sum(AXA_BILLING1.[Parts In]) AS [SumOfParts In], Sum(AXA_BILLING1.[Parts Out]) AS [SumOfParts Out] FROM AXA_BILLING1 GROUP BY AXA_BILLING1.[Profile Name]; VBA code: Dim conDB As ADODB.Connection Dim recDB As ADODB.Recordset Dim i As Integer Sheets("NDM-Assoc").Select Range("A1:E65500").Select Selection.ClearContents i = 1 Set recDB = New ADODB.Recordset Set conDB = New ADODB.Connection conDB.CursorLocation = adUseClient conDB.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=G: \AUDASTAT\NORWICHU\Spreadsheets\NU-NDMs.mdb;;Jet OLEDB:Database Password=;" recDB.Open "SELECT * FROM [NDM-Assoc] ORDER BY SITEID", conDB Do While Not recDB.EOF Range("F" & i).Value = recDB!Initials Range("G" & i).Value = recDB!Type Range("H" & i).Value = recDB!SiteID If IsNull(recDB!CatOrGEO) Then Range("I" & i).Value = " " Else Range("I" & i).Value = recDB!CatOrGEO End If recDB.MoveNext i = i + 1 Loop recDB.Close Columns("F:I").Select Selection.Copy Range("A1").Select ActiveSheet.Paste Thanks Simon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB code to run a Query in Access and paste results onto an Excelworksheet | Excel Programming | |||
How can I see assembly code for VBA routine? | Excel Programming | |||
Converting a Access routine into an Excel macro? | Excel Discussion (Misc queries) | |||
help w/ generic copy & paste/paste special routine | Excel Programming |