Using SUMIF with ExecuteExcel4Macro
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Using SUMIF with ExecuteExcel4Macro
As this approach is faster than any other approach. That's the only reason.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Using SUMIF with ExecuteExcel4Macro
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.
Then manipulate the array any way you want.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Using SUMIF with ExecuteExcel4Macro
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
_.____________________________________________
-
_ 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. - His workbook looks back to front, as they often do, so I expect that has something to do with it ! )
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
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. - 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
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Using SUMIF with ExecuteExcel4Macro
_...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
- With that data this macro will work on that the closed Sample1.xlsx
_._________________________________________________
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 )
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
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
- 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
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
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Using SUMIF with ExecuteExcel4Macro
Thank you very much, Mr. Alan for your great contribution
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Using SUMIF with ExecuteExcel4Macro
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
Rory
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Using SUMIF with ExecuteExcel4Macro
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 …._ 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
( 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… )
(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
You can find me at DocAElstein also