Runtime error 9 when trying to reference another workbook

**MingVBA** · 06-30-2011, 06:03 AM

Hi all

I wrote below code to refer to another work but got runtime 09 error, would you please point out what mistake I had made?

Dim Currentpath As Variant
Currentpath = "C:\"        
MsgBox Workbooks(Currentpath & "book1.xls").Sheets("Sheet1").Range("A1").Value

**Colin Legg** · 06-30-2011, 06:25 AM

Hi,

You have to open the other workbook before you can read values from it (unless you use ADO).

Dim Currentpath As String
Dim wkbTest As Workbook

Currentpath = "C:\"   
     
Set wkbTest = Workbooks.Open(Currentpath & "book1.xls")

MsgBox wkbTest.Sheets("Sheet1").Range("A1").Value

**MingVBA** · 06-30-2011, 08:50 AM

Thanks and I fixed the problem.

One more question, is it not necessary to provide the full path of the file? as I found problem in below code:

Workbooks("c:\book2.xlsx").Sheets("Sheet1").Range("A1")

but fine for below:

Workbooks("book2.xlsx").Sheets("Sheet1").Range("A1")

**Colin Legg** · 06-30-2011, 03:30 PM

When you are opening the file, it's better to use the full file path, otherwise it assumes the current directory. Once the file is open and you want to refer to it in the Workbooks collection, don't use the full path - as per your example.

**MingVBA** · 06-30-2011, 08:24 PM

Thank you so much!

**Mordred** · 06-30-2011, 08:48 PM

Originally Posted by MingVBA

Workbooks("c:\book2.xlsx")

When you are using Workbooks in your code you are calling the name of the workbook and not the file path. Your example that I quoted would look for a workbook named c:\book2.xlsx instead of book2.xlsx.