0

I have the following macro that I routinely use to import text files into separate excel worksheets:

Sub ImportManyTXTs()
Dim strFile As String
Dim ws As Worksheet
strFile = Dir("C:\location\of\folder\with\textfiles\*.txt")
Do While strFile <> vbNullString

strFile2 = Replace(strFile, ".txt", "")

Set ws = Sheets.Add
With ws.QueryTables.Add(Connection:= _
    "TEXT;" & "C:\location\of\folder\with\textfiles\" & 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

...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?

HotDogCannon
  • 2,113
  • 11
  • 34
  • 53
  • If you overwrite those worksheets, what sense lies in keeping those references? Are you just appending content? – EngJon Jan 14 '15 at 11:00
  • the data from the text file are 'updated' values. This would be essentially 'refreshing' the worksheet, using data from text files. – HotDogCannon Jan 14 '15 at 11:02
  • maybe you should try to just append new informations. That way you can keep the old worksheet, the references to it and still update it. I gotta admit I'm not sure what your code does - never worked with querytables - but if you can just extract new informations, I'm sure there's a way to easily append it. – EngJon Jan 14 '15 at 11:07
  • that's why i thought i'd ask the clever folks of SA if they knew of a good solution ;) – HotDogCannon Jan 14 '15 at 11:11
  • See similar question [Excel Interop - How to change named range](http://stackoverflow.com/questions/9791935/excel-interop-how-to-change-named-range). Using named ranges for your references makes the data updating much easier – xmojmr Jan 14 '15 at 13:08

1 Answers1

1

Assuming you don't have any other information stored on those sheets besides the querytable, try this (I cut out your with statement for space):

Sub ImportManyTXTs()
Dim strFile As String
Dim Sht As Worksheet
Dim ws As Worksheet
strFile = Dir("C:\location\of\folder\with\textfiles\*.txt")
Do While strFile <> vbNullString

strFile2 = Replace(strFile, ".txt", "")

For Each Sht in Worksheets
    If Sht.Name = strFile2 Then
        Sht.Cells.ClearContents
        Set ws = Sht
    End If
Next Sht
If ws Is Nothing Then
    Set ws = Sheets.Add
    ws.Name = strFile2
End If
ws.Activate

With ActiveSheet.QueryTables.Add(Connection:= _
    'YourStuffHere
End With

strFile = Dir
Loop
End Sub

In this case the contents of the sheet will just be replaced if it already exists, the references to the cells shouldn't change.

D_Zab
  • 710
  • 1
  • 5
  • 14
  • I get 'Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic' pointing to the line 'ws.Name = strFile2' – HotDogCannon Jan 14 '15 at 13:26
  • Sorry about that, try now. – D_Zab Jan 14 '15 at 13:36
  • no worries. Now i get 'the destination range is not on the same worksheet the Query table is being created on'...maybe i have to prescribe the current worksheet?? – HotDogCannon Jan 14 '15 at 13:46
  • I added the ws.Activate lines and edited the With reference. All the other examples of querytables referenced them this way so it may be required in this case. This is a little bit of trial and error but I cant set up a test sheet for a querytable to check for sure. – D_Zab Jan 14 '15 at 17:30