none
How to access to a "Variable" situated in a closed workbook RRS feed

  • Question

  • Hi

    Considering a Name declared in a Workbook and called "myName" - this Name belong to the workbook.names collection but do not correspond to any cell or range.

    I'd like to access to this name without being obiged to open the workbook where it is declared but so far all what I have tried failed... :(

    - have tried ADODB but seems to be limited to named ranges

    - have tried with Application.ExecuteExcel4Macro but also seems to be working only for cells

    ---- Application.ExecuteExcel4Macro('C:\Path\[file.xls]Sheet1'!R1C1) is working

    ---- Application.ExecuteExcel4Macro('C:\Path\file.xls'!myName) is not working while I was expecting to :(

    Woud be appreciated if someone could help me

    Regards // Alain

    Friday, January 23, 2015 1:29 PM

Answers

  • Then I would recommend that you store the value in a separate worksheet, and refer this Name to a cell in that worksheet.

    >>So perhaps I should stop trying to do that without opening the WB... What I do not really know is how time consuming could be to open a big workbook... Should do tests...

    The speed will depend on the machine performance, as wel as the amount of data(plain data/formulas/macros) stored in the workbook. If the data is really huge, I would recommend that you don't store it in Excel workbook, but in some database.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, January 28, 2015 3:21 AM
    Moderator

All replies

  • Hi Alain,

    Please firstly check if the range name "myName" exists in the workbook file.xls, then try to add double quotation marks """ for the parameter of Application.ExecuteExcel4Macro, because it works fine for me in the following code:

    Public Sub Test()
        Dim fileName As String
        Dim wb As Workbook
        fileName = "C:\Path\file.xlsx"
        Set wb = Application.Workbooks.Open(fileName)
        MsgBox wb.Names("myName").RefersToRange.Value
        wb.Close
        
        MsgBox Application.ExecuteExcel4Macro("'C:\Path\[file.xlsx]Sheet1'!R1C1")
        MsgBox Application.ExecuteExcel4Macro("'C:\Path\[file1.xls]Sheet1'!myName")
        
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 26, 2015 6:35 AM
    Moderator
  • Thanks but this will not work assuming the names I want to read the value have been declared as Workbook Names...

    Workbooks("workBook.xls").Names.Add ... RefersTo:= "=""value"""

    Regards // Alain


    Monday, January 26, 2015 2:58 PM
  • Based on my knowledge, I'm afraid you can't do this. Open the workbook and you'll get the value, it's not very complecate, create a function, open the workbook and get the name value in this function.

    Dim fileName As String
        Dim wb As Workbook
        fileName = "C:\Path\file.xlsx"
        Set wb = Application.Workbooks.Open(fileName)
        wb.Names.Add Name:="myName", RefersTo:="=""Value"""
        MsgBox wb.Names("myName").RefersTo
        wb.Close


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 27, 2015 10:04 AM
    Moderator
  • Thanks again but I am really willing not to open the workbook. This WB is used as a kind of database after identification I simply extract data using ADODB queries which doesnt need to open the workbook. So my idea was to try to do the WB identification the same way, I mean without opening it...

    So perhaps I should stop trying to do that without opening the WB... What I do not really know is how time consuming could be to open a big workbook... Should do tests...

    Thanks and Regards // Alain 

    Tuesday, January 27, 2015 12:52 PM
  • Then I would recommend that you store the value in a separate worksheet, and refer this Name to a cell in that worksheet.

    >>So perhaps I should stop trying to do that without opening the WB... What I do not really know is how time consuming could be to open a big workbook... Should do tests...

    The speed will depend on the machine performance, as wel as the amount of data(plain data/formulas/macros) stored in the workbook. If the data is really huge, I would recommend that you don't store it in Excel workbook, but in some database.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, January 28, 2015 3:21 AM
    Moderator