Overite Cells VBA Query Code

Hi,

I currently have the following VBA code that imports data from a Dbf file into an Excel spreadsheet but even though it clears the sheet before i run it if i run if twice it "inserts" cells shift the remaining cells to the right

Any ideas of how to set the code to overate existing cells would be great

Sub DataQuery()
'

'
Application.ScreenUpdating = False
    
    Dim Mailbox As String
    Dim create As Boolean
    Dim sConn As String
    Dim sSql As String
    Dim oQt As QueryTable
    create = True
    
     Dim qt As QueryTable
     Dim WSh As Worksheet

     For Each WSh In ThisWorkbook.Worksheets
          For Each qt In WSh.QueryTables
               qt.Delete
          Next qt
     Next WSh
    
    Mailbox = ActiveWorkbook.Path
    Sheets("Info").Visible = True
    ActiveWorkbook.Sheets("Info").Select
    ActiveWorkbook.Sheets("Info").Cells.ClearContents
      
    
    If create Then
  
    
 ' Allow Data
 
   sConn = "ODBC;CollatingSequence=ASCII;DBQ=C:\TEMP;DefaultDir=C:\TEMP;Deleted=1;Driver={Microsoft dBase Driver (*.dbf)};DriverId=533;FIL=dBase;"
   sSql = "SELECT DESC, RETAIL FROM """ & Mailbox & """\ALLOW.DBF ALLOW WHERE(ALLOW.DESC='PIPECLEANING')"
   Set oQt = ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("k1"), Sql:=sSql)
    oQt.AdjustColumnWidth = False
    oQt.PreserveFormatting = True
    oQt.Refresh
    
    
  
        Application.ScreenUpdating = True
        
    End If 'Remove if Stacking Query And add to end.
    
End Sub

Thanks in advance

Originally Posted by Firefly2012

Hi

You can programmatically set the RefreshStype property - eg before your oQT.Refresh line include the line:

oQT.RefreshStyle = xlOverwriteCells

Thanks for this but when running the code i get an Run-Time error 1004, Cannot complete operation: A table cannot overlap with a pivot table report, query results, a table, merged cells, or an XML mapping

Any ideas??

Thanks

Thanks for all of these but I'm still getting cells inserted rather than overwritten when running the code more than once!!

I've tried differnt combinations of the code but still no joy.

Thanks anyway

Hi Firefly,

I've tried the above and still no joy. When i insert the code into the Immediate window it displays 1

Any ideas??

Thanks