Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query adds rather than replaces
Hi, I have the following code in to run a query from a button and this works
great - except for the fact that instead of replacing existing data it places the new data beside it - moving along the columns each time. Please can someone help point out where I have gone wrong. Private Sub CommandButton1_Click() Dim strConnection As String Dim strFullFileName As String, strFolder As String Dim strQueryName As String strQueryName = "Pest-Invoice Due" strFolder = "U:\" strFullFileName = _ strFolder & strQueryName & ".dqy" strConnection = "FINDER;" & strFullFileName With ActiveSheet.QueryTables.Add( _ Connection:=strConnection, _ Destination:=Range("A7")) .Name = strQueryName .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 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query adds rather than replaces
hi
basicly your code is creating a new query each time your code is run. Microsoft query will not overwrite an existing query so it creates a new query beside the old query. solution: archive the code you have now and replace it with update code and attached the update code to the button. assuming that your MSQ is on sheet 1 cell A1..... sub refreshMSQ() Sheets("Sheet1").activate Range("A1").QueryTable.Refresh BackgroundQuery:=False Msgbox "Refresh complete" End sub Adjust sheet name and MSQ range to suit. Regards FSt1 "Leanne" wrote: Hi, I have the following code in to run a query from a button and this works great - except for the fact that instead of replacing existing data it places the new data beside it - moving along the columns each time. Please can someone help point out where I have gone wrong. Private Sub CommandButton1_Click() Dim strConnection As String Dim strFullFileName As String, strFolder As String Dim strQueryName As String strQueryName = "Pest-Invoice Due" strFolder = "U:\" strFullFileName = _ strFolder & strQueryName & ".dqy" strConnection = "FINDER;" & strFullFileName With ActiveSheet.QueryTables.Add( _ Connection:=strConnection, _ Destination:=Range("A7")) .Name = strQueryName .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 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query adds rather than replaces
Thank you so much - I never would have imagined that such a small amount of
code could replace all that I had. If only I could solve my lookup/record change issue! "FSt1" wrote: hi basicly your code is creating a new query each time your code is run. Microsoft query will not overwrite an existing query so it creates a new query beside the old query. solution: archive the code you have now and replace it with update code and attached the update code to the button. assuming that your MSQ is on sheet 1 cell A1..... sub refreshMSQ() Sheets("Sheet1").activate Range("A1").QueryTable.Refresh BackgroundQuery:=False Msgbox "Refresh complete" End sub Adjust sheet name and MSQ range to suit. Regards FSt1 "Leanne" wrote: Hi, I have the following code in to run a query from a button and this works great - except for the fact that instead of replacing existing data it places the new data beside it - moving along the columns each time. Please can someone help point out where I have gone wrong. Private Sub CommandButton1_Click() Dim strConnection As String Dim strFullFileName As String, strFolder As String Dim strQueryName As String strQueryName = "Pest-Invoice Due" strFolder = "U:\" strFullFileName = _ strFolder & strQueryName & ".dqy" strConnection = "FINDER;" & strFullFileName With ActiveSheet.QueryTables.Add( _ Connection:=strConnection, _ Destination:=Range("A7")) .Name = strQueryName .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 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replaces formula to its value | Excel Worksheet Functions | |||
Microsoft Query sometimes adds trailing spaces to fields | Excel Discussion (Misc queries) | |||
VBA replaces formula but adds row in multiplier | Excel Programming | |||
Multiple Edit Replaces | Excel Programming | |||
Multiple Edit Replaces | Excel Programming |