How to Import a Sheet from an external Workbook AND use the Filename (WITHOUT the .datatype at the end ) as the New Worksheet name?

Question

How to import a Sheet from an external Workbook AND use the Filename (WITHOUT the .datatype at the end) as the new Worksheet name?

The part with WITHOUT the .datatype at the end I meant because I could split the filename from the file path with UBound, but when I try to do that with the filename and the filetype at the end, it doesn't work and gives me an error. Perhaps i dont understand ubound well enough.

I found this Sub somewhere here on the forum. But I don't want to import any sheet except the sheet which has the same name as the file itself. So I am not even sure if you need to specify the sheet name.

So I have this Excel file with VBA macros. And the Sheet is called Blank (Since I can't have an excel file without a sheet inside it) and I have a Userform button where I browse for the file first, and the sheet there should be imported to my Excel File and delete the Blank sheet and import the new EXTERNAL sheet.

Also, it should import ANY Sheet from the file path. Because the names will always be different.

And also, how do I import the data as csv? I am googling but I don't see what exactly causes it to be imported as csv at other peoples solutions.

Sub ImportSheet()
   Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook, wbBk As Workbook
Dim vfilename As Variant
Dim wsSht As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Comma Separated Value, *.csv", Title:="Open Workbook")
If sImportFile = "False" Then
    MsgBox "No File Selected!"
    Exit Sub

Else
    vfilename = Split(sImportFile, "\")
    sFile = vfilename(UBound(vfilename))



    Application.Workbooks.Open Filename:=sImportFile

    Set wbBk = Workbooks(sFile)

    With wbBk
        If SheetExists("GaebTesten.g42_2") Then
            Set wsSht = .Sheets("GaebTesten.g42_2")
            wsSht.Copy Before:=sThisBk.Sheets("Start")

        Else
            MsgBox "There is no sheet with name :US in:" & vbCr & .Name
        End If
        wbBk.Close SaveChanges:=False
    End With
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


Private Function SheetExists(sWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(sWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

this is my second post here on stack overflow, and my first question was very dumb, and when I asked my first question, it was my 2nd hour with vba. I think I am at about 30 hours now and I've learned a lot.

Question: I am doing this Excel Macro in VBA with userform too now. But mostly I google how to do what and I try to implement it WHILE understanding it, I don't just copy and paste code. Often I just do line by line and test it out. BUT... how do you guys remember all that? If I had to program the same thing again right now, I won't know how to, because I know how a syntax works, but I wouldn't know which syntax and stuff to actually use to achieve the desired effect...

Does it come from repeating the same things = experience? Or how do you acquire the abilities to code without googling almost every single thing? When watching youtubers live streaming how they code something, they never look it up on the internet....

Answer 1

Let me present you a different way than pure string manipulation:

Set a new reference to Microsoft Scripting Runtime. This will enable the Scripting namespace. With it you can do things like the following:

sImportFile = "C:\StackFolder\PrintMyName.xlsx"
With New Scripting.FileSystemObject
    Debug.Print .GetBaseName(sImportFile)
    ' Outputs "PrintMyName"
    Debug.Print .GetExtensionName(sImportFile)
    ' Outputs "xlsx"
    Debug.Print .GetFileName(sImportFile)
    ' Outputs "PrintMyName.xlsx"
    Debug.Print .GetDriveName(sImportFile)
    ' Outputs "C:"
    Debug.Print .GetParentFolderName(sImportFile)
    ' Outputs "C:\StackFolder"
End With

You can build a little helper function to give you the part of the file name you need:

Public Function GetFilenameWithoutExtension(ByVal filename as String) as String
    With New Scripting.FileSystemObject
        GetFilenameWithoutExtension = .GetBaseName(filename)
    End With
End Function

and call it: sFile = GetFilenameWithoutExtension(sImportFile)

---

Regarding the interesting use of UBound in your subroutine, you could even get the filename (without extension) that way - assuming it doesn't contain additional dots:

vfilename = Split(sImportFile, "\")
sFile = vfilename(UBound(vfilename))
SplitName = Split(sFile, ".")
FilenameWithoutExtension = SplitName(UBound(SplitName)-1)
Extension = SplitName(UBound(SplitName))

These are, however, purely academical thoughts and I wouldn't recommend doing it this way.

Answer 2

Here are two ways to extract the workbook name without the file extension. Here I am removing the extension .xlsx. If the extension is constant, you can just hard code it. If not, you can use wildcards also

---

MsgBox Left(wbBk.Name, Len(ThisWorkbook.Name) - 5)
MsgBox Replace(wbBk.Name, ".xlsx", "")

You can refer to the sheet with the same name as the workbook by using something like

Sheets(Left(wbBk.Name, Len(ThisWorkbook.Name) - 5).Copy
Sheets(Replace(wbBk.Name, ".xlsx", "").Copy

Answer 3

You can use InstrRev. It is efficient as starts from the end of the string which is where the extension is located.

Left$(wbBk.Name, InStrRev((wbBk.Name, ".") - 1)