CODEWITHSUNDEEP
vbaexcelrangeout
user6879637
user6879637

Reputation: 41

VBA workbook object giving "Subscript Out of range Error"

I'm trying to use below code PrintWB has following value "C:\Users\jarora\Desktop\Master Test Sheet.xlsx"

PrintWB = .Cells(i, 1) & "\" & .Cells(i, 2)
Workbooks(PrintWB).Sheets(Sheetname).Printout From:=Frompage, To:=Topage

Above line is giving me error 'Subscript Out of range Error' when referencing Workbook object.

I get the following error on the above line when referencing the Workbook object:

Subscript Out of Range Error

Any help will be appreciated immensely since this is my last piece of puzzle I'm trying to put together.

Upvotes: 0

Views: 3531

Answers (1)

Tyeler
Tyeler

Reputation: 1118

The issue you're having is that you're trying to print a workbook that Excel can't see. Workbooks() is a collection of all open workbooks.

Step 1 is opening the target workbook. Before we just open it though, it's always best to check that the file path is accurate. A simple IF STATEMENT will suffice: If Dir(yourfilepath) <> "" Then

Step 2 is to open the target workbook, print the target sheet, and then close the workbook. I added the line Workbooks(targetworkbook).Saved = True. This will hide the "Do you want to save your changes?" Dialog that pops up when we try to close the workbook. We know we didn't make any changes, so using this method is safe.

WBPath = .Cells(i, 1) & "\" & .Cells(i, 2)
PrintWB = .Cells(i,2) 'I'm assuming this is "Master Test Sheet.xlsx"
If Dir(WBPath) <> "" Then
    Workbooks.Open (WBPath)
    Workbooks(PrintWB).Sheets(Sheetname).PrintOut From:=1, To:=1
    Workbooks(PrintWB).Saved = True
    Workbooks(PrintWB).Close
Else: MsgBox "File not found.", vbCritical
End If

Upvotes: 1

Related Questions