Subscript out of range error

[tiredofit]

What is wrong with the following?

I get a Run-time error '9', subscript out of range error.

Workbooks.Open "C:\Files\MyWorkbook.xlsx"

Dim a As Long

a = Workbooks("C:\Files\MyWorkbook.xlsx").Worksheets.Count

Thanks

 

[steve the fish]

Try like this:

Dim wb As Workbook, a As Long

Set wb = Workbooks.Open("C:\Files\MyWorkbook.xlsx")
a = wb.Worksheets.Count

 

[NARAYANK991]

Hi ,

I think the reason for the error is that once a workbook is open , the pathname is not used when accessing it.

In the code you posted , try using :

a = Workbooks("MyWorkbook.xlsx").Worksheets.count

Or , you could go with what Steve has posted.

 

[tiredofit]

Try like this:

Dim wb As Workbook, a As Long

Set wb = Workbooks.Open("C:\Files\MyWorkbook.xlsx")
a = wb.Worksheets.Count

Hi ,

I think the reason for the error is that once a workbook is open , the pathname is not used when accessing it.

In the code you posted , try using :

a = Workbooks("MyWorkbook.xlsx").Worksheets.count

Or , you could go with what Steve has posted.

Thanks, both suggestions worked.

BTW, is there a way of doing this WITHOUT opening the workbook?

I'm trying to refer to a range within a closed workbook.