Get Values from Closed File

[Pat_The_Bat]

I need to get values from another file on the server. I don't want to open the file. Is there a way to reference a cell in a closed file?
Really appreciate any help that you can give!

 

[mrshl9898]

You sure can, the reference will look like this.

='filelocation\[filename]sheetname'!cell

='C:\Users\andrew.mathews\Desktop\[Book7.xlsx]Star Chart'!$AD$121

 

[JumboCactuar]

come across this before and it works, though unsure how :D

Option Explicit


Sub Sample()
    Dim wbPath As String, wbName As String
    Dim wsName As String, cellRef As String
    Dim Ret As String


    wbPath = "C:\testing\aaa\"


    wbName = "a1.xlsx"
    wsName = "Sheet2"
    cellRef = "G19"


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


    Range("A1").Value = ExecuteExcel4Macro(Ret)
End Sub

 

[sandy666]

or try PowerQuery. You'll be allowed to choose: table, set of data, cell, cells etc.
(Excel 2010/2013 add-in, 2016 and higher - built-in)