VBA Excel - Filename

[vaneagle]

Hi

I have some vba in a file called template.xls. During the process I save the xls file under a new name. I run the macro and of course it errors as the filename has changed. Instead of me finding and replacing the old name with the new filename, is there some code or command I can use in order that, as the macro runs it can identify with the name of the workbook that the code sits in ??

 

[DrJavaJoe]

ActiveWorkbook.FullName

 

[vaneagle]

Thanks DrJavaJoe... that solved that issue.

in addition too...

I have the code below

 Windows("Upload template.xls").Activate

if the "Upload template.xls" file were to change names, yet remain open in excel, how could i update my code to reflect the new name ?? (the name will never be constant)...

Thanks in advance....

 

[krinid]

Keep a link to your WB via a Workbook type variable and you should have no problems. Here's a sample, where the fullname is printed before and after the SaveAs operation, displaying the appropriate name:

Dim WB as WorkBook
Set WB = Workbooks("Upload template.xls")
Debug.Print WB.Fullname
WB.SaveAs filename:="Different Filename.xls"
Debug.Print WB.Fullname

' access workbook via WB variable, ie: WB.Activate, etc

 

[vaneagle]

Hi krinid,

with the code

 Set WB = Workbooks("Upload template.xls")

it comes up with the error "subscript out of range"... any ideas why.... I am new to vba.. here is an extract of the code...

    Dim WB As Workbook
    Workbooks.Open FileName:="C:\temp\Blank.xls"
    Set WB = Workbooks("Upload template.xls")
    Debug.Print WB.FullName
    WB.Activate
    
    'Windows("IED Conversion and Upload template.xls").Activate
    Sheets("Weekly Sales Upload").Select
    Range("A1:BC1030").Select

thanks in advance for your help....

 

[DrJavaJoe]

Try this

Dim WB As Workbook
Set WB = Workbooks(Dir(ActiveWorkbook.FullName))
Debug.Print WB.FullName
WB.SaveAs Filename:="Different Filename.xls"
Debug.Print WB.FullName

 

[vaneagle]

Excellent thanks for that!!

 

[DrJavaJoe]

Glad to help.