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

[30percent]

Hi,

I have the following vba macro:

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:

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:

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

 

[JonXL]

Hi,

I have the following vba macro:

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:

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:

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]

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?

 

[Norie]

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]

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?

 

[JLGWhiz]

HI, once I add the file extension to it, the problem is solved.

Thank you!!

You're welcome,
regards, JLG