Thisworkbook.Name

[Zzzzzz]

I have two modules both using the same if statement which doesn't seem to be working...what am I doing wrong with this statement

If Right(ThisWorkbook.Name, 3) <> "XLS" Then

Private Sub Workbook_Open()

    If Right(ThisWorkbook.Name, 3) <> "XLS" Then
        Sheets("Expenses").Activate
        With Range("B6")
            If .Value = "" Then
                .NumberFormat = "@"
                .Value = UCase(Format(Date, "dd-MMM-yy"))
                Range("B4") = Environ("Username")
                Range("I4").Select
            End If
        End With
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If Right(ThisWorkbook.Name, 3) <> "XLS" Then
        ThePath = "C:\Documents and Settings\pitfield\My Documents\Reports\Expense\"
        TheDate = Format(Sheets("Expenses").Range("I4"), "yyyy-mmmm-dd")
        Fname = "Expense Report " & TheDate
    ActiveSheet.Copy
   
        ActiveWorkbook.SaveAs Filename:=ThePath & Fname & ".xls"
    End If
End Sub

 

[Zack Barresse]

What are the expected results? And why would this line ..

If Right(ThisWorkbook.Name, 3) <> "XLS" Then

.. evaluate to TRUE?? The only thing that comes to my head is if the workbook isn't saved. This is Excel code, or are you using csv files? More explanation please.

 

[Zzzzzz]

Hey firefytr, thanks for the reply!

This code is attempting to help auto date and auto name a file created from a template.

The first module is intended to auto date the template only if the file does not have an .XLT extension, or if there is no date present in “I4”. Therefore, if I am working on the template as a template, this code will not run when I make and save changes to the template, but as soon as I create a file from the template, it runs.

The second module is attempting to do the same thing, only run if it is not a template .XLT. In this case though it is attempting to automate the naming process of the file created from the template, It also shouldn't run if the file is already named as per the code, so not to name it twice or every time you save a copy of the file!

 

[Zack Barresse]

You can check if a file exists like so ...

<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> FileExists(sFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
        <SPAN style="color:#00007F">Dim</SPAN> oFSO
        <SPAN style="color:#00007F">Set</SPAN> oFSO = CreateObject("Scripting.FileSystemObject")
        <SPAN style="color:#00007F">If</SPAN> oFSO.FileExists(sFileName) <SPAN style="color:#00007F">Then</SPAN> FileExists = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> oFSO = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

.. and you can test it like so ..

<font face=Tahoma New><SPAN style="color:#00007F">Sub</SPAN> testFE()
    MsgBox "File exists? " & vbNewLine & _
        FileExists("C:\Documents and Settings\Rob\Desktop\Book1.xls")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


I'm a little unclear as to what you want, but to save only if the file is not there (as per specified format), maybe ..


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">If</SPAN> Right(ThisWorkbook.Name, 3) <> "XLS" <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Sheets("Expenses")
            <SPAN style="color:#00007F">If</SPAN> .Range("B6").Value = "" <SPAN style="color:#00007F">Then</SPAN>
                .Range("B6").NumberFormat = "@"
                .Range("B6").Value = UCase(Format(Date, "dd-MMM-yy"))
                .Range("B4") = Environ("Username")
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeSave(<SPAN style="color:#00007F">ByVal</SPAN> SaveAsUI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">Dim</SPAN> ThePath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, TheDate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Fname As <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Right(ThisWorkbook.Name, 3) <> "XLS" <SPAN style="color:#00007F">Then</SPAN>
        ThePath = "C:\Documents and Settings\Rob\Desktop\"
        TheDate = Format(Sheets("Expenses").Range("I4"), "yyyy-mmmm-dd")
        Fname = "Expense Report " & TheDate
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> FileExists(ThePath & Fname & ".xls") <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#007F00">'doesn't exist</SPAN>
            Sheets("Expenses").Copy
            ActiveWorkbook.SaveAs Filename:=ThePath & Fname & ".xls"
        <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#007F00">'already exists</SPAN>
            <SPAN style="color:#007F00">'..</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Zzzzzz]

Hi firefytr,

Thanks for the help, having a basic understanding of VBA does this code know the name of the workbook it is checking or do I have to tell it the name?

Also if you need a better explanation as to what I am attempting to do just let me know?

 

[Zack Barresse]

A better explanation would help. (It may just be that I'm really thick too. )

It only checks the file extension, that is all.

 

[Zzzzzz]

Let’s look at each module separately to avoid confusion.

Both modules are attempting to automate a process, the first module is supposed to auto date and input the user name into the worksheet when it is created from a template on the Woorkbook_Open event.

The auto date and name works well, except it always runs whether I open up the template for editing or when I create a workbook from the template. I only want this code to run when a workbook is created new from the template (file, new from template…etc), which is why I was going down the check the file extension road.

I am not married to this method so please let me know if there is a more logical or better way to go about this?

 

[Zack Barresse]

No, I would use a similar (if not the same) type of approach. What exactly is not working for you though?