Using SUMIF with ExecuteExcel4Macro

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

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

As this approach is faster than any other approach. That's the only reason.

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

Re: Using SUMIF with ExecuteExcel4Macro

Post by HansV »

I'd create a SQL query, open a recordset on it using an ADO connection, and load the data into an array using the GetRows method.
Then manipulate the array any way you want.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Using SUMIF with ExecuteExcel4Macro

Post by Doc.AElstein »

Hello Yasser, ( and everyone ),
What Hans said is probably the thing to do, but I like these closed workbook and ExecuteExcel4Macro things, so, if I may, I have a few comments. .. :)

_ It seems like a few worksheet functions work in that ExecuteExcel4Macro( ) thing, but I can’t find any documentation on it. There are lots of list of the Excel 4.0 Macro commands but they don’t seem to include all the worksheet functions that work in it. Shame, I think there is a lot of hidden potential there..

_ Going back to the macro here , that is to say the one modified for rosy’s correction …
You might want to consider qualifying all the paths fully, just to help keep track of where and what’s going on. Something like

Code: Select all

 Sub Test() '
Dim myDir As String, s As String, t As String, You As String, fn As String
 Let myDir = ThisWorkbook.Path & "\": Debug.Print myDir   '  this macro is in an xlsm workbook                                                                     C:\Users\Elston\Desktop\
    With ThisWorkbook.Worksheets.Item(1)
     Let fn = Dir(myDir & "\" & .Range("A10").Value & ".xlsx") ' A10 Has The Value "Sample"  and  B10 Has The Value "1"
        If fn <> "" Then
         Let s = "'" & myDir & "[" & fn & "]" & "Sheet1" & "'!" & Range("B2:B12").Address(True, True, -4150): Debug.Print s    '                                  'C:\Users\Elston\Desktop\[Sample.xlsx]Sheet1'!R2C2:R12C2
         Let t = "'" & myDir & "[" & fn & "]" & "Sheet1" & "'!" & Range("C2:C12").Address(True, True, -4150): Debug.Print t   '                                   'C:\Users\Elston\Desktop\[Sample.xlsx]Sheet1'!R2C3:R12C3
         Let You = "'" & myDir & "[" & ThisWorkbook.Name & "]" & "Sheet1" & "'!" & Range("B10").Address(True, True, -4150): Debug.Print You '                     'C:\Users\Elston\Desktop\[An xlsm workbook.xlsm]Sheet1'!R10C2
         Let You = "'" & ThisWorkbook.Path & "\" & "[" & ThisWorkbook.Name & "]" & "Sheet1" & "'!" & Range("B10").Address(True, True, -4150): Debug.Print You '   'C:\Users\Elston\Desktop\[An xlsm workbook.xlsm]Sheet1'!R10C2
         Let .Range("C10").Value = Application.ExecuteExcel4Macro("SUMPRODUCT((" & s & "=" & You & ")*(" & t & "))")
        End If
    End With
End Sub
_.____________________________________________
-

_ This following macro is a similar macro to what Hans already gave to give the name of a worksheet in a closed workbook. It works well for me in Excels 2003-2013. I don’t know how it performs in newer versions. In this example I run it on a new workbook of mine and Yassers sample file with a second sheet added.
( For Yassers file the sheets are listed in reverse order. I am not sure why that is. :scratch: - His workbook looks back to front, as they often do, so I expect that has something to do with it ! )

Code: Select all

 Sub WorksheetNameFromClosedWorkbook()
' Example  Yassers Workbook  "Sample.xlsx"
Dim FullFilePathAndName As String: Let FullFilePathAndName = ThisWorkbook.Path & "\" & "Sample.xlsx"
Dim oRST As Object: Set oRST = CreateObject("ADODB.Recordset"): Dim oConn As Object
Dim sConnString As String: Let sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FullFilePathAndName & ";Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1"";" 'Shoul work for .xls .xlsx and .xlsm Files
 Set oConn = CreateObject("ADODB.Connection"): oConn.Open sConnString: Set oRST = oConn.OpenSchema(20) '20 =adSchemaTables
 oRST.MoveFirst
    Do Until oRST.EOF: Dim Cnt As Long: Let Cnt = Cnt + 1  'Loop through Table names
        If Right(Replace(oRST("TABLE_NAME"), "'", ""), 1) = "$" Then 'All worksheet names end in $ (after the single quotes denoting a field have been removed)
        Dim ShtName As String
         Let ShtName = Left(oRST("TABLE_NAME"), Len(oRST("TABLE_NAME")) - 1) 'Exclude $ at the end of the tab name
         Debug.Print ShtName
        End If
         oRST.MoveNext
    Loop
' Example  New book of mine       "New book of mine.xlsx"
Let FullFilePathAndName = ThisWorkbook.Path & "\" & "New book of mine.xlsx"
 Let sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FullFilePathAndName & ";Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1"";" 'Shoul work for .xls .xlsx and .xlsm Files
 Set oConn = CreateObject("ADODB.Connection"): oConn.Open sConnString: Set oRST = oConn.OpenSchema(20) '20 =adSchemaTables
 oRST.MoveFirst
    Do Until oRST.EOF: Let Cnt = Cnt + 1  'Loop through Table names
        If Right(Replace(oRST("TABLE_NAME"), "'", ""), 1) = "$" Then 'All worksheet names end in $ (after the single quotes denoting a field have been removed)
         Let ShtName = Left(oRST("TABLE_NAME"), Len(oRST("TABLE_NAME")) - 1) 'Exclude $ at the end of the tab name
         Debug.Print ShtName
        End If
         oRST.MoveNext
    Loop
End Sub


Refs
http://www.mrexcel.com/forum/excel-ques ... ost3637224
https://www.excelforum.com/tips-and-tut ... ost4630185
http://www.mrexcel.com/forum/excel-ques ... post216171
https://www.myonlinetraininghub.com/exc ... umentation
https://www.myonlinetraininghub.com/exc ... ment-83471


_._________________________________________________

One worksheet function that works in the ExecuteExcel4Macro( ) thing is the Match
You might consider some application of that to get at a last data row. I can’t think how to do that directly as Match doesn’t seem to like looking for an empty cell.
Maybe a couple of workarounds to that…_

_... in next post
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 16 Sep 2021, 12:50, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Using SUMIF with ExecuteExcel4Macro

Post by Doc.AElstein »

_...If you could re arrange your data so that you had either 0’s or some other character in your empty cells, then you could use match in the ExecuteExcel4Macro( ) thing for finding the last used row in a closed workbook.
Example in your sample file, Sample1.xlsx , if you modified your data in column C to look like this
Header2
70
78
22
55
70
16
73
70
87
26
52
0
-
Sample1 with 0 or - added.JPG
With that data this macro will work on that the closed Sample1.xlsx

Code: Select all

 ' https://eileenslounge.com/viewtopic.php?p=271415#p271415
' https://www.excelforum.com/excel-programming-vba-macros/1321581-put-data-in-series-by-vba.html#post5363303
' http://www.eileenslounge.com/viewtopic.php?p=287700#p287700
'  https://www.myonlinetraininghub.com/excel-4-macro-functions#comment-83471

Sub LastRow()
Dim r As Range, Ex, BK As String, myVal
 Let BK = "'" & ThisWorkbook.Path & "\[Sample.xlsx]Sheet1'!"
 Let myVal = 0
    ' If Not IsNumeric(myVal) Then myVal = Chr(34) & myVal & Chr(34)
 Let Ex = ExecuteExcel4Macro("match(" & myVal & "," & BK & "R2C3:R1234C3,0)"): Debug.Print Ex '   12
 Let myVal = "-"
    If Not IsNumeric(myVal) Then myVal = Chr(34) & myVal & Chr(34)
 Let Ex = ExecuteExcel4Macro("match(" & myVal & "," & BK & "R2C3:R1234C3,0)"): Debug.Print Ex   '  13
End Sub
_._________________________________________________




A final alternative to do all you want to do is to just use the single relative reference closed workbook reference stuff we did a lot before, to get the range from the closed workbook into a temporary range in an open workbook, for example:
http://www.eileenslounge.com/viewtopic.php?f=30&t=31150
http://www.eileenslounge.com/viewtopic. ... 16#p221503

In other words just bring in your sample range in one go with a single closed relative range reference into a spare range in your open workbook, and work on that.

This following example is applied to your original closed workbook ranges, ( and I assume that you don’t have any data of value exactly 0 – On that assumption we can take in an extended row range, convert all the 0’s to empty in one go. Doing that you don’t need to concern yourself with knowing the last row, as long as you know what the largest used row might be )

Code: Select all

 Sub ClsdWkbRelRef() ' This macro in an open workbook.   Sample.xlsx  is saved in the same place as this workbook and is closed when this macro is run. ( It remains closed. its not necersary to open it. if you do open it, then the macro will still work )
Dim RngTemp As Range: Set RngTemp = ThisWorkbook.Worksheets.Item(1).Range("S2:R16") ' Any temporary range
' Let RngTemp.Value = "='C:\Users\Elston\Desktop\[Sample.xlsx]Sheet1'!B2"
 Let RngTemp.Value = "='" & ThisWorkbook.Path & "\[Sample.xlsx]Sheet1'!B2" ' The same relative ( vector ) referrence is put in all cells.
 Let RngTemp.Value = RngTemp.Value ' Optional
 Let RngTemp.Value = Evaluate("=IF(" & RngTemp.Address & "=0," & """""" & "," & RngTemp.Address & ")") ' Optional to replace 0 with empty string
End Sub
This last way of doing it I use a lot. I sometimes find it quicker than some more advanced ways. It works well even for very large ranges for me. I expect for mega huge ranges you might be better with the more advanced stuff, like ADO and co.

Alan
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 17 Sep 2021, 05:52, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

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

Re: Using SUMIF with ExecuteExcel4Macro

Post by YasserKhalil »

Thank you very much, Mr. Alan for your great contribution

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

Re: Using SUMIF with ExecuteExcel4Macro

Post by rory »

To the best of my knowledge, ADO simply returns the sheet names in alphabetical order, so isn't much use in getting a particular sheet. It's far simpler to just open the workbook.
Regards,
Rory

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Using SUMIF with ExecuteExcel4Macro

Post by Doc.AElstein »

rory wrote:
17 Sep 2021, 08:15
..., ADO simply returns the sheet names in alphabetical ordering a particular sheet.....
I am glad you mentioned that, Mr Rory, as I had always assumed the ADO listed them in the Excel Item/ tab order, and very stupidly did not check. So I probably passed some wrong info onto some people, because of that. ( I suppose I was usually coincidently often using the default worksheet names , so the Item order and alphanumerical order thing would have been usuially the same in that situation. )
I checked , like in the demo macro and files below.

Example:- , a workbook looking something like this …._
SomeSheetsInWorkbook.JPG
Sheet4 Sheet1 Shee1 Sheet MackRow Sht S1 S12345 S123456789 S1eetmyShtMF S 42 S0 SO
_..... gives results like this ( In the Immediate window - Ctrl+g from the vb Editor to get that window , ( Alt+F11 to get the vb Editor ) )

Code: Select all

 ADO=========
'42$
S
S0
S1
S12345
S123456789
S1eetmyShtMF
Shee1
Sheet
Sheet1
Sheet4
Sht
SO

ADOX========
S$
S0$
S1$
S12345$
S123456789$
S1eetmyShtMF$
Shee1$
Sheet$
Sheet1$
Sheet4$
Sht$
SO$
'42$'

Worksheets==
Sheet4
Sheet1
Shee1
Sheet
Sht
S1
S12345
S123456789
S1eetmyShtMF
S
42
S0
SO

Sheets======
Sheet4
Sheet1
Shee1
Sheet
MackRow
Sht
S1
S12345
S123456789
S1eetmyShtMF
S
42
S0
SO

DataInExcel4macroSheet
Hans ADOX and my ADO give slightly different results, but both give some sort of Alphanumerical order, and not the sheet item order, as I had stupidly assumed.

( I also added a Excel4 macro sheet just out of interest ( via Ctrl+F11 or Insert from a tab thing ). The ADO and ADOX doesn’t see that, I am not sure if I was expecting that result. - I thought it could go both ways, - on the one hand its not a worksheet, but on the other hand I can use it to store data and get data from it , even with a closed reference formula like I did in Rem 4 of the demo macro.
Interesting result, - I suppose this could be useful if you want to prevent people using ADO to get at your workbook sheet data, but still let the data handle normally in/ with Excel and Excel VBA. I am not sure why anyone would want to do that, but there might be some devious reason for doing it to someone, for fun… :evilgrin: )
(I suppose we should say that ADO and ADOX gets worksheet names, and not sheet names, to say it right. Or maybe it gets other sheets , I am not sure, I don’t have experience with any of the other sheet stuff in Excel. Or probably it's better to say it gets Table names, and Excel worksheets may look sometimes like Tables, by design, or whatever, - I am not really clued up on these things... )

Alan

_._____

Demo macro

Code: Select all

 Sub OrderOfTablesWorksheetsSheets()  '   http://www.eileenslounge.com/viewtopic.php?f=30&t=37123
Dim FullFilePathAndName As String: Let FullFilePathAndName = ThisWorkbook.Path & "\" & "Pfaarrrp.xls"
Rem 1 Table stuff
' 1a) ADO
Debug.Print "ADO========="
Dim oRST As Object: Set oRST = CreateObject("ADODB.Recordset"): Dim oConn As Object
Dim sConnString As String: Let sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FullFilePathAndName & ";Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1"";" 'Shoul work for .xls .xlsx and .xlsm Files
 Set oConn = CreateObject("ADODB.Connection"): oConn.Open sConnString: Set oRST = oConn.OpenSchema(20) '20 =adSchemaTables
 oRST.MoveFirst
    Do Until oRST.EOF: Dim Cnt As Long: Let Cnt = Cnt + 1  'Loop through Table names
        If Right(Replace(oRST("TABLE_NAME"), "'", ""), 1) = "$" Then 'All worksheet names end in $ (after the single quotes denoting a field have been removed)
        Dim ShtName As String
         Let ShtName = Left(oRST("TABLE_NAME"), Len(oRST("TABLE_NAME")) - 1) 'Exclude $ at the end of the tab name
         Debug.Print ShtName
        End If
         oRST.MoveNext
    Loop
Debug.Print
 Set oRST = Nothing: Set oConn = Nothing ' In this macro you must do this, or else the  .Open  line below will open the workbook in read only mode, whiuch kind of messes the demo up a bit
' 1b) ADOX
Debug.Print "ADOX========"
Dim Cnn As Object, Cat As Object, Tbl As Object ' ADODB.Connection , ' ADOX.Catalog , ' ADOX.Table

 Set Cnn = CreateObject("ADODB.Connection")
 Cnn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" & "Initial Catalog=" & FullFilePathAndName
 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
Debug.Print
Debug.Print "Worksheets=="
Rem 2 Worksheets
Workbooks.Open Filename:=ThisWorkbook.Path & Application.PathSeparator & "Pfaarrrp.xls", ReadOnly:=False
    For Cnt = 1 To Workbooks("Pfaarrrp.xls").Worksheets.Count
     Debug.Print Workbooks("Pfaarrrp.xls").Worksheets.Item(Cnt).Name
    Next
Debug.Print
Rem 3 Sheets
Debug.Print "Sheets======"
    For Cnt = 1 To Workbooks("Pfaarrrp.xls").Sheets.Count
     Debug.Print Workbooks("Pfaarrrp.xls").Sheets.Item(Cnt).Name
    Next
Debug.Print

Rem 4 Data in MackRow sheet
 Workbooks("Pfaarrrp.xls").Sheets("MackRow").Activate
'4a) Put data in a Excel4 Macro Sheet
 Let Workbooks("Pfaarrrp.xls").Sheets("MackRow").Range("A1").Value = "DataInExcel4macroSheet"
'4b)(i) get data in VBA coding from a Excel4 Macro Sheet
 Debug.Print Workbooks("Pfaarrrp.xls").Sheets("MackRow").Range("A1").Value
'4b(ii) use a closed workbook referrence to get at the data in a Excel4 Macro Sheet
 Workbooks("Pfaarrrp.xls").Save: Workbooks("Pfaarrrp.xls").Close
 Let ActiveSheet.Range("A1").Value = "='" & Thisworkbook.Path & "\[Pfaarrrp.xls]MackRow'!$A$1"  '      "='F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\MsQueeryADO\[Pfaarrrp.xls]MackRow'!$A$1"
End Sub
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also