TheDetective
asked on
VBA Create and Rename excel tabs
Hi,
I want to import some text files programmatically and rename them according to their original file name.
I think my code should work, but the tab names are still "sheet 1" and so forth
I used the macro recorder to generate the code to insert tab
I want to import some text files programmatically and rename them according to their original file name.
I think my code should work, but the tab names are still "sheet 1" and so forth
I used the macro recorder to generate the code to insert tab
strFile = Dir(strPath & "\*.dat")
Do While Len(strFile) > 0
strPathFile = strPath & "\" & strFile
Sheets.Add after:=Sheets(Sheets.Count)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strPathFile _
, Destination:=Range("$A$1"))
iNameLen = Len(strFile)
.Name = strFile '<------I try to change the tab name here, but it's not working.
.FieldNames = False
.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 = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
strFile = Dir()
Loop
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER