?
Solved

Export to Excel, and add a cell to 'count if'

Posted on 2011-10-10
18
Medium Priority
?
739 Views
Last Modified: 2013-12-18
I have a working code that exports records to Excel.  I have been asked to add columns that do simple calculations.  The only one I can't get to work is COUNTIF.

SUM, COUNT, and COUNTA all work, but COUNTIF DOESN'T.

For example, the code reads
'This works
xlsheet.Cells(3,2).Value = "=SUM(A1:A13)"
xlsheet.Cells(4,2).Value = "=COUNTA(A14:A20)

'This throws an error
xlsheet.Cells(11,2).Value = "=(countif(c3:c13),"Y")"

the error is:
Unexpected Y: Expected End-Of-statement: Operator

I can't find anything in lotusscript references.  This is the last piece of this project - I'm desparate!  Is this possible?
0
Comment
Question by:jkee54
  • 9
  • 4
  • 4
  • +1
18 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36945853
try this:

xlsheet.Cells(11,2).Value = "=countif(c3:c13;"Y")"
0
 

Author Comment

by:jkee54
ID: 36945973
Still gives the same error.  Seems like it doesn't recognize COUNTIF
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36946012
try this:

xlsheet.Cells(11,2).Value = "=countif(c3:c13;""Y"")"

Davy
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Expert Comment

by:Davy2270
ID: 36946021
or better:

xlsheet.Cells(11,2).Value = "=countif(c3:c13,"Y")"

Just saw that your separator is a comma instead of ;

Davy
0
 

Author Comment

by:jkee54
ID: 36946042
Davy,

It didn't throw that same error, but it does stop the code.  Debugger doesn't show anything, but the client shows an error, "OLE Automation error".
0
 

Author Comment

by:jkee54
ID: 36946059
OOPS I meant it does not stop the code - it ran all the way, but didn't insert the formula in the right cell, and then had the OLE error.  
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36946170
I just saw I messed up my responses a bit, did you use this code?

xlsheet.Cells(11,2).Value = "=countif(c3:c13,""Y"")"

It should put this formula into cell B12, as a test just pointed out.
Maybe you could change Value to Formula.
Or maybe there's something else in your code that is causing the error?
Can you share the rest of the code?
0
 

Author Comment

by:jkee54
ID: 36946385
I tried that last line with my correct range, so it's modified as follows and the code runs with no error:

      xlsheet.Cells(11,2).Value = "=countif(C11:Z11,""Y"")"

The problem is still though, it doesn't do the calculation.  There were Y's in columns E and F, so the cell should have had 2 as the formula result.

I also tried Formula instead of Value, same thing - no error, but no result.

It's not as though it doesn't calculate - the formulas just don't show up in the cell.  The COUNTA formulas don't show either.

I'm attaching the code this time. I will clean up the formatting later - it's quite long but I hope you can help.
Sub Click(Source As Button)
	
'Export the selected documents from view to Excel spreedsheet
	
	Dim session As New notessession
	Dim db As notesdatabase
	Dim doc As notesdocument
	Dim xlApp As Variant
	Dim xlsheet As Variant
	Dim xlrange As Variant
	Dim xlobj As Variant
	Dim filename As String
	Dim DC As NotesDocumentCollection
	
	Set db=session.currentdatabase
	Set dc=db.unprocesseddocuments
	Print "Collection size: " &dc.Count
	
	Set xlApp=CreateObject("Excel.application")
	xlApp.visible=True
	xlApp.Workbooks.Add
	Set xlsheet=xlApp.Workbooks(1).Worksheets(1)
	
'Load column and row headers
	
	
' Row titles
'xlsheet.cells(row,colum)
	xlsheet.Cells(2,1).Value = "Underwriter Name"
	xlsheet.Cells(2, 1).font.bold = True
	xlsheet.Cells(3,1).Value = "Policy"
	xlsheet.Cells(3, 1).font.bold = True
	xlsheet.Cells(4,1).Value = "Face"
	xlsheet.Cells(4, 1).font.bold = True
	xlsheet.Cells(5,1).Value = "Age"
	xlsheet.Cells(5, 1).font.bold = True
	xlsheet.Cells(6,1).Value = "Client Last Name"
	xlsheet.Cells(6, 1).font.bold = True
	xlsheet.Cells(7,1).Value = "Site"
	xlsheet.Cells(7, 1).font.bold = True
	xlsheet.Cells(8,1).Value = "Summary of Case"
	xlsheet.Cells(8, 1).font.bold = True
	xlsheet.Cells(9,1).Value = "FINAL DECISION"
	xlsheet.Cells(9, 1).font.bold = True
	xlsheet.Cells(10,1).Value = "  FINAL DECISION APPROPRIATE"
	xlsheet.Cells(10, 1).font.bold = True
	xlsheet.Cells(11,1).Value = "     Appropriately developed and assessed per published guidelines. Good und. Judgement applied to determine overall risk class"	
	xlsheet.Cells(12,1).Value = "     Underwriter utilized all available options for best class "
	xlsheet.Cells(13,1).Value = "     Superior Handling"
	xlsheet.Cells(14,1).Value = "  FINAL DECISION -MINOR"
	xlsheet.Cells(14, 1).font.bold = True
	xlsheet.Cells(15,1).Value = "      Underwriting decision  within 1 risk class per published guidelines"
	xlsheet.Cells(16,1).Value = "  FINAL DECISION - MAJOR"
	xlsheet.Cells(16, 1).font.bold = True
	xlsheet.Cells(17,1).Value = "      Underwriting decision  2 or more risk classes outside published guidelines"
	xlsheet.Cells(18,1).Value = "      Unable to determine if final decision is appropriate due to lack of  development"
	xlsheet.Cells(19,1).Value = "  FINAL DECISION - OPPORTUNITY FOR IMPROVED OFFER"
	xlsheet.Cells(19, 1).font.bold = True
	xlsheet.Cells(20,1).Value = "      There were favorable aspects of the case that could have allowed for a better class than suggested by published guidelines"
	xlsheet.Cells(21,1).Value = "DOCUMENTATION"
	xlsheet.Cells(21, 1).font.bold = True
	xlsheet.Cells(22,1).Value = "  DOCUMENTATION APPROPRIATE"
	xlsheet.Cells(22, 1).font.bold = True
	xlsheet.Cells(23,1).Value = "      Adequately documented to justify overall decision."
	xlsheet.Cells(24,1).Value = "  DOCUMENTATION NEEDS IMPROVEMENT "
	xlsheet.Cells(24, 1).font.bold = True
	xlsheet.Cells(25,1).Value = "      Documentation insufficient to support decision. All pertinent aspects of risk assessment not commented on."
	xlsheet.Cells(26,1).Value = "DEVELOPMENT"	
	xlsheet.Cells(26, 1).font.bold = True
	xlsheet.Cells(27,1).Value = "  DEVELOPMENT MAJOR"
	xlsheet.Cells(27, 1).font.bold = True
	xlsheet.Cells(28,1).Value = "      Oversight is one that is contrary to established practice (without adequate documentation justifying the departure), and could have impacted the final underwriting decision significantly."
	xlsheet.Cells(29,1).Value = "  DEVELOPMENT MINOR"
	xlsheet.Cells(29, 1).font.bold = True
	xlsheet.Cells(30,1).Value = "      Requirements not ordered in a timely fashion ."
	xlsheet.Cells(31,1).Value = "      Oversight is one that is contrary to established practice (without adequate documentation justifying the departure), but most likely would not have impacted the final underwriting decision. ."
	xlsheet.Cells(32,1).Value = "FINANCIAL / SUITABILITY"	
	xlsheet.Cells(32, 1).font.bold = True
	xlsheet.Cells(33,1).Value = "      Product/amt in relationship to age/finances/purpose/need  developed/assessed appropriately."
	xlsheet.Cells(34,1).Value = "      Unadmitted replacement not developed or referred to replacement analyst."
	xlsheet.Cells(35,1).Value = "Amendment Forms"	
	xlsheet.Cells(35, 1).font.bold = True
	xlsheet.Cells(36,1).Value = "      Amendment  appropriately executed ."
	xlsheet.Cells(37,1).Value = "AUD letter"	
	xlsheet.Cells(37, 1).font.bold = True
	xlsheet.Cells(38,1).Value = "      AUD appropriately executed ."
	xlsheet.Cells(39,1).Value = "Reinsurance"	
	xlsheet.Cells(39, 1).font.bold = True
	xlsheet.Cells(40,1).Value = "      Case coded appropriately for reinsurance ceding or retention."	
	xlsheet.Cells(41,1).Value = "      Prior reinsured policy was not referred to Reinsurance Unit for review."
	xlsheet.Cells(42,1).Value = "      Autobound Inappropriately."
	xlsheet.Cells(43,1).Value = "MIB Coding"	
	xlsheet.Cells(43, 1).font.bold = True
	xlsheet.Cells(44,1).Value = "      MIB Coding appropriately executed."
	xlsheet.Cells(45,1).Value = "Misc"	
	xlsheet.Cells(45, 1).font.bold = True
	xlsheet.Cells(46,1).Value = "      Benefits not included or deleted as necessary.  Policy issued and dated incorrectly (COD-forward dating, save age, specific issue date requests), incorrect plan, face, sex, dob, etc.   ."
	xlsheet.Cells(47,1).Value = "      Illustration, certification or computer screen certification not submitted with app (when required)."
	xlsheet.Cells(48,1).Value = "      New or Revised illustration not requested at issue. ."
	xlsheet.Cells(49,1).Value = "      All other issues not mentioned above."
	xlsheet.Cells(1,2).Value = "Counts (if Y)"
	xlsheet.Cells(1, 2).font.bold = True
	
	
	
		'set up counts
	
'Start Import from Notes on this col
	columns%=3
	Set doc=dc.GetFirstDocument
	While Not (doc Is Nothing)
		
'fill cells with values from Notes and sets column widths
		
		xlsheet.Cells(2, columns%).Value=doc.AnalystName(0)
		xlsheet.Cells(3, columns%).Value=doc. CaseNumber(0)
		xlsheet.Cells(4, columns%).Value=doc. FaceAmount(0)
		xlsheet.Cells(5, columns%).Value=doc. Age(0)
		xlsheet.Cells(6, columns%).Value=doc. ClientLastName(0)
		xlsheet.Cells(7, columns%).Value=doc. Site(0)
		xlsheet.Cells(8, columns%).Value=doc. Comment1(0)
		xlsheet.Cells(11, columns%).Value=doc. ChBx1_9(0)
		xlsheet.Cells(12,columns%).Value=doc. Rating_1(0)
		xlsheet.Cells(13,columns%).Value=doc.Comment2(0)
		xlsheet.Cells(15,columns%).Value=doc.ChBx1_1(0)
		xlsheet.Cells(17,columns%).Value=doc.ChBx1_2(0)
		xlsheet.Cells(18,columns%).Value=doc.ChBx1_2_1(0)
		xlsheet.Cells(20,columns%).Value=doc.ChBx1_3(0)
		xlsheet.Cells(23,columns%).Value=doc.ChBx1_4(0)
		xlsheet.Cells(25,columns%).Value=doc.ChBx1_6(0)
		xlsheet.Cells(28,columns%).Value=doc.ChBx1_7(0)
		xlsheet.Cells(30,columns%).Value=doc.ChBx1_8(0)
		xlsheet.Cells(31,columns%).Value=doc.ChBx1_8_1(0)
		xlsheet.Cells(33,columns%).Value=doc.ChBx1_10(0)
		xlsheet.Cells(34,columns%).Value=doc.ChBx1_11(0)
		xlsheet.Cells(36,columns%).Value=doc.ChBx1_13(0)
		xlsheet.Cells(38,columns%).Value=doc.ChBx1_14(0)
		xlsheet.Cells(40,columns%).Value=doc.ChBx1_15(0)
		xlsheet.Cells(41,columns%).Value=doc.ChBx1_16(0)
		xlsheet.Cells(42,columns%).Value=doc.ChBx1_16_1(0)
		xlsheet.Cells(44,columns%).Value=doc.ChBx1_18(0)
		xlsheet.Cells(46,columns%).Value=doc.ChBx1_19(0)
		xlsheet.Cells(47,columns%).Value=doc.ChBx1_20(0)
		xlsheet.Cells(48,columns%).Value=doc.ChBx1_21(0)
		xlsheet.Cells(49,columns%).Value=doc.Comment1_1(0)
		'-----------------------
		
		xlsheet.Cells(11,2).Value = "=countif(C11:Z11,""Y"")"
		
		xlsheet.Cells(13,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(15,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(17,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(18,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(20,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(23,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(25,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(28,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(30,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(31,2).Value = "=counta(C11:Z11)"
		
		xlsheet.Cells(34,2).Value = "=counta(C11:Z11)"
		
		
		
		xlsheet.Cells(41,2).Value = "=counta(C11:Z11)"
		xlsheet.Cells(42,2).Value = "=counta(C11:Z11)"
		'-----------------------
		
		xlSheet.Cells(8,columns%).Wraptext = True
		xlSheet.Cells(8,columns%).Font.Size = 8	
		xlSheet.Cells(8, columns%).Columnwidth = 30
		
		xlSheet.Cells(13,columns%).Wraptext = True
		xlSheet.Cells(13,columns%).Font.Size = 8	
		
		xlSheet.Cells(49,columns%).Wraptext = True
		xlSheet.Cells(49,columns%).Font.Size = 8	
		
		Set doc=dc.GetNextDocument(doc)
		columns%=columns%+1
		
	Wend
	
'FORMATS
	xlSheet.Columns(1).Columnwidth = 40
	xlSheet.Columns(1).Font.Size = 8	
	xlSheet.Columns(2).Columnwidth = 15	
	xlSheet.Columns(2).VerticalAlignment = 3
	
	xlSheet.Columns(1).Wraptext = True     
	xlsheet.Columns(1).HorizontalAlignment = 1
	
	
' Other formats
' Left Justify Analyst name
'	xlsheet.Cells(1,2).HorizontalAlignment = 1
	
' Freeze panes
	xlApp.application.Cells(3,2).Select
	xlApp.application.ActiveWindow.FreezePanes = True
	
 ' Setting first 2 rows as header row to reprint on each page
	xlsheet.PageSetup.PrintTitleRows = "$1:$2"
	
' Set the print to landscape, letter and 85% size
	xlSheet.PageSetup.Orientation = 2
	xlSheet.PageSetup.PaperSize = 1
	xlSheet.PageSetup.Zoom = 85
	
' Headers and Automatic page number setup
	xlSheet.PageSetup.CenterHeader = "Audit Summary"
	xlSheet.PageSetup.CenterFooter = "Page &P of &N"
	xlSheet.PageSetup.LeftFooter = "&D"
	
' Set the Printgridlines	& Page setup
	xlSheet.PageSetup.PrintGridlines = True
	xlSheet.PageSetup.LeftMargin = xlapp.InchesToPoints(0.81)
	xlSheet.PageSetup.RightMargin = xlapp.InchesToPoints(0)
	xlSheet.PageSetup.HeaderMargin = xlapp.InchesToPoints(0.25)
	xlSheet.PageSetup.FooterMargin = xlapp.InchesToPoints(0)
	xlSheet.PageSetup.TopMargin = xlapp.InchesToPoints(0.5)
	xlSheet.PageSetup.BottomMargin = xlapp.InchesToPoints(0)
	
	'Call xlsheet.saveas("C:\Export.xls") 
	'XLApp.ActiveWorkBook.Close
	'XLApp.Quit
	Set xlapp=Nothing
End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36947233
Have you tried this one?
xlsheet.Cells(11,2).Formula= "=COUNTIF(C11:Z11,""Y"")"

This is how it works in Excel. I have no experience with lotus.
So I guesse my help stops about here. I hope you can solve it.

Davy
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 36947236
And this:

xlsheet.Cells(11,2).Value = |=(countif(c3:c13),"Y")|
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 36947240
Explanation:
there are three sets of string delimiters in LotusScript, examples: "string", |string| and {string}
0
 

Author Comment

by:jkee54
ID: 36948204
Thanks - It works inserting the formulas, but when it hits the loop to pull in the documents

'Start Import from Notes on this col
      columns%=3
      Set doc=dc.GetFirstDocument
      While Not (doc Is Nothing)
            
'fill cells with values from Notes and sets column widths
            
            xlsheet.Cells(2, columns%).Value=doc.AnalystName(0)
            xlsheet.Cells(3, columns%).Value=doc. CaseNumber(0)
...etc....


It stops with the error: OLE: Automation object error

Is this error caused by my particular PC settings or is it a problem with the code?
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 2000 total points
ID: 36948268
Can you please add some error trapping code, or run the code using the debugger, so you can better pinpoint the line where execution stops.

For error trapping use:

            On Error Goto oops

And at the end of the code:

      exitsub:
            Exit Sub
      oops:
            Print Error$ " on line " Erl
            Resume exitsub
0
 

Assisted Solution

by:jkee54
jkee54 earned 0 total points
ID: 36948419
Thanks!  It was on the line where I tried the vertical bar

xlsheet.Cells(11,2).Value = |=(countif(C11:Z11),"Y")|

I changed it back to quotes and it works like a charm.  I will finish testing and then come back and award the points (In case something else goes wrong)  - but I have learned a valuable lesson on error handling :)
0
 

Author Comment

by:jkee54
ID: 37060025
I've requested that this question be closed as follows:

Accepted answer: 0 points for jkee54's comment http:/Q_27389708.html#36948419

for the following reason:

Sorry I was late closing this - thanks again!
0
 

Author Comment

by:jkee54
ID: 37060026
sorry - I clicked the wrong Accept button - I meant to accept the previous by sjef.  Please cancel the acceptance of my own comment and award the points to sjef bosman, thanks
0
 

Author Closing Comment

by:jkee54
ID: 37087341
I accepted my own as a mistake - all points to sjef.. thanks!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 37060043
It's very much appreciated :-)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question