VBA code for opening 2 files in different file paths

blueeagles003

New Member
Joined
Feb 27, 2019
Messages
1
Hi All,

I am new to VBA and I am trying to have one sub open 2 files that are stored in different file paths. The below is my current code:

Sub ExceptionReportSCV()
'
' ExceptionReportSCV Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Workbooks.Open Filename:=Sheets("Execute").Range("F1")
Workbooks.Open Filename:=Sheets("Execute").Range("F2")



And the below is what is being referenced in sheet "Execute" within cells F1 & F2:

cell F1 is: S:\Institutional Trading\Automated Reports\Model Exception Reports\Small Cap Value\SCV_Model_Exc_Report_02272019.xlsx

cell F2 is: S:\Institutional Trading\Daily Sign Offs\Exception Reports\SCV\SCV_Model_Exc_Report_02262019.xlsx

When running my macro I get a Run-time error '9': Subscript out of range. The first workbook referencing cell F1 opens successfully but the second command calling cell F2 fails to open. Curiously, when I try to have the macro just call the file path in F1 or F2 they both work successfully, but when I have the two commands under the same Sub, the second command fails. Does anyone have any insight to how I can rework this code to have both files open through one sub? Thank you!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
The problem is because when you open the first book, in that book the "exeute" sheet does not exist, that's why it sends you the error.

Try:

Code:
    Set sh = Sheets("Execute")
    Workbooks.Open Filename:=sh.Range("F1")
    Workbooks.Open Filename:=sh.Range("F2")

Or :

Code:
    Workbooks.Open Filename:=ThisWorkbook.Sheets("Execute").Range("F1")
    Workbooks.Open Filename:=ThisWorkbook.Sheets("Execute").Range("F2")
 

Watch MrExcel Video

Forum statistics

Threads
1,108,633
Messages
5,523,995
Members
409,555
Latest member
TIPSAREA

This Week's Hot Topics

Top