Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Results, Totals, Updates, How to glue this all together ?
I've inherited a sheet and SQL queries that are used to produce a current sales order status. Historically its been handled manually step by step and I now need to glue it all together but seem to be going in circles. An SQL query returns the data to the sheet. The number of rows can grow or shrink (+/- 100 or more rows typically.) I need to total various columns. I want a one button update and recaculate the sheet ! I've gone round and round tables, pivot tables, macros and some simple VBA but really have gone blind looking at this. Al I want is a button that calls the SQL, Re-sizes and recalculates the sheet totals. Any help would be appreciated. -- Imerge Admin ------------------------------------------------------------------------ Imerge Admin's Profile: http://www.excelforum.com/member.php...o&userid=24240 View this thread: http://www.excelforum.com/showthread...hreadid=378506 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Results, Totals, Updates, How to glue this all together ?
If all you need to do is simple sums of particular columns, I find it is best
to put the querytable results on a sheet of its own (no other cells used) and then sum an entire column, e.g. SUM("C:C"). This can be put as a worksheet cell formula so you do not need any code to calculate the sum. COUNT will work, too, if you need counts; as will AVERAGE and some other functions. By the way, these will also ignore any non-numeric column headers you might have. So then all you need your button to do is to refresh the querytable results: Public Sub CommandButton1_Click() Sheets("QuerySheet").QueryTables(1).Refresh End Sub The button will call the SQL; the querytable will automatically resize, and your cell formulas do not need to be changed: they still sum the specified row (using the new data). "Imerge Admin" wrote: I've inherited a sheet and SQL queries that are used to produce a current sales order status. Historically its been handled manually step by step and I now need to glue it all together but seem to be going in circles. An SQL query returns the data to the sheet. The number of rows can grow or shrink (+/- 100 or more rows typically.) I need to total various columns. I want a one button update and recaculate the sheet ! I've gone round and round tables, pivot tables, macros and some simple VBA but really have gone blind looking at this. Al I want is a button that calls the SQL, Re-sizes and recalculates the sheet totals. Any help would be appreciated. -- Imerge Admin ------------------------------------------------------------------------ Imerge Admin's Profile: http://www.excelforum.com/member.php...o&userid=24240 View this thread: http://www.excelforum.com/showthread...hreadid=378506 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Results, Totals, Updates, How to glue this all together ?
Try attaching this macro to a Command Button...
Macro assumes you have the SQL query in Row 2, col 1 (cell A2) Make sure "Data Range Properties" has "Allow Background Refresh" checked Sub Macro1() Dim col, nd_row As Integer Sheets("Download II").Select '(Select Worksheet) Range("A2").Select '(Select base cell for query) Selection.QueryTable.Refresh BackgroundQuery:=False '(Refreshes Query) For col = 2 To 7 '(Assumes contiguouse series of columns to sum) Cells(2, col).Select Selection.End(xlDown).Select nd_row = Selection.Row '(Determines last row in selected column) Cells(nd_row + 2, col).Select ActiveCell.FormulaR1C1 = "=SUM(R[-" & nd_row & "]C:R[-2]C)" 'Sticks in Sum formula for column) Next col '(Do next column) End Sub Naturally you'll have to adjust to fit your specific query/column structure (i.e. which columns need summing) but this should at least point you in the right direction.. "Imerge Admin" wrote: I've inherited a sheet and SQL queries that are used to produce a current sales order status. Historically its been handled manually step by step and I now need to glue it all together but seem to be going in circles. An SQL query returns the data to the sheet. The number of rows can grow or shrink (+/- 100 or more rows typically.) I need to total various columns. I want a one button update and recaculate the sheet ! I've gone round and round tables, pivot tables, macros and some simple VBA but really have gone blind looking at this. Al I want is a button that calls the SQL, Re-sizes and recalculates the sheet totals. Any help would be appreciated. -- Imerge Admin ------------------------------------------------------------------------ Imerge Admin's Profile: http://www.excelforum.com/member.php...o&userid=24240 View this thread: http://www.excelforum.com/showthread...hreadid=378506 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Glue Cells | Excel Discussion (Misc queries) | |||
Pivot Totals: Group totals different from Grand totals | Excel Discussion (Misc queries) | |||
How can I "glue" a picture in a cell and treat it as content? | Excel Discussion (Misc queries) | |||
Totals of calculated field in pivot table give incorrect results | Excel Worksheet Functions | |||
Glue/Lock cells to external data before refresh | Excel Discussion (Misc queries) |