导入多个文本文件时的标签页Excel VBA [英] Label sheets while importing multiple text files Excel VBA

问题描述

我一直在尝试将多个大文本文件导入同一工作簿中的多个电子表格。感谢在这个网站上的其他帖子，我已经拼凑了一些似乎做这个工作的VBA代码。唯一的问题是，由于数据在文本文件中未标记，因此很难将它们分开进行分析。因此，我想在导入过程中将每个电子表格与相应的文本文件进行标签。我使用的代码如下。

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.

谢谢！

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

编辑：管理来计算出来。刚刚添加ws.Name = strFile后结束。似乎工作得很好。

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

推荐答案

管理来计算出来。刚刚添加ws.Name = strFile后结束。似乎工作得很好。

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

这篇关于导入多个文本文件时的标签页Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！