Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to pick Home and Away results from a list | Excel Worksheet Functions | |||
Paste results consecutively within column (based off other worksheet) | Excel Worksheet Functions | |||
abdualmohsn | Excel Discussion (Misc queries) | |||
Date stamp formula results? Tricky problem? | Excel Discussion (Misc queries) | |||
calculating results in formulas | Excel Discussion (Misc queries) |