CODEWITHSUNDEEP
vbaexcel
Nega developer
Nega developer

Reputation: 269

Run-time Error 9 for Workbooks.Activate

I am having the error in Visual Basic when I try to use the function 

Workbooks("C:\Documents and Settings\Nega\test.xls").Activate

The error says that:

Error 9 in run time error: Subscript out of range.

I saw a lot of post about extension, but my file exists and the extension is correct.

It is may problems of privileges? In that case how can I add privileges in VBA for this directory?

Note: If I do Workbooks("test.xls").Activate it works (Ofc, with the file into the project). So for this I think it is about privileges.

Thanks for the help.

Upvotes: 0

Views: 17252

Answers (3)

Marcel Nijskens
Marcel Nijskens

Reputation: 1

you can't use path names in the workbooks.activate When/because the Excelfile is already open it only needs the filename WITHOUT the path. So Workbooks("C:\MN\DoubleClick.xlsx").Activate will give the error while Workbooks("Double click worklist BTN.xlsx").Activate wil work perfectly

Upvotes: 0

paul bica
paul bica

Reputation: 10715

A few details about your question and the reasons it doesn't work:

"Workbooks" is a collection of files currently open in Excel

  • your file exists on disk, but if is not open it doesn't exist in Workbooks collection, so the first step is to open it (as suggested)
  • once the file is open Excel adds it to Workbooks collection. You can highlight the word "Workbooks" in VBA and add it to the Watches window to see all its items (files) and each item's properties
  • you have access to the items in the collection by their .Name
    • the name doesn't include the path, only Name.ext
  • if the file is not saved yet, the name doesn't contain the extension

.

Option Explicit

Sub wbActivate()

    Dim wb As Workbook

    MsgBox Workbooks.Count  'currently open files count: 1
    Set wb = Workbooks.Open("C:\test.xls")
    MsgBox Workbooks.Count  'currently open files count: 2

    wb.Activate                     'the file we just opened
    Workbooks("test.xls").Activate  'the file we just opened

    Workbooks("book1").Activate     'initial file (unsaved yet)

End Sub

Upvotes: 1

mielk
mielk

Reputation: 3940

Few things need to be fixed here:

  • You miss colon in the filepath. It should be C:\Documents and Settings\Nega\test.xls.

  • You have a typo in the name of the function - it should be Workbooks.

  • You can't activate a file unless it is open, so first you needs to ensure that it is already open. You can do it with this command:

    Call Workbooks.Open("C:\Documents and Settings\Nega\test.xls").Activate

Upvotes: 0

Related Questions