File Name Variables in Macros

Balfin

Board Regular
Joined
Dec 29, 2005
Messages
119
Hi,

Is there a way to assign a variable to a file name, so that I don't have to type the file name in the code every time I want to switch windows? The filename is quite long and made up of several variables so that it contains the date.

Thanks!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi Balfin

You can set a reference to a workbook such that:

Code:
Dim wb As Workbook
Set wb = Workbooks("WhateverTheNameIsHere")

So that, depending on what you want to do, you can refer to the workbook by its reference:

Code:
wb.Save

for example.

Does this help?
 
Upvote 0
Balfin

Why are you switching windows?

It's easy to create references to workbooks.

This will create a reference to the workbook the code is in.
Code:
Set wbThis = ThisWorkbook
This to the active workbook.
Code:
Set wbAct = ActiveWorkbook
This to a newly opened workbook.
Code:
strFileName = "C:\MyFirstFolder\MySecondFolder\MyWorkbook.xls"
Set wbOpen = Workbooks.Open(strFileName)
All of these can then be used in subsequent code whenever you want to refer to the workbooks.
 
Upvote 0
I'm still having a bit of trouble.

I've got
Code:
Dim Original As Workbook

and then I save the activework book like this
Code:
ActiveWorkbook.SaveAs Filename:="C:\Macro Test\JN " & Month & " " & Day & " " & Year & ".xls"

and that works fine. So I try
Code:
Set Original = ActiveWorkbook

and I get

Run-time error '13':

Type mismatch


Not sure what's wrong.

There are steps inbetween that seem to work, and the file does save in the right place with the right name.
 
Upvote 0
That looks fine to me apart from your use of Month/Year/Day.

It appears you are using them as variables.

That's not a good idea since Month and Year are VBA functions.

Mind you that doesn't explain why you would get an error here.:eek:
Code:
Set Original = ActiveWorkbook
Could you post the rest of your code?
 
Upvote 0
Yeah, Month and Year are being used as variables, I guess I wasn't thinking.

Here is the the code.

Code:
Sub Weekly()
    Dim Day, Month, Year As Integer
    Dim Original As Workbook
    
    
    
    'Find Last Friday
    Workbooks.Open Filename:="V:\Macro Related\Last Friday.xls"
    Day = Evaluate("=DAY('V:\Macro Related\[Last Friday.xls]Last Friday'!A3)")
    Month = Evaluate("=MONTH('V:\Macro Related\[Last Friday.xls]Last Friday'!A3)")
    Year = Evaluate("=YEAR('V:\Macro Related\[Last Friday.xls]Last Friday'!A3)")
    ActiveWindow.Close
    
    ActiveWorkbook.SaveAs Filename:="C:\Macro Test\JN " & Month & " " & Day & " " & Year & ".xls"
    
    Set Original = ActiveWorkbook

    
    
  Set NewBook = Workbooks.Add
    With NewBook
        .SaveAs Filename:="C:\Macro Test\Nipissing " & Month & " " & Day & " " & Year & ".xls"
    End With
    


    Windows(Original).Activate
    
    
End Sub

I took out a few comments and shortened the paths a bit. If you know a better way to find the date of the last Friday that would be cool too. And the last part about NewBook isn't my code, but it seems to do what I want.
 
Upvote 0
Where are you getting the error?
 
Upvote 0
The last line

Code:
Windows(Original).Activate

It was mostly in there to test to see if it worked and I could call a file like that.
 
Upvote 0
Well that's the problem.:)

You've declared Original as a Workbook, Windows expects a string.
Code:
Original.Activate
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top