Import text. File name too long

[BrendanDixon]

Hi All,

I am looking to have a spreadsheet that imports all the text files in a folder into sheets of the current workbook. I do have some code I downloaded. but when I try and run the code it complains the the file names are longer than 31 characters. the text file names are normally around 63 characters. can anyone tell me how to fix this as I do not understand how this coding works.

If possible, in the filename there is an underscore and I would only like to use the characters before the underscore as each sheet name.

Sub TxtImporter()
Dim f As String, flPath As String
Dim i As Long, j As Long
Dim ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
flPath = ThisWorkbook.Path & Application.PathSeparator
i = ThisWorkbook.Worksheets.Count
j = Application.Workbooks.Count
f = Dir(flPath & "*.txt")
Do Until f = ""
    Workbooks.OpenText flPath & f, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, TrailingMinusNumbers:=True
    Workbooks(j + 1).Worksheets(1).Copy After:=ThisWorkbook.Worksheets(i)
    ThisWorkbook.Worksheets(i + 1).Name = Left(f, Len(f) - 4)
    Workbooks(j + 1).Close SaveChanges:=False
    i = i + 1
    f = Dir
Loop
Application.DisplayAlerts = True
End Sub

 

[CharlesChuckieCharles]

ThisWorkbook.Worksheets(i + 1).Name = Left(f, Len(f) - 4)


Tab or Sheet names cannot be greater than 31 characters and have to be unique in a work book ( can't have two "Sheet1"'s for instance)


If its possible to post a couple of examples of the Names that might help

but instr would give you the positions

If InStr(1, f, "_") > 0 Then
UnderscorePosition = InStr(1, f, "_")

Else
UnderscorePosition = 31
End If
ThisWorkbook.Worksheets(i + 1).Name = Left(f, IIf(UnderscorePosition > 31, 31, UnderscorePosition))