Extract Filename (including dots) without extension

[Sinem]

Hello,

I canot extract the filename without ".xlsm" because of the dots in the name.

The filename is: "Test 01.01.2019"

Here are my tries:

  FileName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) 

 FileName= Len(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1)

The Workbook is saved as pdf data with the name "Test 01.01" and if I delete the date then I can save it normally as xlsx

 

[Sinem]

Please look at the first page. There is my code.

 

[Macropod]

And none of that code has anything to do with PDF creation.

 

[Sinem]

I don't want to create a pdf. I want to save my file as xlsx but it is saved as a weird format named "Test 01.01" with type "Excel" but has a pdf symbol. My expected filename is "Test 01.01.2019" as Excel file without macros. So my assumption is that SaveAs has problems with the date format. Since changing the filename to "Test 01-01-2019" works.

 

[Sajukai]

Since you grabbed the filename by stripping off the extension, did you remember to add the extension ".xlsx" back when saving?

Sounds like you may not have as what you are describing would have saved as "Test 01.01".2019 meaning a file named "Test 01.01" with an extension of "2019". Which would most likely result in Windows giving it a weird file association like you are describing.

 

[Macropod]

Try:
fileName = Replace(Split(ThisWorkbook.Name, ".xls")(0), ".", "-")

 

[Sinem]

Hi the error occurs during SaveAs action. The name is extracted correctly but the date format "DD.MM.YYYY" prevents SaveAs somehow. If I change the date format to "YY-MM-YYYY" then everything runs as expected.

Does somebody know why SaveAs cannot handle filenames including dots?

 

[Macropod]

Having periods in filenames is poor practice - and is one that has often been exploited by writers of trojan horse software and the like. The code I posted replaces the extraneous periods, thus obviating the problem.

 

[Sinem]

Hi Macropod, I used your code thank you. There is now way to make it with dots I suggest. I shall leave it at that. Thank you