Run-time error '9': Subcript out of Range

30percent

Board Regular
Joined
May 5, 2011
Messages
102
Hi,

I have the following vba macro:

Code:
Sub save_Report()Set fso = CreateObject("Scripting.FileSystemObject")
basePath = "C:\Users\Orders"
pathName = basePath & "\" & thisYear & "\" & month_Name & "\" & get_FolderDate(minus_day)
fileName_Order_Stats = get_Order_Stats_filename(minus_day)
Workbooks("Order_Template").SaveAs pathName_Order_Stats & "\" & fileName_Order_Stats & ".xlsm", FileFormat:=52
End Sub

I have a VBS script that call the above macro:

Code:
Set objExcel = CreateObject("Excel.Application")

Set wb = objExcel.Application.Workbooks.Open("C:\Users\Template\Order_Template.xlsm")


objExcel.Application.Run "'" & wb.Name & "'!save_Report"


wb.Save
wb.Close
Set wb = Nothing

The code works seamlessly. However, one of the the computers I work on, when I run the VBS script to run the VBA macro, the codes to run till it encounters error on the following line:

Code:
Workbooks("Order_Template").SaveAs pathName_Order_Stats & "\" & fileName_Order_Stats & ".xlsm", FileFormat:=52

I've been looking at it but I couldn't figure out why that line of code creates problem.

Error message: Run-time error '9':
Subscript out of range
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

I have the following vba macro:

Code:
Sub save_Report()Set fso = CreateObject("Scripting.FileSystemObject")
basePath = "C:\Users\Orders"
pathName = basePath & "\" & thisYear & "\" & month_Name & "\" & get_FolderDate(minus_day)
fileName_Order_Stats = get_Order_Stats_filename(minus_day)
Workbooks("Order_Template").SaveAs pathName_Order_Stats & "\" & fileName_Order_Stats & ".xlsm", FileFormat:=52
End Sub

I have a VBS script that call the above macro:

Code:
Set objExcel = CreateObject("Excel.Application")

Set wb = objExcel.Application.Workbooks.Open("C:\Users\Template\Order_Template.xlsm")


objExcel.Application.Run "'" & wb.Name & "'!save_Report"


wb.Save
wb.Close
Set wb = Nothing

The code works seamlessly. However, one of the the computers I work on, when I run the VBS script to run the VBA macro, the codes to run till it encounters error on the following line:

Code:
Workbooks("Order_Template").SaveAs pathName_Order_Stats & "\" & fileName_Order_Stats & ".xlsm", FileFormat:=52

I've been looking at it but I couldn't figure out why that line of code creates problem.

Error message: Run-time error '9':
Subscript out of range

Is that computer otherwise able to save to that path?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
With that error, it indicates that something in that line of code is preventing it from finding the object it is looking for. It could be spelling error. It could be missing file extension, (Workbooks("Order_Template.xls?"). It could be upper/lower case difference. But something is not matching whatever the object is. In this case the only existing object is that workbook. Also does the file path exist? Is the workbook open that is being saved?
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Does Workbooks("Order_Template") refer to the workbook the code is in?

If it does then you can replace Workbooks("Order_Template") with ThisWorkbook.

Otherwise I'd suggest you try JLGWhiz's suggestion of adding in the file extension.
 

30percent

Board Regular
Joined
May 5, 2011
Messages
102
HI, once I add the file extension to it, the problem is solved.

Thank you!!

With that error, it indicates that something in that line of code is preventing it from finding the object it is looking for. It could be spelling error. It could be missing file extension, (Workbooks("Order_Template.xls?"). It could be upper/lower case difference. But something is not matching whatever the object is. In this case the only existing object is that workbook. Also does the file path exist? Is the workbook open that is being saved?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,219
Messages
5,640,958
Members
417,183
Latest member
CuteLeo

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
Top