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

 

[Fluff]

How about

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

 

[Sinem]

Hi Fluff thank you but unfortunately not. It has problems with the date format. If it's 01-01-2019 then it can handle it perfectly. But it doesnt like the dots anyhow.

 

[Rick Rothstein]

Hi Fluff thank you but unfortunately not. It has problems with the date format.

Can you tell us exactly what the problems are?

 

[Macropod]

Cross-posted at: http://www.vbaexpress.com/forum/sho...t-FIlename-(including-dots)-without-extension
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Sinem]

Hi Rick,

here is my a part of my code:

Public Sub fkt_Copy()

    Dim str_Path_Dest As String
    Dim str_FileName As String
    Dim str_DestPathName As String
    Dim fnPos As Integer
    Dim foFS As FileSystemObject
    
    
    Dim xApp As Excel.Application
    Dim xWbk As Workbook
    Dim ws As Worksheet
   
   
 
    
    
On Error GoTo 0
    
    str_Path_Dest = "C:\TESTFOLDER"
    str_FileName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)  
    
    str_DestPathName = str_Path_Dest & "\" & str_FileName & ".xlsm"
    
    
   
        Set foFS = New Scripting.FileSystemObject
        Call foFS.CopyFile(ThisWorkbook.FullName, str_DestPathName, True)
       
        Set xApp = New Excel.Application
        xApp.DisplayAlerts = False
        xApp.EnableEvents = False
       
        Set xWbk = xApp.Workbooks.Open(str_DestPathName, ReadOnly:=True)
         
  
        xWbk.SaveAs str_Path_Dest & "\" & str_FileName, xlOpenXMLWorkbook
        xWbk.Close

Sine the Filename contains the date with dots 01.01.2019 it saves my workbook as pdf but I cannot not open it. And the name becomes "Test 01.01" instead of "Test 01.01.2019"
But changing the filename to "Test 01-01-2019" works perfectly.

 

[Fluff]

Oops, it should be

fileName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1)

 

[Sinem]

Hi Fluff tha does not work either. Please look at my post above. It has dot problems in the date.

 

[Sinem]

Hi Macropod the filename is extracted correctly, also in my code, but during SaveAs it has problems with the date. The workbook is saved as "Test 01.01" and has a weird format (looking as pdf but type is excel)

 

[Macropod]

Well, since you haven't posted any code indicating how the PDF is generated, we can't comment on that.