remove file extension from sheet name import

[jsalazar82]

Hello, I am trying to modify this VBA to exclude the ".txt" from the sheetname after import - but not sure how to that, is it easier to just add a line to remove the last 4 characters from the sheet name after import is done? thanks

Dim xWb As WorkbookDim xToBook As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim i As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder of TXT files"
If xFileDialog.Show = -1 Then
    xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
    MsgBox "No files found", vbInformation, "TXT to tabs"
    Exit Sub
End If
Do While xFile <> ""
    xFiles.Add xFile, xFile
    xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
    For i = 1 To xFiles.Count
        Set xWb = Workbooks.Open(xStrPath & xFiles.Item(i))
        xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = xWb.Name
        On Error GoTo 0
        xWb.Close False
    Next End If

 

[jsalazar82]

how to remove the last 4 characters from a sheet name

is there a way to do this, i cannot find anything on the forum of via google. any help appreciated
thanks!

 

[jsalazar82]

Re: how to remove the last 4 characters from a sheet name

excel vba preferred. thanks!

 

[Joe4]

Re: how to remove the last 4 characters from a sheet name

Isn't this the same question you asked here: https://www.mrexcel.com/forum/excel-questions/1022284-remove-file-extension-sheet-name-import.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

 

[Fluff]

Re: how to remove the last 4 characters from a sheet name

How about

Sub ShtName()
'jsalazar82
    With ActiveSheet
        .Name = Left(.Name, Len(.Name) - 4)
    End With

End Sub

 

[Joe4]

Re: how to remove the last 4 characters from a sheet name

Since you got a reply to this thread, I am merging the two together, so we do not have two active threads on the same issue.

 

[Fluff]

Re: how to remove the last 4 characters from a sheet name

Seeing as you've got code on this thread, which you didn't on the other one.
Ignore post#5 & try this

ActiveSheet.Name = Left(xWb.Name,Len(xWb.Name)-4)

 

[Fluff]

Re: how to remove the last 4 characters from a sheet name

Since you got a reply to this thread, I am merging the two together, so we do not have two active threads on the same issue.

Cheers for that Joe4

 

[jsalazar82]

thank you!

 

[Fluff]

Glad to help & thanks for the feedback