Why do I get a VBA run-time error when assigning my Excel workbook file ?

Hi All,

I'm trying to save a workbook in my VBA procedure but it returns a Run-time error 9 (Subscript out of range) when it interprets the Set workbook line.

Why is this ? My workbook exists. Does it have to be open first, before saving ?

Here's my code.

   Dim wbMaster As Workbook
   Dim strFileName As String
   
   strFileName = "Master.xlsx"
   Set wbMaster = Workbooks(strFileName)
   
   Application.DisplayAlerts = False
   Application.EnableEvents = False
   wbMaster.SaveAs Filename:=strFileName
   Application.EnableEvents = True
   Application.DisplayAlerts = True

Open in new window


I get the error on the "Set wbMaster..." line.

Any ideas
Toco ?
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
strFileName must include the path too.
0
NorieVBA ExpertCommented:
Do you actually have a workbook with the name 'Master.xlsx' open when you run the code?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Hmmm....even when I enter the full path in strFileName I still get the same error. I've tried saving (and pointing at) the file elsewhere but the same thing happens  eg.    strFileName = "C:\Users\Toco\Master.xlsx"
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

TocogroupAuthor Commented:
No, the Master workbook isn't open.
0
James ElliottManaging DirectorCommented:
You'll need something like this:

Dim wb as workbook
Dim strFileName as string

strFileName = "Master.xlsx"

set wb = workbooks.open("C:\yourPath\" & strFileName)

Open in new window

0
James ElliottManaging DirectorCommented:
You can then save when you close.

wb.Close True

set wb = nothing

Open in new window

0
NorieVBA ExpertCommented:
You can't refer to, or save, a workbook that isn't open.
0
TocogroupAuthor Commented:
Thank you both. You were spot on with your solutions. Hope you don't mind if I split the points but your replies appeared at the same time.
It is strange because I've used my code before thinking it was the way to 'assign' a workbook object to a variable name. However, on reflection I've most likely used it in conjunction with the On Error Resume statement and never flagged the error.

Toco
0
TocogroupAuthor Commented:
You don't need the full path if the workbook resides in the same folder as the workbook module you are running the macro from.
0
NorieVBA ExpertCommented:
It's always best to specify the path.

If you don't VBA will look for the file in the current directory and that won't always be the same directory as the workbook you are running the code from resides.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.