title: Excel macro to import text file and overwrite worksheet without breaking references
body: <p>I have the following macro that I routinely use to import text files into separate excel worksheets:</p>
  
  <pre><code>Sub ImportManyTXTs()
  Dim strFile As String
  Dim ws As Worksheet
  strFile = Dir("C:\location\of\folder\with\textfiles\*.txt")
  Do While strFile &lt;&gt; vbNullString
  
  strFile2 = Replace(strFile, ".txt", "")
  
  Set ws = Sheets.Add
  With ws.QueryTables.Add(Connection:= _
      "TEXT;" &amp; "C:\location\of\folder\with\textfiles\" &amp; strFile, Destination:=Range("$A$1"))
      .Name = strFile
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .TextFilePromptOnRefresh = False
      .TextFilePlatform = 437
      .TextFileStartRow = 1
      .TextFileParseType = xldelimited
      .TextFileTextQualifier = xlTextQualifierDoubleQuote
      .TextFileConsecutiveDelimiter = True
      .TextFileTabDelimiter = False
      .TextFileSemicolonDelimiter = False
      .TextFileCommaDelimiter = False
      .TextFileSpaceDelimiter = True
      .TextFileColumnDataTypes = Array(1, 1, 1)
      .TextFileFixedColumnWidths = Array(7, 9)
      .TextFileTrailingMinusNumbers = True
      .Refresh BackgroundQuery:=False
  End With
  ws.Name = strFile2
  strFile = Dir
  Loop
  End Sub
  </code></pre>
  
  <p>...but I would like to overwrite an existing worksheet if the same name is already used. In other worksheets I have references to cells in the worksheets that would be 'overwritten' so I need a way to do this without breaking the references to those cells. Anyone know of a good solution for this?</p>