VBA Create and Rename excel tabs

Posted on 2012-09-19
Last Modified: 2012-09-19

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()

Open in new window

Question by:TheDetective
    LVL 13

    Accepted Solution

    you could say = strfile

    at the end after the 'End with'

    Author Closing Comment

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

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now