Consulting

Results 1 to 7 of 7

Thread: Solved: Simple error message

  1. #1

    Solved: Simple error message

    I got a new computer with a new Excel configuration. I'm writting a macro that includes the following simple line:

    Set wBook = Workbooks("FilePlan.xls")

    and I receive a "Subscript out of range" error (wBook is Empty). I have checked that he file exists and it is in the same directory as the file executing the Macro.

    I suspect, there's a Security option preventing me from accessing other files. Any hint? Thanks!!

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    That would ok IF it was already open.

    You can add the path by ThisWorkBook.Path. e.g.
    [vba]Sub Test()
    Dim wb As Workbook
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\ken.xls")
    MsgBox ActiveWorkbook.Name
    TestWhenWBIsOpenAlready
    End Sub


    Sub TestWhenWBIsOpenAlready()
    Dim wb2 As Workbook
    Set wb2 = Workbooks("ken.xls")
    MsgBox wb2.Name
    End Sub[/vba]

  3. #3
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    [VBA]Dim wBook As Workbook
    Set wBook = Workbooks.Open(ActiveWorkbook.Path & "\" & "FilePlan.xls")[/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Ken got here before I refreshed......
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    Sorry, I thought it worked but not. I don't want to open that Workbook. I just want to access some date on a sheet of a specifique Workbook. How can I access it with a command? I tthought

    Workbooks("name").Sheets("Name"). Select shoudl work.


    Any hint?

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    That is a whole other question.

    Is the goal to Select a cell in the other workbook or get the value?

    There are 2 methods to get a value, (1) with the workbook closed and (2) with the workbook open.


    If the goal is to get a value using method (1), then see: http://www.vbaexpress.com/forum/showthread.php?t=24015

  7. #7
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    ACK! Sorry Ken; slow fingers...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •