1

I am trying to retrieve a cell value from another workbook without opening the source file. The below will return the value from "SourceRange" when I use the actual cell address. However, I want to modify the below code to reference the cell name instead of the cell address because lines may be added. So if the cell name in the source workbook is named "Total", is there a way for me to retrieve the value?

Sub OpenFiles()

Dim Folder_YR As String
Dim Folder_MO As String
Dim Path As String
Dim File As String
Dim SourceSheet As String
Dim SourceRange As String

Folder_YR = Sheets("Inputs").Range("B5").Value
Folder_MO = Sheets("Inputs").Range("B9").Value
FN = Sheets("Inputs").Range("B10").Value


Path = "G:\PMT ACTIVITY\" & Folder_YR & "\" & Folder_MO & "\"
File = FN

SourceSheet = "Summary"
SourceRange = Range("C1").Address(1, 1, xlR1C1)

Ref = "'" & Path & "[" & File & "]" & SourceSheet & "'!" & SourceRange

Sheets("Model").Range("C18").Formula = ExecuteExcel4Macro(Ref)

End Sub
nam24
  • 51
  • 9
  • Have you tried the ADO method? Not sure if that works with named range... http://stackoverflow.com/questions/16859255/reading-a-workbooks-without-opening-it-with-ado – David Zemens Jul 07 '16 at 00:04

1 Answers1

0

The format is something like 'C:\Book1.xlsx'!Total

https://www.ablebits.com/office-addins-blog/2015/12/08/excel-reference-another-sheet-workbook/#reference-to-defined-name-another-workbook

[Model!C18].Formula = "='" & Path & File & "'!Total"
Slai
  • 22,144
  • 5
  • 45
  • 53