Prev: MsgBox when the cell is empty
Next: koşullu biçimlendirme
From: Simon on 19 Jan 2010 10:16
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
 | 
Pages: 1
Prev: MsgBox when the cell is empty
Next: koşullu biçimlendirme