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

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

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