Imported Text File Worksheet Tab Name

[bob]

I created this macro using snippets of code I found in this news group.
The macro: (1) clears the worksheet (2) prompts the user to select
a textfile.txt to open (3) imports the textfile into sheet 1 and (4)
does a bit of formatting to the worksheet.

How can I make the tab reflect the name of the textfile that was opened
excluding the .txt ext?

Thanks

Here's the code below:

Cells.Select
Selection.ClearContents
Range("A1").Select

MyDataFile = Application.GetOpenFilename("Text Files,*.Txt")

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyDataFile, _
Destination:=Range("A1"))
.Name = "MyDataFile & Activesheet.QueryTable.counts +1"
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With
End Sub

 

[Frank Kabel]

Hi
try
..name=left(MyDataFile ,len(MyDataFile)-4))

 

[bob]

That only gives me MyFileName on the tab.
I need it to return the name of the selected file.
For example, if the selected file is A.txt, I need the
tab to reflect A
Thanks

 

[Frank Kabel]

Hi
but if I read your code correctly you use MyDataFile to store the
selected name. Just put my statement at the end (last item within the
with statement)