Label sheets while importing multiple text files E

2019-08-04 09:32发布

问题:

I've been attempting to import multiple large text files onto multiple spreadsheets within the same workbook. Thanks to other posts on this site, I have cobbled together some VBA code that seems to do the job. Only problem is that since the data are unlabelled within the text files, it is difficult to tell them apart for analysis. As such, I would like to label each spreadsheet with the corresponding text file during the import process. The code I'm using is as follows.

Thanks!

Sub ImportManyTXTs()
Dim strFile As String
Dim ws As Worksheet
strFile = Dir("I:\test\*.txt")
Do While strFile <> vbNullString
Set ws = Sheets.Add
With ws.QueryTables.Add(Connection:= _
    "TEXT;" & "I:\test\" & 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 = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileFixedColumnWidths = Array(7, 9)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
strFile = Dir
Loop
End Sub

Edit: Managed to figure it out. Just added ws.Name = strFile after End With. Seems to work quite well.

回答1:

Managed to figure it out. Just added ws.Name = strFile after End With. Seems to work quite well.