CODEWITHSUNDEEP
excelvba
Gionata Quadri
Gionata Quadri

Reputation: 1

Run time error (9) when defining a Workbook

Good morning,

I'm trying from my master excel workbook to read into another workbook. Anytime I try to set that workbook into a variable (book2) I get the error "Run-time error 9: Subscript out of range". Here the code I wrote:

Const path As String = "\\prod\root\v_drive\IBS\ENG_Data\Jobs\NOETIX_EXPORT_FOR_COMMUNICATION_TOOL\PPM TOOL - Order Level.xls"

Sub Import_Data()
Dim lookFor As String
Dim srchRange As Range
Dim book1 As Workbook: Set book1 = ThisWorkbook
Dim book2 As Workbook: Set book2 = Workbooks(path)
End sub

If instead of the line that gives me an error I enter the code

Workbooks.Open (path)

the files opens without any problem (this should mean that the path is correct and the file exists..). I tried having the file open and closed, on my local drive or in a remote location but I keep having the same error. Do you have any idea what i may be doing wrong? Thanks in advance for your help.

Upvotes: 0

Views: 1303

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

try the code below, explanations inside the code as comments:

Option Explicit

Const path As String = "\\prod\root\v_drive\IBS\ENG_Data\Jobs\NOETIX_EXPORT_FOR_COMMUNICATION_TOOL\PPM TOOL - Order Level.xls"

Sub Import_Data()

Dim lookFor As String
Dim srchRange As Range
Dim ClnFileName As String
Dim book1 As Workbook
Dim book2 As Workbook

Set book1 = ThisWorkbook
' get the clean file name (without the path)
ClnFileName = Mid(path, InStrRev(path, "\") + 1)

On Error Resume Next
Set book2 = Workbooks(ClnFileName) ' try to set the workbook (if it's open)
On Error GoTo 0
If book2 Is Nothing Then
    Set book2 = Workbooks.Open(path)
End If

End Sub

Upvotes: 1

Rafael Matos
Rafael Matos

Reputation: 258

The method Workbooks doesn't accept the path of the file as argument. It only accept the Index or the Name of the Workbook (i.e. they need to be open).

See more in [1]: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-object-excel

Upvotes: 1

Related Questions