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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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.
 

Balfin

Board Regular
Joined
Dec 29, 2005
Messages
119
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

Balfin

Board Regular
Joined
Dec 29, 2005
Messages
119
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.
 

Balfin

Board Regular
Joined
Dec 29, 2005
Messages
119

ADVERTISEMENT

I just changed Day to Dy, Month to Mnth and Year to Yr, but it didn't help.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Where are you getting the error?
 

Balfin

Board Regular
Joined
Dec 29, 2005
Messages
119
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Well that's the problem.:)

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

Watch MrExcel Video

Forum statistics

Threads
1,109,447
Messages
5,528,803
Members
409,837
Latest member
karnasrinivas
Top