Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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 ?
0
Tocogroup
Asked:
Tocogroup
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
MacroShadowCommented:
strFileName must include the path too.
0
 
NorieData ProcessorCommented:
Do you actually have a workbook with the name 'Master.xlsx' open when you run the code?
0
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
NorieData ProcessorCommented:
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
 
NorieData ProcessorCommented:
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now