Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query results make entire sheet's cells move
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query results make entire sheet's cells move
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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "chuckiej " wrote in message ... Thanks to this list, I now know how to implement an SQL query in a macro by recording the macro then modifying it. Now, I have it working but when the results come into the sheet, all the other cells on the sheet move to the right the width of the incoming data + 1 (seven columns in this case). Also, after the query in the code, I make some formatting changes. These are getting done 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 query (.BackgroundQuery etc) to try to make it stop moving eveyrthing but no 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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query results make entire sheet's cells move
Thanks Dick! This forum is amazing. I hope I can eventually contribut
what I am learning -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make cells not move in a worksheet | Excel Worksheet Functions | |||
Move entire row if it contains specified text. | Excel Discussion (Misc queries) | |||
How do I make the cursor move down 8 cells each time i press enter | Excel Worksheet Functions | |||
How can I make the cursor move down 8 cells each time I press ente | Setting up and Configuration of Excel | |||
How can I make the cursor move down 8 cells each time I press ente | Setting up and Configuration of Excel |