Android

The best way to do this is to not Add a QueryTable every time you want to
run it. You can just change some properties of your QT and Refresh it. It
looks like you're just changing the connection string and not the sql. See
here

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

Yours will be a little different than what's on that page. You need to
extract the old database and directory and replace it with the new one.
Here's an example:

Dim OldReport As String
Dim DBQStart As Long
Dim DBQEnd As Long
Dim OldDirect As String
Dim DefStart As Long
Dim DefEnd As Long

Const sDBQ As String = "DBQ="
Const sDef As String = "DefaultDir="

With Sheet1.QueryTables(1)
DBQStart = InStr(1, .Connection, sDBQ) + Len(sDBQ)
DBQEnd = InStr(DBQStart, .Connection, ";")
OldReport = Mid(.Connection, DBQStart, DBQEnd - DBQStart)

DefStart = InStr(1, .Connection, sDef) + Len(sDef)
DefEnd = InStr(DefStart, .Connection, ";")
OldDirect = Mid(.Connection, DefStart, DefEnd - DefStart)

.Connection = Left(.Connection, DBQStart - 1) _
& Replace(.Connection, OldReport, _
ReportFileName, DBQStart, 1)
.Connection = Left(.Connection, DefStart - 1) _
& Replace(.Connection, OldDirect, _
DataFileDirectory, DefStart, 1)

.Refresh

End With

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Android" wrote in message
...
I'm using thecode below (derived using Record Macro, and them modified to
include file and directory name variables) to extract data from an Access
database table and copy to a worksheet called "LinkedData3".

It works, but it inserts the data, and so the sheet grows with every
refresh. Could someone tell me how I can change the code to "replace" the
full content of the sheet rather than "insert"?

Regards...
================================================== =====

With
Worksheets("LinkedData3").QueryTables.Add(Connecti on:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & ReportFileName &
";DefaultDir=
&" & DataFileDirectory & ";DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout" _
), Array("=5;")),
Destination:=Worksheets("LinkedData3").Range("A1") )
.CommandText = Array( _
"SELECT `Agent Report`.datetime, `Agent Report`.groupNumber,
`Agent
Report`.agentNum, `Agent Report`.agentName, `Agent Report`.inCall, `Agent
Report`.noCallAnswer, `Agent Report`.totalInNormalTime, `Ag" _
, _
"ent Report`.totalSigninTime, `Agent Report`.totalOutNormalTime,
`Agent Report`.totalBusyTime, `Agent Report`.totalWrapupTime" & Chr(13) &
""
& Chr(10) & "FROM `" & ReportFileName & "`.`Agent Report` `Agent Report`"
&
Chr(13) & "" & Chr(10) & "WHERE (`Agent Repor" _
, _
"t`.datetime={ts '" & ReportStartDate & " 00:00:00'} And `Agent
Report`.datetime<{ts '" & ReportBeforeDate & " 00:00:00'})" & Chr(13) & ""
&
Chr(10) & "ORDER BY `Agent Report`.datetime" _
)
.Name = "Query from MS Access Database"
.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