VBA Create and Rename excel tabs

Posted on 2012-09-19
Medium Priority
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

Shanan212 earned 1600 total points
ID: 38414591
you could say

activesheet.name = strfile

at the end after the 'End with'

Author Closing Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

807 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