Assigning a cell value to a variable - between different workbooks

[jporjpor]

How, without opening the source workbook, copy the contents of a cell and put it in a variable in the destination workbook?

Example:

valueX = "='c:\code\[example.xlsx]Sheet1'!R2C1"
MsgBox (valueX)

The result is ='c:\code\[example.xlsx]Sheet1'!R2C1
and not the value XPTO

If I assign the reference to a cell directly, it work:

Range("A1").Value="='c:\code\[example.xlsx]Sheet1'!R2C1"
MsgBox(Range("A1").Value)

The result is XPTO

[Gatti]

Hello...

if you do:

valueX = "='c:\code\[example.xlsx]Sheet1'!R2C1"

The valueX will be a string with the whole path assigned, not the reference.

Try this way:

Sub ReferCellClosedWbk()
    
  Dim txt As String
  Dim wb As Workbook
  Dim ws As Worksheet
  
  Application.ScreenUpdating = False
  
  txt = ThisWorkbook.Path & "\example.xlsx"
  
  If Dir(txt) = "" Then
      MsgBox txt & " does not exist"
  Else
      Set wb = Workbooks.Open(txt)
      On Error Resume Next
      Set ws = wb.Sheets("Sheet1")
      If Not ws Is Nothing Then
          MsgBox ws.Range("a1")
          wb.Close
      Else
          MsgBox ws.Name & " does not exist"
      End If
      Err.Clear
  End If
  
  Application.ScreenUpdating = True

End Sub