0

I am using the code below to retrieve a number from a closed workbook. The MsgBox returns the correct value but I am unable to assign that number to my modelVersion variable and don't understand why this is the case. I would appreciate if someone can help me out as I figure that the ExecuteExcel4Macro method maybe can't be used in this way?

Public Sub checkModelVersion()

    Dim wbPath As String, wbName As String
    Dim wsName As String, cellRef As String
    Dim Ret As String

    wbPath = "C:\mypath\"

    wbName = "Update.xlsm"
    wsName = "Dashboard"
    cellRef = "E7"

    Ret = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, -4150)

    MsgBox ExecuteExcel4Macro(Ret)
    modelVersion = ExecuteExcel4Macro(Ret)

End Sub
Pᴇʜ
  • 45,553
  • 9
  • 41
  • 62
ChartProblems
  • 235
  • 3
  • 13
  • Is modelVersion declared outside your sub in a module? – EvR Jun 14 '19 at 12:05
  • I guess you are not able to get data from a closed workbook. You have to open it. – MrZH6 Jun 14 '19 at 12:32
  • Possible duplicate of [Copy data from another Workbook through VBA](https://stackoverflow.com/questions/7401967/copy-data-from-another-workbook-through-vba) – Robert Todar Jun 14 '19 at 14:44

0 Answers0