Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
iTunes Library text file imported into Excel | Excel Discussion (Misc queries) | |||
formatting a text file imported into excel | Excel Worksheet Functions | |||
can I display the file name of the imported text file in another c | Excel Discussion (Misc queries) | |||
if then elseif on text file imported into excel | Excel Programming | |||
Can worksheet data be exported/imported to/from flat file? | Excel Programming |