Link to home
Start Free TrialLog in
Avatar of TheDetective
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


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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Shanan212
Shanan212
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TheDetective
TheDetective

ASKER

Thanks, that worked. I can't believe the answer is so simple. Cheers