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!

 

[Firefly2012]

You're not using any R1C1 notation. Could you explain in greater detail what you want to do that the code is not currently doing?

 

[VoG]

You might want to check out Harlan Grove's PULL function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger

 

[NessPJ]

You're not using any R1C1 notation. Could you explain in greater detail what you want to do that the code is not currently doing?

Hello there. I would like to use the VBA posted by me, but without the R1C1 notation. Yet is doesn't seem to be working for my right now (using the code posted above in my initial post). Any ideas?

You might want to check out Harlan Grove's PULL function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger

Thanks for your post. I will keep this in mind, but what i am using right now has a little more functionality and should have better performance too (when it works).
I will extracting quite a few numbers from several files on a network so....

 

[Firefly2012]

Ah I see - apologies, hadnt looked at your code thoroughly enough

You can easily switch between a string between reference styles eg with:

Sub ReadOnly2()
    '
    ' Credit this To Bob Umlas
    '
    Dim strPath As String
    Dim strFile As String
    Dim strSheet As String
    Dim strInfoCell As String
    Dim strAddress As String

    strAddress = Sheets("Parameters").Range("F17").Value    'assume you have the range in A1 notation entered in this cell
     
    strPath = Sheets("Parameters").Range("F14").Value
    strFile = Sheets("Parameters").Range("F15").Value
    strSheet = Sheets("Parameters").Range("F16").Value
    strInfoCell = "'" & strPath & "[" & strFile & "]" & strSheet & Range(strAddress).Address(1,1,xlR1C1)  '=== this bit changes your A1 style address to R1C1
    MsgBox "In Cell" & strAddress & " = " & ExecuteExcel4Macro(strInfoCell), vbInformation, strFile   
  
End Sub

 

[NessPJ]

@ Firefly2012:
Thanks a lot!
Hmm, i just noticed my previous reply on you had some weird typo's (must have been late in the day....), sadly this forum doesn't allow you to edit posts after n amount of time.

One more question.... would it be possible to combine this routine with a function/formula such as SUMIF ?
I have a few cases where a single cell would do, but i also have a case where i would like to sum a column if the right criteria are met.

In this case i have (=SUMIF(Range,Criteria,SumRange) :
Range in Cell F17
Criteria in Cell B4
SumRange in Cell L17

 

[NessPJ]

Anyone?

 

[Fazza]

Try it & see!

I know you asked about SUMIF, fwiw I recollect that SUM could be used; been a long time since I've done that. I'll have a search to see if I can find an example.

There are other ways, such as ADO that can get data from closed workbooks. If you have lots of data that might be a good idea. Works like database queries.

regards

 

[Firefly2012]

You could use the Pull function referred to by Vog or possibly use code to write formulas out to cells, but in either case you will need to use SUMPRODUCT rather than SUMIF(S) as SUMIF(S)/COUNTIF(S) don't work with closed workbooks.

 

[Fazza]

here is an example with SUM http://www.mrexcel.com/forum/excel-questions/250023-sum-range-different-files-one-folder.html

please let us know how you go with the SUMIF

regards