Using SUMIF with ExecuteExcel4Macro

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Hello everyone

I am trying to deal with some closed workbooks and tried to use ExecuteExcel4Macro approach and here's some hints This is the normal formula

=SUMIF('[Example.xlsx]Sheet1'!$B:$B,C3,'[Example.xlsx]Sheet1'!$C:$C)
I used s string to store the address of column B and t to store the address of column t of the closed workbook

Code: Select all

s = "'" & myDir & "[" & fn & "]" & "Sheet1" & "'!" & Columns(2).Address(True, True, -4150)
the same with the variable t but to change Columns(2) to Columns(3) And in the immediate window I got something like that 'C:\Users\Any\Desktop\[Example.xlsx]Sheet1'!C2

Then I tried to use

Code: Select all

Debug.Print ExecuteExcel4Macro("SUMIF(" & s & "," & Range("C3").Address(True, True, -4150) & "," & t & ")")
But I encountered an error Method ExecuteExcel4Macro of object _Global failed Any ideas?

Another point how to get the worksheet name without the need to know the name by referring for example: Worksheets(1).Name in the variable s?

I noticed that the formula is working when the workbook is open but it throws an error #VALUE when the workbook is closed.

I also tried to use SUMPRODUCT as an alternative to SUMIF (to deal with closed workbooks) but with no success too

Code: Select all

Debug.Print ExecuteExcel4Macro("SUMPRODUCT((" & s & "=" & c.Offset(3 - c.Row, n).Address(True, True, -4150) & ")*(" & t & "))")
Even used Evaluate instead of ExecuteExcel4Macro but the same (NO SUCCESS)!!

Finally, the only solution that has worked is to put the string as a formula then to convert the result to a value
Thanks advanced for any ideas.

User avatar
HansV
Administrator
Posts: 71978
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using SUMIF with ExecuteExcel4Macro

Post by HansV »

ExecuteExcel4Macro evaluates - surprise, surprise - Excel 4.0 Macro commands. SUMIF and SUMPRODUCT are not Excel 4.0 Macro commands but worksheet functions. So you cannot do what you want.
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Thanks a lot. Is there a workaround to solve such a problem?

User avatar
rory
5StarLounger
Posts: 742
Joined: 24 Jan 2010, 15:56

Re: Using SUMIF with ExecuteExcel4Macro

Post by rory »

ExecuteExcel4Macro will run functions like SUMIF and SUMPRODUCT but you need to use R1C1 references and properly qualify the sheet. SUMIF will not work with a closed workbook no matter how you call it.
Regards,
Rory

User avatar
HansV
Administrator
Posts: 71978
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using SUMIF with ExecuteExcel4Macro

Post by HansV »

If your data are in table format, you can use ADO to create a connection to the external closed workbook and run a query.
See for example How to use ADO and VBA to Read from Worksheets
Regards,
Hans

User avatar
HansV
Administrator
Posts: 71978
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using SUMIF with ExecuteExcel4Macro

Post by HansV »

rory wrote:
15 Sep 2021, 08:06
ExecuteExcel4Macro will run functions like SUMIF and SUMPRODUCT but you need to use R1C1 references and properly qualify the sheet.
I tried that before posting my reply but still got an error...
Regards,
Hans

User avatar
rory
5StarLounger
Posts: 742
Joined: 24 Jan 2010, 15:56

Re: Using SUMIF with ExecuteExcel4Macro

Post by rory »

It works for me with something like:

Code: Select all

application.ExecuteExcel4Macro("SUMPRODUCT('C:\testing\[testex.xlsx]Sheet1'!R8C3:R19C3,'C:\testing\[testex.xlsx]Sheet1'!R8C4:R19C4)")
Regards,
Rory

User avatar
HansV
Administrator
Posts: 71978
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using SUMIF with ExecuteExcel4Macro

Post by HansV »

It works, of course. I misspelled the sheet name... :stupidme:
Regards,
Hans

