ExcelBanter - Get Filename without the extension

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Get Filename without the extension (https://www.excelbanter.com/excel-programming/418897-get-filename-without-extension.html)

Larry S

Get Filename without the extension

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

Get Filename without the extension

Try this Larry

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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Larry S" wrote in message ...
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

egun

Get Filename without the extension

Try:

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

HTH,

Eric

"Larry S" wrote:

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

joel

Get Filename without the extension

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

"Larry S" wrote:

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

Larry S

Get Filename without the extension

Thanks egun.
Worked perfectly

"egun" wrote:

Try:

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

HTH,

Eric

"Larry S" wrote:

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

Rick Rothstein

Get Filename without the extension

This simpler expression would have worked as well...

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

--
Rick (MVP - Excel)

"egun" wrote in message
...
Try:

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

HTH,

Eric

"Larry S" wrote:

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

Larry S

Get Filename without the extension

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?

"Joel" wrote:

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

"Larry S" wrote:

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

Tim Williams

Get Filename without the extension

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

Tim

"Larry S" wrote in message
...
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?

"Joel" wrote:

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

"Larry S" wrote:

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

Gary Keramidas

Get Filename without the extension

here's another way:

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

--

Gary

"Larry S" wrote in message
...
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

Rick Rothstein

Get Filename without the extension

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.

--
Rick (MVP - Excel)

"Gary Keramidas" <GKeramidasAtMsn.com wrote in message
...
here's another way:

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

--

Gary

"Larry S" wrote in message
...
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

All times are GMT +1. The time now is 01:07 PM.