Query results make entire sheet's cells move

[chuckiej]

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

 

[Dick Kusleika]

Chuckiej

Change these two lines

..RefreshStyle = xlOverwriteCells
..Refresh BackgroundQuery:=False

The RefreshStyle you have now will insert cells for the table and move your
existing data right and down to accommodate the table. Changing to
overwrite will wipe out any data in its path.

Set your BackgroundQuery property to whatever you want for user-interface
refreshes. The BackgroundQuery argument to Refresh in code will override
that. You want it to be False so that macro execution pauses until the
table is created.

 

[chuckiej]

Thanks Dick! This forum is amazing. I hope I can eventually contribut
what I am learning