User avatar
rory
5StarLounger
Posts: 742
Joined: 24 Jan 2010, 15:56

Re: Using SUMIF with ExecuteExcel4Macro

Post by rory »

:) Just to show you're human.
Regards,
Rory

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Thank you very much. I tried with all my efforts to get it working and this is my try

Code: Select all

Sub MyTest()
    Dim myDir As String, s As String, t As String, fn As String
    myDir = ThisWorkbook.Path & "\"
    With ThisWorkbook.Worksheets(1)
        Rem A10 Has The Value "Sample"
        Rem B10 Has The Value "1"
        fn = Dir(myDir & "\" & .Range("A10").Value & ".xlsx")
        If fn <> "" Then
            s = "'" & myDir & "[" & fn & "]" & "Sheet1" & "'!" & Range("B2:B12").Address(True, True, -4150)
            t = "'" & myDir & "[" & fn & "]" & "Sheet1" & "'!" & Range("C2:C12").Address(True, True, -4150)
            .Range("C10").Value = Application.ExecuteExcel4Macro("SUMPRODUCT((" & s & "=" & .Range("B10").Address(True, True, -4150) & ")*(" & t & "))")
        End If
    End With
End Sub
The code wil be in an xlsm workbook. And here's the Sample workbook for testing
You do not have the required permissions to view the files attached to this post.

User avatar
rory
5StarLounger
Posts: 742
Joined: 24 Jan 2010, 15:56

Re: Using SUMIF with ExecuteExcel4Macro

Post by rory »

You didn't properly qualify the B10 reference:

Code: Select all

Application.ExecuteExcel4Macro("SUMPRODUCT((" & s & "='" & .Name & "'!" & .Range("B10").Address(True, True, -4150) & ")*(" & t & "))")
Regards,
Rory

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Amazing. So I have to deal with thisworkbook object with the same logic and refers to the worksheet name preceded by apostrophe and followed by another.
Thank you very much Mr. Rory for your great help.

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Just one more question. Is it possible to return the worksheet name of the closed workbook without hardcoding it? I mean "Sheet1" is used as the worksheet name of the closed workbook. Is it possible to get the worksheet name of the first worksheet in the closed workbook?

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

I have searched and found this approach

Code: Select all

                Dim sh As Object
                Set sh = GetObject(myDir & fn).Worksheets
                Debug.Print sh.Item(1).Name
Thank you very much.

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Delete this post.

User avatar
rory
5StarLounger
Posts: 742
Joined: 24 Jan 2010, 15:56

Re: Using SUMIF with ExecuteExcel4Macro

Post by rory »

Just FYI, that is opening the workbook.
Regards,
Rory

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Do you mean the method GetObject open the workbook? If yes, is there an approach to get the worksheet name without opening the workbook?

User avatar
HansV
Administrator
Posts: 71978
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using SUMIF with ExecuteExcel4Macro

Post by HansV »

Yes, GetObject opens the workbook in Excel (but hidden).

You might use ADO and ADOX - here is an example:

Code: Select all

Sub GetSheetNames()
    Dim sPath As String
    Dim cnn As Object ' ADODB.Connection
    Dim cat As Object ' ADOX.Catalog
    Dim tbl As Object ' ADOX.Table
    ' *** Supply your own path and filename! ***
    sPath = "C:\Excel\MyWorkbook.xlsx"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" & _
        "Initial Catalog=" & sPath
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = cnn
    For Each tbl In cat.Tables
        Debug.Print tbl.Name
    Next tbl
    Set cat = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4500
Joined: 31 Aug 2016, 09:02

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Thank you very much, my tutor. As for this approach, will it be possible to get the last row of a specific column?

User avatar
HansV
Administrator
Posts: 71978
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using SUMIF with ExecuteExcel4Macro

Post by HansV »

If you want to do all those things, why do you insist on not opening the workbook? :scratch:
Regards,
Hans