Help with ActiveWorkbook.Name

[KC Cat]

Hello to all,

I am using ActiveWorkbook.Name in my macro, but it is returning the "dot" and file extension (e.g. ".xls"). I thought the .Name would give me just the filename with no extension.

How can I get just the filename?

Example: this_is_my_filename.xls would be this_is_my_filename

Thanks!

 

[Norie]

What are you using it for?

 

[Michael M]

Maybe post the code.
Activeworkbook.name works for me !!

 

[KC Cat]

Here is the code:


Dim wbVar As String
Dim wbpathVar As String
wbVar = ActiveWorkbook.Name
wbpathVar = ActiveWorkbook.Path

Dim wbnewVar As Workbook
Dim wbnewnameVar As String

Worksheets("Exporter Sheet").Copy
Set wbnewVar = ActiveWorkbook

Application.DisplayAlerts = False
wbnewVar.SaveAs wbpathVar & "\" & wbVar & " - to payroll.xls"
Application.DisplayAlerts = True

This yields a filename "Test - Timecard.xls - to payroll.xls", when I would like it to be "Test - Timecard - to payroll.xls".

Hope this helps!

 

[shg]

    With ActiveWorkbook
        MsgBox Left(.Name, InStrRev(.Name, ".") - 1)
    End With

The workbook has to be saved in order to have an extension.

 

[KC Cat]

shg4421,

Let me explain what's happening a little better.

I have a master workbook with multiple worksheets, let's call it "master.xls", from which I copy one worksheet out to a new file with my macro that also names this new file for me. This worksheet happens to contain the information I want the payroll department to have, but I don't want to send them the full master file, as they don't need any of the rest of this file. Plus, the workbook is quite large, and the new file is very small - makes emailing/storing more efficient.

To be clear, everything is working fine functionally. It's just that the file name is not exactly what I want. So, if using "master.xls" as an example, I would like to end up with "master - to payroll.xls". Instead, I'm getting "master.xls - to payroll.xls".

To your comment about the workbook having to have been saved to have an extension - it (master.xls) has been.

Your thoughts now?

 

[shg]

    Dim sWkb        As String
    Dim sPath       As String
 
    With ActiveWorkbook
        sWkb = Left(.Name, InStrRev(.Name, ".") - 1)
        sPath = .Path & "\"
    End With
 
    Worksheets("Exporter Sheet").Copy
    ActiveWorkbook.SaveAs sPath & sWkb & " - to payroll"

 

[KC Cat]

Now we're talkin'! That worked very nicely - Thanks!

 

[KC Cat]

shg4421,

Well, it worked for use in Excel 2003, but I'm getting a "Compile Error in Hidden Module" error when trying to use in Excel 2007.

I had a similar problem when trying to use the Weekday function. And when I changed my code from:

"If Weekday(Range("WeekEndingDate")) <> 1 Then"

to

"If WorksheetFunction.Weekday(Range("WeekEndingDate")) <> 1 Then"

it resolved the compile error.

So, I thought I would try that here, and change the Left to WorksheetFunction.Left, with no luck.

What are your thoughts on this situation?

Thanks!