Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.scripting.vbscript,microsoft.public.dotnet.languages.vb
|
|||
|
|||
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 |
#2
Posted to microsoft.public.scripting.vbscript,microsoft.public.dotnet.languages.vb,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.scripting.vbscript,microsoft.public.dotnet.languages.vb,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading SQL Table into Excel and calculating/inserting group line (with VBA)? | Excel Discussion (Misc queries) | |||
Pivot table reports - repeat the group name on each line | Excel Discussion (Misc queries) | |||
Excel 2000 reading the group names from a pivot table | Excel Programming | |||
Reading a text file line by line | Excel Programming | |||
Excel 2000 Hanging while reading large file with Line Input | Excel Programming |