Get Filename without the extension

[Larry S]

I have a macro that saves a workbook in .xlsm format and then as a .pdf in
the same folder. My problem is the file name for the .pdf file. If the excel
file is named abc.xlsm, then the pdf file is saved as abc.xlsm.pdf. I need to
get just "abc" for the filename for the pdf. Below is my code. I know that
specifying the Filename as ActiveWorkbook.Name is my problem, i just don't
know how to fix it.


Sub SaveAsPDFandXLS(control As IRibbonControl)

ans = Application.GetSaveAsFilename _
("S:\Shared\CORRESPONDENCE\" & ActiveWorkbook.Name, _
fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
ActiveWorkbook.SaveAs ans
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveWorkbook.Name, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub


Thanks,
Larry

 

[Ron de Bruin]

Try this Larry


Dim fname As Variant
fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files (*.xls), *.xls")

MsgBox Left(fname, InStrRev(fname, ".") - 1)

 

[egun]

Try:

Filename:=Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xl") -
1) & ".pdf"

HTH,

Eric

 

[Joel]

FName = ActiveWorkbook.Name
BName = Left(FName, Len(FName) - InStrRev(FName, "."))

 

[Larry S]

Thanks egun.
Worked perfectly

Try:

Filename:=Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xl") -
1) & ".pdf"

HTH,

Eric

 

[Rick Rothstein]

This simpler expression would have worked as well...

Filename:=Replace(ActiveWorkbook.Name, ".xlsm", ".pdf")

 

[Larry S]

Speaking of file lengths, the other thing i wanted to do with this macro was
reference the last three characters of a cell value. How do i accomplish that?

 

[Tim Williams]

Msgbox Right(Sheet1.Range("A1").Value, 3)

Tim

Speaking of file lengths, the other thing i wanted to do with this macro
was
reference the last three characters of a cell value. How do i accomplish
that?

 

[Gary Keramidas]

here's another way:

fname = Split(ActiveWorkbook.Name, ".")(0)

 

[Rick Rothstein]

That will work **only** if you know for sure that the path or filename does
not contain any dots in them except for the one in front of the extension.