Thanks to this list, I now know how to implement an SQL query in a macr
by recording the macro then modifying it.

Now, I have it working but when the results come into the sheet, al
the other cells on the sheet move to the right the width of th
incoming data + 1 (seven columns in this case). Also, after the quer
in the code, I make some formatting changes. These are getting don
before the query finishes and are getting moved to the right also.


With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=mySERVER;UID=myUID;APP=Microsoft
Query;WSID=FBFPQ;Trusted_Connection=Yes" _
, Destination:=Range("A3:F8"))
.CommandText = Array( _

// my query goes here

.Name = "Query from mdc_14"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With

// then I modify the titles and borders with some code like this

Range("B3").Select
ActiveCell.FormulaR1C1 = "MBMScore"
Range("B4").Select
Columns("B:B").EntireColumn.AutoFit
Range("C3").Select
ActiveCell.FormulaR1C1 = "ADLScore"


I made some changes to the default settings below the quer
(.BackgroundQuery etc) to try to make it stop moving eveyrthing but n
luck so far. If you notice those settings are odd, that's why.

*Thanks* for your help last week. I hope this post is as fruitful

--
Message posted from http://www.ExcelForum.com