Posts by Mackeral

    This old code doesn't work with the "Names Manager" any more

    Quote

    TS = Names("Name_to_Lookup").Value


    Now what I get is the "refersTo" information.


    How do you extract the "Value" portion in Excel 2016?


    At this point I have spent so much time converting code that I wish I'd never switched from 2007. 2016 is so much slower to start. Don't switch!

    This old code doesn't work any more

    Quote

    Names("Name_to_Lookup").Value


    doesn't work in 2016 any more. (Why would Microsoft change something like this?


    Now what I get is the "refersTo" information.


    How do you extract the "Value" portion?

    Re: vba how to access a different workbook


    I've discovered that the problem lies somewhere else in my code with the SHEET specification. So please ignore this thread.


    I would delete it myself but I can' figure out how. It says "To edit or delete your posts, click the [Blocked Image: http://www.ozgrid.com/forum/images/buttons/edit_40b.png] Edit Post button by the particular post. If your post was the first in the thread, then deleting it may remove the entire thread", but I don't see any way to actually delete it when I do that. If someone reads this who know how to mark or delete posts, could you let me know how.


    Thanks,
    Mac

    Re: vba how to access a different workbook


    Since I put a "Watch" on WKBK and found in it's Watch Display the sheets, including one test one that I put there explicitly for that purpose, that I expected, I'm assuming that Workbook_Get returned a valid workbook. I've also tried the code
    [code]
    Set SHEET = Workbooks(Book_Name).Sheets(Sheet_Name)
    [\code]


    which produced the same error message as previously.


    Mac

    Here is my code

    Code
    Dim WKBK as Workbook
             Dim SHEET As Worksheet
    
    
             Set WKBK = Workbook_Get(Path, FileName)
             WkBk_Name = WKBK.Name    
                
             Set SHEET = WKBK.Worksheets(FileName)  <-- Error 9, "subscript out of range"


    I can put a "Watch" on WKBK. It shows the sheets I expect to find, so obviously (at least to me) WKBK has been initialized. However I get an error on the "Set SHEET" statement. "FileName" is the name of both the Workbook and it's Worksheet that I want. And if you look at WKBK, the sheet is there. Any idea what's going on?


    And as a second question, does the Error Number have any useful meaning?


    Thanks for any help


    Mac


    Keywords: workbooks vba sheet access