Activating closed workbooks

Question

I am using the function below to extract data from other workbooks.

Function GetValue(path, file, sheet, ref)

    'Retrieves a value from a closed workbook
    Dim myArg As String
    
    'Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    
    'Create the argument
    myArg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
    
    'Execute an XLM macro
    GetValue = ExecuteExcel4Macro(myArg)

End Function

I am calling this function like this:

Sub TestGetValue()
    Dim p As String, f As String
    Dim s As String, a As String
    p = "C:\Users\schaudha\Desktop\FIT transition\test simulation results"
    f = "all cancer rate.xml"
    s = "CONTENTS"
    a = "A1"
    MsgBox GetValue(p, f, s, a)
End Sub

This function seems to work only when the workbook is active. I mean, if I open the Excel file that I need data from and then run my subroutine, it works, but if it is closed, it doesn't work. I would also like it work when the workbook is closed. I am guessing I need to activate the workbook somehow before I use ExecuteExcel4Macro(myArg). How do I do that? I plan on using this function to extract data from thousands to cells from about a hundred workbooks, so I want to make this code as efficient as possible.

Your workbook is not an Excel workbook, it's an XML file, which is why the code doesn't work. You would have to actually open the file as a workbook, or use a different method to parse the XML content. — Rory, May 14 '14 at 12:26

score 1 · Answer 1 · answered May 14 '14 at 12:25

I think what you're looking for is (modified from your code):

If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
else
    CurrBook = Workbooks.Open Path & File
End If
'''Code here
CurrBook.Close

This will open the file, if it's found, and you'll be able to extract the data from it. I hope this helps!

score 0 · Accepted Answer · answered May 14 '14 at 14:02

This works

Function GetValue(path, file, sheet, ref)

'Retrieves a value from a closed workbook
Dim myArg As String

Dim CurrBook As Workbook

'Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
End If

Application.Workbooks.Open (path & file)

'Create the argument
myArg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

'Execute an XLM macro
GetValue = ExecuteExcel4Macro(myArg)

Application.Workbooks(file).Close (False)

End Function

score 0 · Answer 3 · edited Jul 19 '21 at 21:14

0

If you're going to open the workbook you don't need ExecuteExcel4Macro at all:

Function GetValue(path, file, sheet, ref)

    Dim CurrBook As Workbook
    
    'Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    
    Set CurrBook = Application.Workbooks.Open(path & file)
    
    On Error Resume Next
    GetValue = CurrBook.Sheets(sheet).Range(ref).Value
    
    CurrBook.Close savechanges:=False

End Function

edited Jul 19 '21 at 21:14

ZygD

22,092
39
79
102

answered May 14 '14 at 14:38

Rory

32,730
5
32
35

yeah I figured, can you think of a way to extract data from xml files without really opening them? – Sahil Chaudhary May 14 '14 at 15:01
Depends on your definition of "opening" I think. ;) There are a few examples here: http://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba – Rory May 14 '14 at 15:38

Activating closed workbooks

3 Answers3