Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Get Filename without the extension
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,588
Default 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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default 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


Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to insert filename in header without file extension Andrew King Excel Discussion (Misc queries) 4 January 12th 10 09:48 PM
SaveAs Filename:=filename, FileFormat:=xlCSV Teddy[_3_] Excel Programming 2 May 29th 07 02:34 PM
Converting a Variable Filename to a Constant Filename Magnivy Excel Programming 2 August 15th 06 06:13 PM
Getting only the filename with no extension Barry Clark Excel Worksheet Functions 2 June 26th 06 07:51 PM
Transformation d'images avec une extension .jpg ou .jpeg en images avec extension .bmp ou .ico imej-clavier Excel Discussion (Misc queries) 1 May 28th 05 05:52 PM


All times are GMT +1. The time now is 05:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement