Printable View
Hello Folk, How are you All? I'm generating excel report, what I'm looking for is to let field B5 put total number for MSISDN inserted, for example: if I have 15 MSISDN in report, so field B5 type 15? I wish I'm explain enough. What is proper vbcode to do counting? thank you All.
Any chance to get this issue resolved? Thank you all
you can use a formula
=counta(b10:b99)change 99 to desired range
Thank you for your reply friend, I did used this formula before in Excel sheet =acounnt(B10:B300) , when i generate report it show 0 total
I'm looking to vb formula and idid used below code but it's not working:
xlSheet1.Range("B5").Value = Count("b10:b99")
Is there a powerful code to do counting? Thank you
Edited: I used above formula but no chance
What is it you are trying to count? I don't use Excel but there are several ways to come up with a count depending on what you are counting and the source of the data.
For example you may be able to use Count() in your SQL statement, or check the number of rows in your sheet or keep a running total as you are adding items to the sheet or whatever.
In general Excel is a very poor choice for reports.
I would use a report engine instead.
Hello dear brother, I know it's very poor but i can do nothing they want it like it was, any way, dear I just want to account how many numbers insert to excel report as attachment shows above. Thank you for your time.
Well like I said depending on how you are getting the data to the sheet you may be able to use the SQl Count() function or you may be able to keep a running total as you add the items. I am sure there is a way to count the rows in an excel sheet as well as a way to insert a formula but I am not up on coding for Excel.
If a customer asked me to do this I would use my report tool and export the data to Excel format. If they insisted on doing the coding in Excel then I would add $1000.00 to the price for the aggravation of and extra time envolved in having to work with Excel ;)
Seems like you have already put enough time into this project that it would have justified buying a report tool that could create an Excel format.
Well thank you my Brother, Really i enjoy reading your post. Feel happy when you share me your knowledge and i honored by you, Doogle, Mark, Westconn and others and i like to be helped by. one day i will be in position that allow me to assist people and i will wait that day with patience. for your record my friend i doing this project with 0 charge?:( ,I will keep reading and study. God bless you and all.
I did use SQL but when i generate excel report i give me 0 total while i have in MSISDN field 13 number.
Dim intCount As String
strSQL = "SELECT Count(number) as intCount FROM TbStore"
strSQL = strSQL & " AND strDate between " & "#" & S_Date & "#" & " and " & "#" & E_Date & "#"
Set rs2 = New ADODB.Recordset
rs2.Open strSQL, cnn, adOpenStatic, adLockOptimistic
lngnumCount = rs2![intCount]
To me that would indicate a problem with your selection criteria. e.g. values in the where clause causing no records to be counted.
You also should not need to do a seperate select to get the count, rather you should be able to get the count when you grab the data. If number is a unique field then you do not even need to use Count() you could simply check the RS.RecordCount when you retrive the data it should be the same value as a count of a unique field since all records will have a different number.
Edit:
I just noticed that your SQL String is invalid
You do not have a where clause in thereCode:strSQL = "SELECT Count(number) as intCount FROM TbStore"
strSQL = strSQL & " AND strDate between " & "#" & S_Date & "#" & " and " & "#" & E_Date & "#"
Thank you for your time and your pretty answer, I did used it and get count nothing, Code as below:
strSQL = "SELECT Count(number) as intCount FROM TbStore WHERE number = '" & rs1![number] & "' "
That would at most return a count of 1 if the number exists in the system or 0 if not.
You're not showing enough code to really know what your problem is here and you appear to be typing rather than pasting the code as the previous post would have likely blew up with a syntax error if you tried to run it.
What is the query you are using to get the data that goes into the sheet and how are you putting the data in the sheet? This is most likely where you need to add code rather than creating a seperate SQL query just for the count.
Thank you Brother:blush:, Here is code I'm using:
Code:Private Sub FlexToExcel()
Dim cnn As New ADODB.Connection
Dim xlApp As New Excel.Application
Dim xlwk As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim strSQL As String
Dim lngnumCount As Long
Dim intCount As String
Dim ctr As Long
Dim ctr1 As Long
'Dim i As Integer
'Dim strSeriesCell As String
strDate = Format(Date, "mm/dd/yyyy")
cnn.ConnectionTimeout = 15
cnn.CommandTimeout = 30
g_strDBName = dbPath & "\BarqCell Data\DSS.mdb;Jet " & "OLEDB:Database Password=brss"
'creates a new databse conection and sets the conection string
Set cnn = New ADODB.Connection
Constring = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & g_strDBName
cnn.ConnectionString = Constring
'Opens the database conection
cnn.Open
xlApp.Interactive = True
Set xlwk = xlApp.Workbooks.Open(App.path & "\midmonth.xls")
Set xlSheet = xlwk.Worksheets("Sheet3")
xlSheet.Select
xlApp.Range("B5").Value = DTPicker1.Value
xlApp.Range("B6").Value = DTPicker2.Value
S_Date = DTPicker1.Value
E_Date = DTPicker2.Value
strSQL = "SELECT DISTINCT number FROM TbStore WHERE strDate between " & "#" & S_Date & "#" & " and " & "#" & E_Date & "# ORDER BY number"
Set rs1 = New ADODB.Recordset
rs1.Open strSQL, cnn, adOpenStatic, adLockOptimistic
ctr = 12 ' start data after headings
'
strSQL = "SELECT Count(number) as intCount FROM TbStore WHERE number = '" & rs1![number] & "' "
strSQL = strSQL & " AND strDate between " & "#" & S_Date & "#" & " and " & "#" & E_Date & "#"
Set rs2 = New ADODB.Recordset
rs2.Open strSQL, cnn, adOpenStatic, adLockOptimistic
lngnumCount = rs2![intCount]
xlApp.Range("B13").Value = lngnumCount
Do While Not rs1.EOF
ctr = ctr + 1
xlApp.Range("C" & Trim(Str(ctr))).Value = rs1![number]
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
xlApp.Visible = True
End Sub
Is the field named number a unique field in your database table tbStore? e.g. Does not allow duplicates.
Your second rs where you are trying to get a count is only checking the first number from RS1 so that is not going to work the way you want no matter what.
If you want to know how many numbers are in RS1 then you can either use
After your loop statementCode:Total=ctr-12
or you can use
After the loop statement but before the rs1.close statement.Code:Total=Rs1.RecordCount
The Second SQL and RS2 should not be needed at all.
Very Cool my friend:wave:. Issue Resolved. now i wish i can put this thread on Resolved? Thank you DataMiser, wish you all the best my friend;). God bless you