software

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 IntegerOn Error GoTo ErrHandler

Sheets(quot;Transquot;).Select
Range(quot;A10quot;).Select

If (Range(quot;A10quot;).Value lt;gt; vbNullString) Then
iNumRows = Sheets(quot;FuturesTransquot;).Range(quot;A10quot;).End(xlDown).Ro w
iNumCols = Sheets(quot;FuturesTransquot;).Range(quot;A10quot;).End(xlToRight) .Column
Sheets(quot;Transquot;).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:= _
quot;ODBC;DRIVER=SQL Server;SERVER=SQL\SQL;UID=User;PWD=password;APP=Mi
crosoft® Query;quot; _
, Destination:=Range(quot;A10quot;))
.CommandText = Array( _
quot;SELECT statement herequot;
)
.Name = quot;Transactions Queryquot;
.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 WithExit Sub

ErrHandler:
MsgBox Err.Description, vbCritical, quot;Error: Get Transactionsquot;
End Sub
thanks.--
sep27
------------------------------------------------------------------------
sep27's Profile: www.excelforum.com/member.php...oamp;userid=33206
View this thread: www.excelforum.com/showthread...hreadid=530269