SQL results manipulation
 

I am trying to write a macro that queries a MS SQL server and kicks out
data in predicable cells -- say start printing results at A10, but I
want to print certain headings above each column as well. AND perhaps
do some calculations and print the results to other worksheets ...

here is what I have:


Sub GetTrans()

Dim i As Integer
Dim iNumCols As Integer, iNumRows As Integer


On Error GoTo ErrHandler

Sheets("Trans").Select
Range("A10").Select

If (Range("A10").Value < vbNullString) Then
iNumRows = Sheets("FuturesTrans").Range("A10").End(xlDown).Ro w
iNumCols = Sheets("FuturesTrans").Range("A10").End(xlToRight) .Column
Sheets("Trans").Range(Cells(1, 1), Cells(iNumRows, iNumCols)).Select
Selection.ClearContents
End If

For i = 1 To ActiveSheet.QueryTables.Count
ActiveSheet.QueryTables(i).Delete
Next

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=SQL\SQL;UID=User;PWD=password;APP=Mi
crosoft® Query;" _
, Destination:=Range("A10"))
.CommandText = Array( _
"SELECT statement here"
)
.Name = "Transactions Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Exit Sub

ErrHandler:
MsgBox Err.Description, vbCritical, "Error: Get Transactions"
End Sub






thanks.


--
sep27
------------------------------------------------------------------------
sep27's Profile: http://www.excelforum.com/member.php...o&userid=33206
View this thread: http://www.excelforum.com/showthread...hreadid=530269