Hello Everyone,
I am having one agent through which data is exporting from two views, After runing this agent I am getting data in two excel-sheet from two views, My requirement is both excel sheet data will come in one excel in diff sheets. Below is my code.....
Function SendExcel(db As NotesDatabase,reportView As NotesView,reportView1 As NotesView,admindoc As NotesDocument,doc As NotesDocument,doc1 As NotesDocument)
On Error Goto errGenSendExcel
Const ColMax = 6
Dim HeadingArr(ColMax) As String
Dim reportexist As Integer
Dim report1exist As Integer
Dim rtitemp As NotesRichTextItem
reportexist = 1
report1exist = 1
Dim Maildoc As NotesDocument
Set Maildoc = db.CreateDocument
Maildoc.Form="Memo"
Maildoc.Principal="Automated Mail , Please donot reply"
If Weekday ( Today) <>1 Then
If reportView.GetFirstDocument Is Nothing Then
reportexist = 0
End If
If reportView1.GetFirstDocument Is Nothing Then
report1exist = 0
End If
If reportexist = 0 And report1exist = 0 Then
Maildoc.Subject= admindoc.subject_ND
Maildoc.SendTo= admindoc.sendto_ND
Dim rtitemND As New NotesRichTextItem(Maildoc,"Body")
Set rtitemp = admindoc.GetFirstItem("mailbody_ND")
Call rtitemND.AppendText(rtitemp.GetUnformattedText())
Call rtitemND.AddNewline(3)
Set rtitemp = admindoc.GetFirstItem("signature")
Call rtitemND.AppendText(rtitemp.GetUnformattedText())
Call Maildoc.Send(False)
Exit Function
End If
'===================================================
' Agent log declaration
Set AgentLog = New NotesLog( "InvoiceLine Count" )
Call AgentLog.OpenAgentLog
Call AgentLog.LogAction("Inside Agent Line" )
If reportexist Then
Set oExcel = CreateObject ( "Excel.Application" )
Set oWorkbook = oExcel.Workbooks.Add
Set oWorkSheet = oWorkbook.Sheets ( 1 )
oExcel.visible = False
oExcel.ActiveSheet.Name = "IOS-Lines"
'Setting the values of the excel header - 1st row
For ColInt =1 To ColMax
Select Case ColInt
Case 1:
HeadingArr(ColInt)="Invoiced By"
oExcel.Columns(ColInt).ColumnWidth= 30
Case 2:
HeadingArr(ColInt)="Final Dest Country"
oExcel.Columns(ColInt).ColumnWidth= 30
'oExcel.Columns(ColInt).NumberFormat ="00"
Case 3:
HeadingArr(ColInt)="Purchaser"
oExcel.Columns(ColInt).ColumnWidth= 15
oExcel.Columns(ColInt).NumberFormat ="00000"
Case 4:
HeadingArr(ColInt)="Month Invoiced"
oExcel.Columns(ColInt).ColumnWidth= 20
Case 5:
HeadingArr(ColInt)="Bill To Country"
oExcel.Columns(ColInt).ColumnWidth= 30
'oExcel.Columns(ColInt).NumberFormat="000"
Case 6:
HeadingArr(ColInt)="Number of Invoices"
oExcel.Columns(ColInt).ColumnWidth= 20
End Select
Next
oWorkSheet.Rows(1).Font.Bold = True
For ColInt =1 To Colmax
oExcel.Cells(1,ColInt).Value = HeadingArr(ColInt)
oExcel.Cells(1,ColInt).Borders.LineStyle = xlContinuous
oExcel.Cells(1,ColInt).Borders.ColorIndex = xlAutomatic
oExcel.Cells(1,ColInt).Interior.colorindex = 37
Next
RowInt = 2
While Not(doc Is Nothing)
oExcel.Cells(RowInt, 1).Value = Cstr(doc.ln_prep_by_nm(0))
oExcel.Cells(RowInt, 2).Value = Cstr(doc.ln_ult_dest(0))
oExcel.Cells(RowInt, 3).Value = Cstr(doc.ln_purch_id(0))
oExcel.Cells(RowInt, 4).Value = Cstr(doc.ln_invc_dt(0))
oExcel.Cells(RowInt, 5).Value =Cstr(doc.ln_cntry_cd(0))
oExcel.Cells(RowInt, 6).Value = Cstr(doc.ln_nbrlines(0))
RowInt = RowInt + 1
Set doc = reportView.GetNextDocument(doc)
'Loop Until result.IsEndOfData
Wend
'Reading filename and Excel path for storing the generated excel sheet.
filename = "Line Count Data" + Format(Now, "d mmm yy") +", " + Format(Now, "h mm ss AM/PM")
Set oWorkSheet = Nothing
ExcelPath = "D:\Negative\"+ filename+ ".xls"
oWorkbook.SaveAs ExcelPath
'======================================================================
' Closing all the objects used.
Set oWorkbook = Nothing
oExcel.Quit
Set oExcel = Nothing
Set oWorkSheet= Nothing
End If
'======================================================================
'Creating another axcel
If report1exist Then
SheetCount = SheetCount + 1
Const ColMax1 = 7
Dim HeadingArr1(ColMax1) As String
Set oExcel1 = CreateObject ( "Excel.Application" )
Set oWorkbook1 = oExcel1.Workbooks.Add
Set oWorkSheet1= oWorkbook1.Sheets ( 1 )
oExcel1.visible = False
oExcel1.ActiveSheet.Name = "IOS-Invoices"
'Setting the values of the excel header - 1st row
For ColInt1=1 To ColMax1
Select Case ColInt1
Case 1:
HeadingArr1(ColInt1)="Invoiced By"
oExcel1.Columns(ColInt1).ColumnWidth= 30
Case 2:
HeadingArr1(ColInt1)="Final Dest Country"
oExcel1.Columns(ColInt1).ColumnWidth= 30
Case 3:
HeadingArr1(ColInt1)=" Purchaser"
oExcel1.Columns(ColInt1).ColumnWidth= 15
oExcel1.Columns(ColInt1).NumberFormat ="00000"
Case 4:
HeadingArr1(ColInt1)="Month Invoiced"
oExcel1.Columns(ColInt1).ColumnWidth= 15
Case 5:
HeadingArr1(ColInt1)="Bill To Country"
oExcel1.Columns(ColInt1).ColumnWidth= 30
Case 6:
HeadingArr1(ColInt1)="Amount Invoiced"
oExcel1.Columns(ColInt1).ColumnWidth= 15
Case 7:
HeadingArr1(ColInt1)="Number of Invoices"
oExcel1.Columns(ColInt1).ColumnWidth= 15
End Select
Next
oWorkSheet1.Rows(1).Font.Bold = True
For ColInt1 =1 To Colmax1
oExcel1.Cells(1,ColInt1).Value = HeadingArr1(ColInt1)
oExcel1.Cells(1,ColInt1).Borders.LineStyle = xlContinuous
oExcel1.Cells(1,ColInt1).Borders.ColorIndex = xlAutomatic
oExcel1.Cells(1,ColInt1).Interior.colorindex = 37
Next
RowInt1 = 2
While Not(doc1 Is Nothing)
oExcel1.Cells(RowInt1, 1).Value = Cstr(doc1.prep_by_nm(0))
oExcel1.Cells(RowInt1, 2).Value = Cstr(doc1.ult_dest(0))
oExcel1.Cells(RowInt1, 3).Value = Cstr(doc1.purch_id(0))
oExcel1.Cells(RowInt1, 4).Value = Cstr(doc1.invc_dt(0))
oExcel1.Cells(RowInt1, 5).Value =Cstr(doc1.cntry_cd(0))
oExcel1.Cells(RowInt1, 6).Value = Cstr(doc1.amtinvcd(0))
oExcel1.Cells(RowInt1, 7).Value = Cstr(doc1.nbrinvcs(0))
RowInt1 = RowInt1 + 1
Set doc1 = reportView1.GetNextDocument(doc1)
'Loop Until result.IsEndOfData
Wend
'===================================================================
'Reading filename and Excel path for storing the generated excel sheet.
filename1 = "Invoice Count Data" + Format(Now, "d mmm yy") +", " + Format(Now, "h mm ss AM/PM")
Set oWorkSheet1 = Nothing
ExcelPath1 = "D:\Negative\"+ filename1+ ".xls"
oWorkbook1.SaveAs ExcelPath1
'====================================================================
Set oWorkbook1 = Nothing
oExcel1.Quit
Set oExcel1 = Nothing
Set oWorkSheet1= Nothing
End If
'=========================================================================
Else
Print "Sunday - mail send disable"
Exit Function
End If
'======================================================================
' Creating mail doc and attaching the excel sheet to it.
'Maildoc.Form="Memo"
Maildoc.Subject= admindoc.subject(0)+" "+Cstr(Time)
'Maildoc.Principal="Automated Mail , Please donot reply"
Maildoc.SendTo= admindoc.sendto
Maildoc.CopyTo = admindoc.ccto
Maildoc.BlindCopyTo = admindoc.bccto
Dim rtitem As New NotesRichTextItem(Maildoc,"Body")
Set rtitemp = admindoc.GetFirstItem("mailbody")
Call rtitem.AppendText(rtitemp.GetUnformattedText())
Call rtitem.AddNewline(2)
Call rtitem.AddNewline(2)
If reportexist Then
Call rtitem.EmbedObject(EMBED_ATTACHMENT, "", ExcelPath)
Else
Call rtitem.AppendText("No InvoiceLine data available in Q2CINVDTL")
Call rtitem.AddNewline(3)
End If
If report1exist Then
Call rtitem.EmbedObject(EMBED_ATTACHMENT, "", ExcelPath1)
Else
Call rtitem.AppendText("No InvoiceLine data available in Q2CEXSUB")
End If
Call rtitem.AddNewline(3)
Set rtitemp = admindoc.GetFirstItem("signature")
Call rtitem.AppendText(rtitemp.GetUnformattedText())
Call Maildoc.Send(False)
SendExcel=True
Exit Function
errGenSendExcel:
Print "Inside SendExcel error at line:" & Erl & " -------Error Code: " & Err & " --------Error: " & Error
End Function
Thanks in Advance to everyone
Print this page
