Imported Text File Worksheet Tab Name
 

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

Imported Text File Worksheet Tab Name
 

Dim shArr As Variant
Dim shName As String
myDatafile = "C:\Myfolder\MyfileName"
shArr = Split(myDatafile, "\")
shName = shArr(UBound(shArr))
If InStr(shName, ".") Then _
shName = Left(shName, InStr(shName, ".") - 1)
Activesheet.Name = shName

--
Regards,
Tom Ogilvy


"bob" wrote in message
...
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

Imported Text File Worksheet Tab Name
 

This gives me "MyFileName" on the tab,
what I need is for the name of the selected
file to appear on the tab. Also, the path is
not a constant; it is selected by the user.
Thanks
"Tom Ogilvy" wrote in message
...
Dim shArr As Variant
Dim shName As String
myDatafile = "C:\Myfolder\MyfileName"
shArr = Split(myDatafile, "\")
shName = shArr(UBound(shArr))
If InStr(shName, ".") Then _
shName = Left(shName, InStr(shName, ".") - 1)
Activesheet.Name = shName

--
Regards,
Tom Ogilvy


"bob" wrote in message
...
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

Imported Text File Worksheet Tab Name
 

your code gets the filename and path with

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

The code I wrote and posted extracts the filename from the string in
MyDataFile

and names the sheet with that name. To test the code, I assigned a value to
myDatafile. I figured it was obvious that line should be removed, but
perhaps not.

Dim shArr As Variant
Dim shName As String
'myDatafile = "C:\Myfolder\MyfileName"
MyDataFile = Application.GetOpenFilename("Text Files,*.Txt")
shArr = Split(myDatafile, "\")
shName = shArr(UBound(shArr))
If InStr(shName, ".") Then _
shName = Left(shName, InStr(shName, ".") - 1)
Activesheet.Name = shName

or to put it all together.

Dim shArr As Variant
Dim shName As String

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
shArr = Split(myDatafile, "\")
shName = shArr(UBound(shArr))
If InStr(shName, ".") Then _
shName = Left(shName, InStr(shName, ".") - 1)
Activesheet.Name = shName
End Sub

--
Regards,
Tom Ogilvy




"bob" wrote in message
...
This gives me "MyFileName" on the tab,
what I need is for the name of the selected
file to appear on the tab. Also, the path is
not a constant; it is selected by the user.
Thanks
"Tom Ogilvy" wrote in message
...
Dim shArr As Variant
Dim shName As String
myDatafile = "C:\Myfolder\MyfileName"
shArr = Split(myDatafile, "\")
shName = shArr(UBound(shArr))
If InStr(shName, ".") Then _
shName = Left(shName, InStr(shName, ".") - 1)
Activesheet.Name = shName

--
Regards,
Tom Ogilvy


"bob" wrote in message
...
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