0

I have this closed workbook named DataData.xlsm where I am trying to search for "Fido" in column A and where I would like getting the cell value in the same row but in column C using offset. And it is the offset that is the problem.

I'm trying to avoid any "open workbook" code.

My code is located in a module in a workbook named BOB44.xlsm and I need the value in in sheet1, cell A1. So far I would just like to get the value from DataData-file.

I keep getting error from the CellReference. I'm new to vba, so it might be an easy fix, but I have a little trouble getting it fixed. Hope anyone can help.

Sub VBA_Get_Value_From_Closed_Workbook()

    'Variable declaration
    Dim FilePath As String, Filename As String
    Dim SheetName As String, CellReference As String
    Dim sInput As String
    
    
    'Assign values to variables
    FilePath = "C:\"
    Filename = "DataData.xlsm"
    SheetName = "AA"
    CellReference = Range("A:A").Find(What:="Fido", LookIn:=xlValues, LookAt:=xlPart).Offset(, 2).Value
    
    sInput = "'" & FilePath & "[" & Filename & "]" & SheetName & "'!" & CellReference
    
    'Display Message
    MsgBox sInput
End Sub
vbaismylife
  • 1
  • 1
  • 1
    Does this answer your question? [How to copy data from closed workbooks (keeping them closed) into master workbook using VBA?](https://stackoverflow.com/questions/29310458/how-to-copy-data-from-closed-workbooks-keeping-them-closed-into-master-workboo) – braX Nov 25 '21 at 11:28

0 Answers0