Hi All Genius,
I need your help if someone have little time for me.
What the problem is i am trying to export the document to excel at particualr drive. it is working fine. Now what i want let say i am exporting document with scheduled agent. what currently is happeing if my agent run and it find already the with the same name it prompt me "A file name ‘C:\destination.xls’ already exist in this location. Do you want to replace it’ with options ‘Yes’, ’ No’, and ‘Cancel’
Now what i want is if same name file found then it should automatically append the document after the last record with prompting. else if file not found it will create new one with the given name.
Currently is happening if there is not any file with the name ‘destination.xls’ at C drive then it will create. But problem is with if file found at that location with the name ‘destination.xls’ it prompt confirmation which i don’t want.
I will be very thankful to you if someone help me. Its an urgent plese help me.
The agent what i wrote is here
'This is Agent code here i use scriptlibrary to perform this poeration which i am writting after this subroutine…
Sub Initialize
'Dim ws As New NotesUIWorkspace
Dim session As New NotesSession
Dim form As NotesForm
Dim db As NotesDatabase
Dim doc,doc1 As NotesDocument
Set db = session.CurrentDatabase
Dim view As notesview
Dim excelfilepath As String
Dim Sheet As Variant
Dim OffsetX As Integer
Dim OffsetY As Integer
Dim isWithHeader As Boolean
Dim includeIcons As Boolean
Dim includeColors As Boolean
Dim includeHidden As Boolean
Msgbox "Agent Start1"
Sheet = 1
OffsetX = 0
OffsetY = 0
isWithHeader = True
includeIcons = True
includeColors = True
includeHidden = True
excelfilepath = "" ' create an empty excel file
Set view = db.GetView("PendingtoExport") 'ws.CurrentView.View
Msgbox "Agent Start2"
Dim report As Variant
'Set report = New ExcelReport(excelfilepath, False)
Set report = New ExcelReport(excelfilepath, False)
Msgbox "Agent Start2"
Call report.exportNotesView(view, Sheet, OffsetX, OffsetY, isWithheader, includeIcons, includeColors, includeHidden)
Dim pathView As NotesView
Dim pathDoc As NotesDocument
Set pathView = db.GetView("pathSettings")
Set pathDoc = pathView.GetFirstDocument()
If pathDoc Is Nothing Then
Msgbox "Please Create PathSettings document First"
Exit Sub
Else
DestFileName = pathDoc.DestPath(0)
Call report.saveas(DestFileName)
Call report.quit
End If
Msgbox "Agent Start3`"
'Call report.setVisibility(True)
End Sub
‘’ ScriptLibrary’‘’
%REM ##############################################################################################
Sub new (xlFilename As String, isVisible As Boolean)
Function save ()
Function saveAs (filename as String)
Function quit ()
Function setCell ( Sheet As Variant , row As Integer , column As Variant , value As String )
Function getCell ( Sheet As Variant , row As Integer , column As Variant ) As String
Function setVisibility (isVisible As Boolean)
Function setCellColor ( Sheet As Variant , row As Integer , column As Variant, innercolor As Variant )
Function setCellFont ( Sheet As Variant , row As Integer , column As Variant, style As Variant, size As Variant, color As Variant )
Function getVersion () As String
Function exportNotesView (view As NotesView, Sheet As Variant, OffsetRow As Integer, OffsetCol As Integer,
isWithHeader as Boolean, includeIcons As Boolean, includeColors As Boolean, includeHidden As Boolean)
'-----------------------------------------------------------------
Example Code inside an application database:
Const EXCELPATH = "C:\temp\TestExcel.xls"
Dim session As New NotesSession
Dim db As NotesDatabase
Dim report As ExcelReport
Set db = session.CurrentDatabase
Set report = new ExcelReport (EXCELPATH, false) ' false = don't show excel
Sheet = 1
Row = 1
Col = 1
Messagebox report.getversion()
Call report.setCell(Sheet,Row, Col, "Hello world")
call report.setVisibility(true)
%END REM ##############################################################################################
'-------------------------------------------------------------
’ General
'-------------------------------------------------------------
Const EXCEL_APPLICATION = “Excel.application”
Const EXCELPATH = “C:\TestExcel.xls”
'-------------------------------------------------------------
’ Errors
'-------------------------------------------------------------
Private Const BASEERROR = 1200
'Private Const ERROR_NOSUCHCELL = BASEERROR + 0
'Private Const ERRORTEXT_NOSUCHCELL = “Excel Report - Could not get data from cell.”
'-------------------------------------------------------------
’ Version Information
'-------------------------------------------------------------
Const REG_97 = “Software\Microsoft\Office\8.0\Common\InstallRoot” 'Registry Key Office 97
Const REG_2000 = “Software\Microsoft\Office\9.0\Common\InstallRoot” 'Registry Key Office 2000
Const REG_XP = “Software\Microsoft\Office\10.0\Common\InstallRoot” 'Registry Key Office XP
Const REG_2003 =“Software\Microsoft\Office\11.0\Common\InstallRoot” 'Registry Key Office 2003
Const NAME_97 = “Office 97”
Const NAME_2000 = “Office 2000”
Const NAME_XP = “Office XP”
Const NAME_2003 = “Office 2003”
'==================================================================================================
’ Excel Report
'==================================================================================================
Class ExcelReport
Private xlApp As Variant ' Application object
Private strFilePath As String
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ constructor
’ - creates the excel application object
’ - can use a file (.xls or .xlt) as template
’ - application can be set to visible/invisible
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub new(xlFilename As String, isVisible As Boolean)
On Error Goto GeneralError
Msgbox "helo1"
Set xlApp = CreateObject(EXCEL_APPLICATION) ' open the application
xlApp.Workbooks.Add xlFilename ' create an Excel workbook
xlApp.Visible = isVisible ' make it visible (or not)
strFilePath = xlFilename ' store the filename
Msgbox "helo2"
Goto ExitSub
GeneralError:
If Not (xlApp Is Nothing) Then xlApp.quit ' quit, if there is an error
Resume ExitSub
ExitSub:
End Sub
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ destructor
’ - is invoked when you delete the object via e.g. “delete report”
’ - see “delete” keyword in help
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub Delete
'If Not (xlApp Is Nothing) Then xlApp.quit
End Sub
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ save file
’ - file gets saved at the position where it was created from
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function save
xlApp.ActiveWorkbook.SaveAs( strFilePath )
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ save file at a designated location
’ - file gets saved at the designated location
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function saveAs(newFilename)
xlApp.ActiveWorkbook.SaveAs( newFileName )
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ set value in a cell
’ - writes a value in the designated cell
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function setCell( Sheet As Variant , row As Integer , column As Variant , value As Variant )
Dim var
xlApp.Workbooks(1).Worksheets( Sheet ).Cells( row , column ).Value = value
var = xlApp.Workbooks(1).Worksheets( Sheet ).Cells( 2 , column ).Value
var=Trim(var)
If var="Score" Or var="TLScore" Or var="QAScore" Or var="FCR4" Or var="NA" Or var="QA" Or var="Attendance" Then xlApp.Workbooks(1).Worksheets( Sheet ).Cells( row , column ).NumberFormat = "0.00%"
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ get value from a cell
’ - returns the cell value
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function getCell( Sheet As Variant , row As Integer , column As Variant ) As String
On Error Goto GeneralError
getCell = xlApp.Workbooks(1).Worksheets( Sheet ).Cells( row , column ).Value
Goto ExitSub
GeneralError:
getCell = ""
Resume ExitSub
ExitSub:
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ quit excel
’ ’ quits excel, if it is still rnning
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function quit
If Not (xlApp Is Nothing) Then
xlApp.Quit
Set xlApp = Nothing
End If
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ set visibility
’ - switches between visible and invisible
’ - does this if it makes sense only
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function setVisibility(isVisible As Boolean)
If (isVisible And Not xlApp.Visible) Then xlApp.Visible = True
If (Not isVisible And xlApp.Visible) Then xlApp.Visible = False
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ set cell color
’ - sets the background color of the cell
’ - see Excel help for possible values
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function setCellColor(Sheet As Variant, row As Integer, column As Variant, innercolor As Variant)
On Error Goto GeneralError
If Cstr(innercolor) <> "" Then
xlApp.Workbooks(1).Worksheets( Sheet ).Cells( row , column ).Interior.ColorIndex = innercolor
End If
Goto ExitSub
GeneralError:
Resume ExitSub
ExitSub:
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ set cell font
’ - sets the font style/size/color of a cell
’ - can be done even when there already is a value in it
’ - see Excel help for values
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function setCellFont(Sheet As Variant, row As Integer, column As Variant, style As Variant, size As Variant, color As Variant)
On Error Goto GeneralError
If Cstr(style) <> "" Then
xlApp.Workbooks(1).Worksheets( Sheet ).Cells( row , column ).Font.FontStyle = style
End If
If Cstr(size) <> "" Then
xlApp.Workbooks(1).Worksheets( Sheet ).Cells( row , column ).Font.Size = size
End If
If Cstr(color) <> "" Then
xlApp.Workbooks(1).Worksheets( Sheet ).Cells( row , column ).Font.ColorIndex = color
End If
Goto ExitSub
GeneralError:
Resume ExitSub
ExitSub:
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ get version
’ - reads the currently installed Excel version from the registry (Windows only)
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function getVersion() As String
On Error Goto GeneralError
Dim formula As String
Dim SWVersion As String
Dim Versions List As String
Dim v As Variant
'----------------------------------------------------------------------
' Initialize all possible versions
'----------------------------------------------------------------------
Versions(NAME_97) = REG_97
Versions(NAME_2000) = REG_2000
Versions(NAME_XP) = REG_XP
Versions(NAME_2003) = REG_2003
'----------------------------------------------------------------------
' test for installed version
'----------------------------------------------------------------------
Forall vers In Versions
formula$ = | (@RegQueryValue("HKEY_LOCAL_MACHINE"; "| & vers & |";"Path")) |
v = Evaluate( formula$ )
If v(0) <> "" Then
getVersion = Listtag(vers)
Goto ExitSub
End If
End Forall
getVersion = ""
Goto ExitSub
GeneralError:
getVersion = ""
Resume ExitSub
ExitSub:
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
’ export a complete NotesView
’ - you can choose to export headers, columns with icons and hidden columns
’ - offset is used to changes the upper left beginning cell
’ - there is no possibility yet to disable color columns (parameter is for future use)
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function exportNotesView(view As NotesView, Sheet As Variant, OffsetRow As Integer, OffsetCol As Integer, isWithheader As Boolean, includeIcons As Boolean, includeColors As Boolean, includeHidden As Boolean)
Dim viewnav As NotesViewNavigator
Dim entry As NotesViewEntry
Dim viewcolumns As Variant
Dim column As Integer
Dim row As Integer
Dim mainDoc As NotesDocument
Set viewnav = view.CreateViewNav()
Set entry = viewnav.GetFirstDocument()
viewcolumns = view.Columns
row = OffsetRow + 1
column = OffsetCol + 1
'---------------------------------------------------------------
' export header
'---------------------------------------------------------------
If isWithHeader Then
Forall vc In viewcolumns
Call Me.setCell(Sheet, row, column, vc.title)
column = column + 1
End Forall
End If
'---------------------------------------------------------------
' export rows
'---------------------------------------------------------------
While Not (entry Is Nothing)
row = row + 1
column = OffsetCol + 1
Forall cv In entry.ColumnValues
If doColumnExport(viewcolumns(column - OffsetCol - 1), includeHidden, IncludeIcons, includeColors) Then
Call Me.setCell(Sheet, row, column, Cstr(cv))
End If
column = column + 1
End Forall
Set mainDoc = entry.Document
mainDoc.isExported = "1"
Call maindoc.Save(False,False)
Set entry = viewnav.GetNextDocument(entry)
Wend
End Function
Private Function doColumnExport (viewcol As NotesViewColumn, includeHidden As Boolean, IncludeIcons As Boolean, includeColors As Boolean) As Boolean
Dim isHiddenOK As Boolean
Dim isIconOK As Boolean
Dim isColorOK As Boolean
isHiddenOK = (viewcol.isHidden And IncludeHidden) Or Not viewcol.isHidden
isIconOK = (viewcol.isIcon And IncludeIcons) Or Not (viewcol.isIcon)
isColorOK = True
doColumnExport = isHiddenOK And isIconOK And isColorOK
End Function
End Class
With Regards,
Vikas K Sinha