excel - 工作表名称等于输入文本文件的名称

Question

更新。

我有以下代码:

Sub ImportTextFile()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Val(Application.Version) > 15 Then
If ActiveWorkbook.AutoSaveOn Then ActiveWorkbook.AutoSaveOn = False
End If
Dim Ret
Dim newWorksheet As Worksheet
Set newWorksheet = Sheets.Add(After:=Sheets("Konvertering"))
Ret = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If Ret <> False Then
With newWorksheet.QueryTables.Add(Connection:= _
    "TEXT;" & Ret, Destination:=newWorksheet.Range("$A$1"))
    .Name = "Sample"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 1252
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    newWorksheet.Name = Mid$(Ret, InStrRev(Ret, "\") + 1)
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

它将文本文件导入 Excel 中的新工作表。如何更新代码以使新工作表与导入的文本文件具有相同的名称？

Answer 1

首先，在添加新工作表时，将其分配给对象变量以供以后引用...

Dim newWorksheet As Worksheet
Set newWorksheet = Sheets.Add(After:=Sheets("Konvertering"))

然后，您可以引用新工作表进行查询，并将新工作表命名如下...

If Ret <> False Then
    With newWorksheet.QueryTables.Add(Connection:= _
        "TEXT;" & Ret, Destination:=newWorksheet.Range("$A$1"))
        'etc
        '
        '
    End With
    newWorksheet.Name = Mid$(Ret, InStrRev(Ret, "\") + 1)
End If