Get data from a separate workbook in a different location WITHOUT opening it

[Woftae]

I have an excel file that needs to reference another excel workbook that is in another location (directory) so file path needs to be included.
I do not want to have to open this external workbook as it is in constant use by another user and it is a large file and slow to open and close etc.
But the code I've written keeps returning a runtime error: 9 Subscript out of range. indicate with code in green below.
I have tried many different code configurations but have not been able to get it to work yet.
My current/latest code is attached.
Thank in advance for any assistance.

'Sub GetOrderInfo(lastrow)
Sub GetOrderInfo()

Dim WBOrders As String
WBOrders = "Z:\Suppliers Orders\Suppliers Orders V4.3.xlsm"

Dim WshOrders As String
WshOrders = "Orders"

Dim WshPrevious As Worksheet
Dim WshLatest As Worksheet

Set WshLatest = Sheets("Latest")
Set WshPrevious = Sheets("Previous")

Dim LLastRow As Integer
LLastRow = Sheets("Latest").Range("A" & Rows.Count).End(xlUp).Row + 1

Dim PLastRow As Integer
PLastRow = Sheets("Previous").Range("A" & Rows.Count).End(xlUp).Row + 1

Dim OLastRow As Integer

MsgBox Dir("Z:\Suppliers Orders\Suppliers Orders V4.3.xlsm")
OLastRow = Workbooks(WBOrders).Worksheets(WshOrders).Range("A" & Rows.Count).End(xlUp).Row + 1 '<<< Error occurs here

Dim OStartRow As Integer
OStartRow = OLastRow - 1000

Dim OrderFound As String
OrderFound = ""

Dim OSupName As String
OSupName = ""

Dim Li As Integer
Li = LLastRow

 

[Zot]

Have a look at this

Best way to get data from a closed workbook

Usually use code in TestCopy but then recently saw testCopy, so was wondering which is the best way Also in the first version if it contains a password you can use that in the code but wasn't sure how to use in the Get Object version Sub testCopy() Dim wb As Workbook...

www.mrexcel.com

 

[Woftae]

Hi Zot, thankyou for your response and I am very sorry for my slow response, I will defiantly check your reference and let you know how I went.