Create sheetnames based on imported filenames without extension

[Berenloper]

Hi everyone,

As part of a bigger macro I want to create sheetnames based on imported filenames.
I use this code:

Dim fs  As New FileSystemObject
Dim fo As Folder
Dim fi As File
Dim WB As Workbook
Dim ws As Worksheet
Dim sname As String
Sub loadall()
    Set WB = ThisWorkbook
    Set fo = fs.GetFolder("C:\Users\berenloper\Downloads\CSV\")
    For Each fi In fo.Files
        If UCase(Right(fi.Name, 4)) = ".CSV" Then
            sname = Replace(Replace(fi.Name, ":", "_"), "\", "-")
            Set ws = WB.Sheets.Add
            ws.Name = sname
            Call yourRecordedLoaderModified(fi.Path, ws)
        End If
    Next
End Sub

... etc

The code itself works fine but the sheetnames also contain the filename extensions. I tried to eliminate them in the line 'ws.Name = sname' but no luck. Whatever I made, everytime some error occurs.

Any help on this would be appreciated!

Regards,

Berenloper

 

[mrmmickle1]

Try this...it splits the file name at the period....

sname = Split(Fi.Name,".")(0)
ws.Name = sname

 

[Berenloper]

Hello Matt,

Yes! That's perfect. Thanks a lot!!

Regards,

Berenloper