Reading SQL Table into Excel and calculating/inserting group line (with VBA)?
 

Assume I have in a SQL database (Oracle) a table like:

Group Name value1 value2
1 Karl 10 20
1 Paul 3 4
2 Tony 2 0
2 Ben 80 100
2 Peter 1 1
3 ....

Now I want to read this table with VBA and insert it into an Excel sheet.

Moreover after each group a sum line should be calculated and inserted so that the Excel sheet
should look like:

G name V1 V2
1 Karl 10 20
1 Paul 3 4
1 sum 13 24
2 Tony 2 0
2 Ben 80 100
2 Peter 1 1
2 sum 83 101
3 ....
....

How can I code this in VBA ?

Claudia

Reading SQL Table into Excel and calculating/inserting group line
 

This is a 2 part answer. the macro below will add the SUM rows to the
spreadsheet after you get the Database data

The best way of getting the data from the database is to rrecord a macro
while creating the SQL manually.

1) First start recording a macro from the worksheet menu Tools - Macro -
Start Recording.

2) Get the Database data using worksheet menu Data - Import External Data

3) The stop recording Tools - Macro - Stop Recording.

You can then modify the Recorded macro as required to make it more general
purpose.

Sub AddSum()

RowCount = 2
FirstRow = RowCount
Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) < _
Range("A" & (RowCount + 1)) Then

Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Range("B" & (RowCount + 2)) = "SUM"
For ColCount = 3 To 4
Cells(RowCount + 2, ColCount).FormulaR1C1 = _
"=SUM(R" & FirstRow & "C:R" & RowCount & "C)"
Next ColCount

RowCount = RowCount + 4
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"Claudia d'Amato" wrote:

Assume I have in a SQL database (Oracle) a table like:

Group Name value1 value2
1 Karl 10 20
1 Paul 3 4
2 Tony 2 0
2 Ben 80 100
2 Peter 1 1
3 ....

Now I want to read this table with VBA and insert it into an Excel sheet.

Moreover after each group a sum line should be calculated and inserted so that the Excel sheet
should look like:

G name V1 V2
1 Karl 10 20
1 Paul 3 4
1 sum 13 24
2 Tony 2 0
2 Ben 80 100
2 Peter 1 1
2 sum 83 101
3 ....
....

How can I code this in VBA ?

Claudia

Reading SQL Table into Excel and calculating/inserting group line
 

Use Data/Subtotal

JB
http://boisgontierjacques.free.fr
On 11 jan, 14:51, Joel wrote:
This is a 2 part answer. *the macro below will add the SUM rows to the
spreadsheet after you get the Database data

The best way of getting the data from the database is to rrecord a macro
while creating the SQL manually.

1) First start recording a macro from the worksheet menu Tools - Macro -
Start Recording.

2) Get the Database data using worksheet menu Data - Import External Data

3) The stop recording Tools - Macro - Stop Recording.

You can then modify the Recorded macro as required to make it more general
purpose.

Sub AddSum()

RowCount = 2
FirstRow = RowCount
Do While Range("A" & RowCount) < ""
* *If Range("A" & RowCount) < _
* * * Range("A" & (RowCount + 1)) Then

* * * Rows(RowCount + 1).Insert
* * * Rows(RowCount + 1).Insert
* * * Rows(RowCount + 1).Insert
* * * Range("B" & (RowCount + 2)) = "SUM"
* * * For ColCount = 3 To 4
* * * * *Cells(RowCount + 2, ColCount).FormulaR1C1 = _
* * * * * * "=SUM(R" & FirstRow & "C:R" & RowCount & "C)"
* * * Next ColCount

* * * RowCount = RowCount + 4
* * * FirstRow = RowCount
* *Else
* * * RowCount = RowCount + 1
* *End If
Loop

End Sub



"Claudia d'Amato" wrote:
Assume I have in a SQL database (Oracle) a table like:

Group Name value1 value2
1 * * Karl * 10 * * 20
1 * * Paul * *3 * * *4
2 * * Tony * *2 * * *0
2 * * Ben * *80 * *100
2 * * Peter * 1 * * *1
3 ....

Now I want to read this table with VBA and insert it into an Excel sheet.