quick naming question

[Mr_Ragweed2]

Sorry for a dumb question here but how do i take the .xlsx out of a filename/string? The filename/string is dynamic in length.
I have some .xlsx files i am saving as .PDF files and for some reason (prolly because i coded it that way) it is saving them as a "file name.xlsx.pdf" . The "file name.xlsx" is a String. So i just want to edit that string and lop off the .xlsx. So basically lop off the last 5 characters of a string. I'm no good at the LEN/UBound/LBound/LEFT/MID/Right thing if it has something to do with that. haha
This is as far as i have gotten on that bit.

fileName = File.Name
        
    newName =

To add background i used filename as a String in a pathway to save to another location. I now want to switch the string in that save as pathway to newName. Then i kill the original filename.

If fileName Like "*SWO*.xlsx" Then
    
        ActiveWorkbook.SaveAs fileName:= _
            "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Service%20Work%20Orders/" _
            & newName & ".PDF"   'newName used to be filename but that's when the issues arose
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF
        Kill "C:\Users\" & MyName & "\OneDrive - MyCompany\Desktop\Propane Forms\" & fileName

I can put the whole code here if needed. It's not super long and is a loop.

Thanks in advance.

 

[Mr_Ragweed2]

I'm an idiot. Would be as simple as this?

newName = Left(fileName, Len(fileName) - 5)

 

[Mr_Ragweed2]

why yes, yes it was that simple.

 

[MARK858]

Just a bit more generic (caters for the likes of .xls as well as .xlsx)

Left(Filename, InStrRev(Filename, ".", -1, vbTextCompare) - 1)

 

[Mr_Ragweed2]

So if i understand it correctly, what you did was tell it to lop off everything from right to left until it found a "." and then lop off the "." ?

 

[MARK858]

The InStrRev is finding the position of the first period working from the right, the last -1 is making the position 1 character less (the first -1 just tells it to start it's search at the first character [it could be omitted as it is the default]).

Then it is just Left(FileName, found position)

Edit: if you want to shorten it then you can use

Left(Filename, InStrRev(Filename, ".", , 1) - 1)

 

[Mr_Ragweed2]

That's cool. Thank you for taking the time to teach and explain it to me!

 

[MARK858]

You're welcome