SQL Results, Totals, Updates, How to glue this all together ?

[Imerge Admin]

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.

 

[Guest]

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).

 

[Guest]

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..