Read data from file without opening them?

[NessPJ]

Hello All,

Only recently i've found out its actually possible to find a value from a file without going through the seperate hassle of opening it etc.

I found the following code to do this:

Sub ReadClosed()
    '
    ' Credit this To Bob Umlas
    '
    Dim strPath As String
    Dim strFile As String
    Dim strInfoCell As String
     
    strPath = Sheets("Parameters").Range("F14").Value
    strFile = Sheets("Parameters").Range("F15").Value
    strInfoCell = "'" & strPath & "[" & strFile & "]Sheet1'!R2C1"
    MsgBox "In Cell A2 = " & ExecuteExcel4Macro(strInfoCell), vbInformation, strFile
     
End Sub

I tried modifying the code to work the way i would want it to (i refer to some cells in my sheet, rather then having the path hardcoded entirely....this gives a poweruser some more control for the future).

Sub ReadOnly2()
    '
    ' Credit this To Bob Umlas
    '
    Dim strPath As String
    Dim strFile As String
    Dim strSheet As String
    Dim strInfoCell As String
     
    strPath = Sheets("Parameters").Range("F14").Value
    strFile = Sheets("Parameters").Range("F15").Value
    strSheet = Sheets("Parameters").Range("F16").Value
    strInfoCell = "'" & strPath & "[" & strFile & "]" & strSheet & "'!M990"
    MsgBox "In Cell M990 = " & ExecuteExcel4Macro(strInfoCell), vbInformation, strFile   
  
End Sub

I verified the path that is created this way a couple of times and it seems to be 100% correct.
The only difference is, i would like to address the actual Cell number rather then using the R1C1 method. Is this possible this way?

Thanks in advance for any help!

 

[NessPJ]

Hey guys,

I'd like to thank you guys a lot for your input!
After seeing the above methods, i decided to try something else...
I made an extra sheet on the source workbook i am trying to get data from and hid it.
This allowed me to leave the whole SUMIF deal alone and just use a VLOOKUP instead.
I am now going to try and use the VLOOKUP function for every workbook i am getting data from.
In the end i will create a piece of VBA where each of the formula parameters are taken from a Cell (on a 'parameter' worksheet)
and after the data is retreived will be pasted as a value in its destination cell.
This makes both the starting and updating of the workbook very fast and keeps it small at the same time (there are no active data links etc).

Here's an example of what i have so far:

Sub UpdateDashboard()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    
    Dim strPath As String
    Dim strFile As String
    Dim strSheet As String
    Dim strCell As String
    Dim strColumn As String
    Dim strValue As String
    Dim strVlookup As String
    Dim strVlookup2 As String
    Dim strFormula As String
    
    strPath = Sheets("Parameters").Range("F14").Value
    strFile = Sheets("Parameters").Range("F15").Value
    strSheet = Sheets("Parameters").Range("F16").Value
    strCell = "B4"
    strColumn = Sheets("Parameters").Range("F17").Value
    strValue = Sheets("Parameters").Range("L17").Value
    strVlookup = "Vlookup(" & strCell & ",'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strColumn & "," & strValue & ",0)"
    strVlookup2 = "Vlookup(" & strCell & "-1,'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strColumn & "," & strValue & ",0)"
    strFormula = "=IF(ISNA(" & strVlookup & ")," & strVlookup2 & "," & strVlookup & ")"
    
    Sheets("Parameters").Range("B14").Formula = strFormula
    
    Calculate
    
    Sheets("Parameters").Range("B14").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Now all i would like to add in the end is a piece of VBA with a Start and Stop button that will update the workbook automatically at a configured interval (15 mins for example) as long as the workbook is opened.
Is there an easy way to do this perhaps?