%!    >0@- >-@-_        _      ,Arial-@,T,Nx!@!@!@!@!@!@#P@-,#P,#P`,#P`,#P,#P>-,#P$,#P,# P,# P ,# P,# P ,# P,#P>`,#P ,#P_,#P_,#PY_,#Pb_,#P@-,#P@-,#P! , `P `P*v _Wv22?@  -      !"#$% RS0 CR%# 6Sheet4#6CSV Template Fi'xcfsdfdsaf(P  $z@<<@123>>ISEMPTY(@;;)'@IF(@isempty(~#),1,@if(~#="V",0,2)):'@IF(@isempty(~#),1,@IF(@iscaseof(~#,"N;A;D;O"),0,2))R'@IF(@isempty(~#),1,@IF(@LENGTH(@string(~#,0))>3,2,@if(@isanumeric(~#),0,3)))<'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>10,1,0))W'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>35,1,@IF(@ISALPHANUMERIC(~#),0,2)))V'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>3,1,@IF(@ISALPHANUMERIC(~#),0,2)))W'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>20,1,@IF(@ISALPHANUMERIC(~#),0,2)))'@IF(@isempty(~#),1,0)6 '@IF(@isempty(~#),1,@IF(@iscaseof(~#,"U;F"),0,2)) '@IF(@isempty(~#),1,0)6 '@IF(@isempty(~#),1,@IF(@iscaseof(~#,"U;T"),0,2))W '@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>20,1,@IF(@ISALPHANUMERIC(~#),0,2)))W '@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>15,1,@IF(@ISALPHANUMERIC(~#),0,2)))W'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>15,1,@IF(@ISALPHANUMERIC(~#),0,2)))W'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>15,1,@IF(@ISALPHANUMERIC(~#),0,2)))V'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>5,1,@IF(@ISALPHANUMERIC(~#),0,2)))V'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>3,1,@IF(@ISALPHANUMERIC(~#),0,2)))V'@IF(@isempty(~#),0,@IF(@LENGTH(@string(~#,0))>5,1,@IF(@ISALPHANUMERIC(~#),0,2)))/'@IF(@isempty(~#),1,@if(@ISADATE(~#),0,2))<'@IF(@isempty(~#),1,@IF(@ISADATE(~#),@IF(~#2,1,@IF(@ISALPHANUMERIC(~#),0,2)))''@if(@IsEmpty(~#),0,@If(0>~#,1,0))('@If(Z#>=1,@If(@IsEmpty(~#),1,0),0)('@If(Z#>=2,@If(@IsEmpty(~#),1,0),0)('@If(Z#>=3,@If(@IsEmpty(~#),1,0),0)( '@If(Z#>=4,@If(@IsEmpty(~#),1,0),0)("'@If(Z#>=5,@If(@IsEmpty(~#),1,0),0)($'@If(Z#>=6,@If(@isempty(~#),1,0),0)5'This is a mandatory field and must be completed5'This is a mandatory field and must be completed5'This is a mandatory field and must be completed#'Must be 10 characters or less#'Must be 35 characters or less"'Must be 3 characters or less#'Must be 20 characters or less5'This is a mandatory field and must be completed5 'This is a mandatory field and must be completed5 'This is a mandatory field and must be completed5 'This is a mandatory field and must be completed# 'Must be 20 characters or less# 'Must be 15 characters or less#'Must be 15 characters or less#'Must be 15 characters or less"'Must be 5 characters or less"'Must be 3 characters or less"'Must be 5 characters or less5'This is a mandatory field and must be completed5'This is a mandatory field and must be completed"'Must be 2 characters or less'cannot be negative%'Each named driver must be named%'Each named driver must be named%'Each named driver must be named% 'Each named driver must be named%"'Each named driver must be named%$'Each named driver must be named'Must be V'Must be N,A, D or O.'Maximum length for field is 3 characters<'Only characters in the range a-z, A-Z, 0-9 are allowed<'Only characters in the range a-z, A-Z, 0-9 are allowed<'Only characters in the range a-z, A-Z, 0-9 are allowed<'Only characters in the range a-z, A-Z, 0-9 are allowed 'Must be U or F* 'Maximum length for field is 12 chars 'Must be U or T< 'Only characters in the range a-z, A-Z, 0-9 are allowed< 'Only characters in the range a-z, A-Z, 0-9 are allowed<'Only characters in the range a-z, A-Z, 0-9 are allowed<'Only characters in the range a-z, A-Z, 0-9 are allowed$'Engine Size cannot be negative('Number of Seats cannot be negative-'Gross Vehicle Weight cannot be negative'Entry must be a date'Entry must be a date<'Only characters in the range a-z, A-Z, 0-9 are allowed 'Insurer ID must be numeric6'Vehicle Off Date is prior to the Vehicle On Date'Record Type'Update Type'Insurer ID'Insurer Branch ID'Quoteback'Delegated Authority ID'DA Branch ID'Policy Number$ 'Foreign Registration Indicator  'VRM 'Trade Plate Indicator 'Vehicle Type 'Vehicle Make'Vehicle Model'Vehicle Derivative'Vehicle Engine Size'Number of Seats'Gross Vehicle Weight'Vehicle Instep Code'Vehicle On Date'Vehicle Off Date'Permitted Drivers'Class of Use!'Additonal Drivers Indicator'Number of named Drivers'Named Driver Name (1)'Excluded Driver (1)'Named Driver Name (2)'Excluded Driver (2)'Named Driver Name (3)'Excluded Driver (3) 'Named Driver Name (4)!'Excluded Driver (4)"'Named Driver Name (5)#'Excluded Driver (5)$'Named Driver name (6)%'Excluded Driver (6)B@   66 Pu ``??qP6 uqP6 u    }q}}qq}}}}}qq} qq}qq}q}}q}}cc}}q}}q}q}q}q}q}}}q}}qt}tt}tt}}}q}}q}}}}q}}q}}q}}q}}q}}q}}q}}q}}q}}q}}q}q     PP P PP P PPPP P P PPPP P PPPPPPPPPPP PPPPPPP P PPPP P PPPPPPPPPPPPPPPPPPPPPPPPPPPPPP PPP P P PPP PPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP PP     CCCCCCCCC   * ` H 6 ` < l H T   r H N f r Z x l Z ` B   ~ f ~ f ~ f ~ f ~ f ~ f 0    @  C @    2 BBaA.1'd DefaultBBZA.1'd B B>tt ttDialog14).i] 8<D(Dialog1 Globals)lp) Command18).i] gks Command28).i] }} }}Dialog14).i] Dialog1(Dialog1 Globals),LSOBEN ,0$8TB4*26F44B8lNEWDELETE INITIALIZE TERMINATEOBJECT (GLOBALS) (Globals)* DIALOG REGISTRY MODULE *u* Dialog Registry Module *M0h*h* Ҥ Command18).i] h'gOK_OKd2 Command28).i] h'GCancel_Canceld2ii '++LotusScript Development Environment:2:5:(Options):0:74 Option Public Option Declare '++LotusScript Development Environment:2:5:(Forward):0:1 Declare Type tagOPENFILENAME Declare Public Function ColNumberToA1(Byval lngColumnNumber As Long) As String ' returns a column in A1 notation Declare Public Function Replace(Byval strString As String, strWhat As String, strWith As String) As String Declare Function RunRules(CurrentSheet As sheet) Declare Sub ClearAllComments Declare Public Function isAlphaNumeric(varInput) As Long Declare Public Function clearEmpty(varInput As Variant) As String Declare Public Function isCaseOf(strInput As String, strCaseList As String) As Long ' the case list should be ; seperated Declare Public Sub SaveCSVFile(ws As sheet) Declare Sub SaveAll() Declare Function isADate(strInput As Variant) As Variant Declare Function SaveAsDialog (FileName As String , Filter As String, DefExt As String ) As String Declare Function toUniCode(strSource As String) As String Declare Function fromUn iCode(strSource As String, lngLen As Long) As String Declare Public Function isANumeric(varInput) As Long '++LotusScript Development Environment:2:5:(Declarations):0:10 Type tagOPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long lpstrFilter As String lpstrCustomFilter As Long nMaxCustFilter As Long nFilterIndex As Long lpstrFile As String nMaxFile As Long lpstrFileTitle As String nMaxFileTitle As Long lpstrInitialDir As String lpstrTitle As String Flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As Long End Type Declare Function GetSaveAsFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (OPENFILENAME As tagOPENFILENAME) As Long Const OFN_ALLOWMULTISELECT = &H200 Const OFN_CREATEPROMPT = &H2000 Const OFN_ENABLEHOOK = &H20 Const OFN_ENABLETEMPLATE = &H40 Const OFN_ENABLETEMPLATEHANDLE = &H80 Const OFN_EXPLORER = &H80000 Const OFN_EXTENSIONDIFFERENT = &H400 Const OFN_FILEMUSTEXIST = &H1000 Const OFN_HIDEREADONLY = &H4 Const OFN_LONGNAMES = &H200000 Const OFN_NOCHANGEDIR = &H8 Const OFN_NODEREFERENCELINKS = &H100000 Const OFN_NOLONGNAMES = &H40000 Const OFN_NONETWORKBUTTON = &H20000 Const OFN_NOREADONLYRETURN = &H8000 Const OFN_NOTESTFILECREATE = &H10000 Const OFN_NOVALIDATE = &H100 Const OFN_OVERWRITEPROMPT = &H2 Const OFN_PATHMUSTEXIST = &H800 Const OFN_READONLY = &H1 Const OFN_SHAREAWARE = &H4000 Const OFN_SHAREFALLTHROUGH = 2 Const OFN_SHARENOWARN = 1 Const OFN_SHAREWARN = 0 Const OFN_SHOWHELP = &H10 Dim strInput As String Dim lngCounter As Long '++LotusScript Development Environment:2:1:ColNumberToA1:2:8 'Coppied straight from excell Public Function ColNumberToA1(Byval lngColumnNumber As Long) As String ' returns a column in A1 notation Dim lngLowChar As Long 'lngColumnNumber is 1 based! lngColumnNumber = lngColumnNumber - 1 lngLowChar = lngColumnNumber Mod 26 ColNumberToA1  = Chr$(Asc("A") + lngLowChar) lngColumnNumber = lngColumnNumber - lngLowChar If lngColumnNumber > 0 Then lngColumnNumber = lngColumnNumber / 26 End If If (lngColumnNumber > 0) Then lngColumnNumber = lngColumnNumber - 1 ColNumberToA1 = Chr$(Asc("A") + lngColumnNumber) + ColNumberToA1 End If End Function '++LotusScript Development Environment:2:1:Replace:1:8 Public Function Replace(Byval strString As String, strWhat As String, strWith As String) As String Dim lngCounter As Long Dim lngWhatLength As Long Dim lngWithLength As Long lngWhatLength = Len(strWhat) lngWithLength = Len(strWith) lngCounter = Instr(strString, strWhat) While lngCounter > 0 strString = Left$(strString, lngCounter - 1) + strWith + Mid$(strString, lngCounter + lngWhatLength) lngCounter = lngCounter + lngWithLength + 1 lngCounter = Instr(lngCounter, strString, strWhat) Wend Replace = strString End Function '++LotusScript Development Environment:2:1:RunRules:1:8 Function RunRules(CurrentSheet As sheet) Dim lngColum ns As Long Dim lngRows As Long Dim strFormula As String Dim lngRowNumber As Long Dim lngColNumber As Long RunRules=True CurrentSheet.MoveCellPointer $EndHome,0 'Get the number of rows and cols (make 1 based to match excell) lngRows=CurrentSheet.ActiveCell.EndRow+1 lngColumns=CurrentSheet.ActiveCell.EndColumn +1 Dim entireRange As Range Dim strActiveRange As String strActiveRange = ("A1.."+ ColNumberToA1(lngColumns)+Cstr(lngRows)) Set entireRange = Bind(strActiveRange) On Error Resume Next Call CurrentDocument.lock(False,"") CurrentDocument.DataProtected =False On Error Goto 0 For lngRowNumber = 6 To lngRows For lngColNumber = 2 To lngColumns strFormula = entireRange.cell(0, lngColNumber-1).CellDisplay entireRange.cell(lngRowNumber -1, lngColNumber-1,0).CellComment = "" If strFormula <> "" Then strFormula = Replace(strFormula, "#", Cstr(lngRowNumber)) strFormula = Replace(strFormula, "~", ColNumberToA1(lngColNumber)) Dim cell0 As range  Set cell0=entireRange.cell(0,0,0) cell0.formatname = "General" cell0.Contents="="+strFormula Call cell0.recalcrange(0) Dim lngResult As Long lngResult=Clng(cell0.celldisplay) If lngResult > 0 Then ' there's an error on this cell entireRange.cell(lngRowNumber -1, lngColNumber-1,0).CellComment = "Error: " +entireRange.Cell(lngResult, lngColNumber-1,0).CellDisplay If RunRules=True Then entireRange.cell(lngRowNumber -1, lngColNumber-1,0).Select entireRange.cell(lngRowNumber -1, lngColNumber-1,0).goto RunRules=False End If End If End If Next Next CurrentDocument. DataProtected =True Call CurrentDocument.lock(True,"") End Function '++LotusScript Development Environment:2:2:ClearAllComments:1:8 Sub ClearAllComments Dim lngColumns As Long Dim lngRows As Long Dim strFormula As String Dim lngRowNumber As Long Dim lngColNumber As Long Dim LastSelected As Range Dim CurrentSheet As Sheet Set Curre ntSheet= [].CurrentSheet Set LastSelected = CurrentSheet.ActiveCell.Cell(0,0,0) CurrentSheet.MoveCellPointer $EndHome,0 'Get the number of rows and cols (make 1 based to match excell) lngRows=CurrentSheet.ActiveCell.EndRow+1 lngColumns=CurrentSheet.ActiveCell.EndColumn +1 LastSelected.Select LastSelected.goto Dim entireRange As Range Dim strActiveRange As String strActiveRange = ("A1.."+ ColNumberToA1(lngColumns)+Cstr(lngRows)) Set entireRange = Bind(strActiveRange) For lngRowNumber = 6 To lngRows For lngColNumber = 1 To lngColumns entireRange.cell(lngRowNumber -1, lngColNumber-1,0).CellComment = "" entireRange.cell(lngRowNumber -1, lngColNumber-1,0).Clear Next Next End Sub '++LotusScript Development Environment:2:1:isAlphaNumeric:1:8 Public Function isAlphaNumeric(varInput) As Long isAlphaNumeric = False Dim strInput As String On Error Resume Next strInput = Cstr(varInput) If Err Then Err =0 Exit Function End If On Error Goto 0 'Need to chech each c har is in A-Z,a-z,0-9, Dim lngCounter As Long For lngCounter = 1 To Len(strInput) 'ASC and <> may be quicker? If Instr("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ", Mid$(strInput, lngCounter, 1)) = 0 Then ' non alhpa cound Exit For End If Next If lngCounter > Len(strInput) Then isAlphaNumeric = True End If End Function '++LotusScript Development Environment:2:1:clearEmpty:1:8 Public Function clearEmpty(varInput As Variant) As String clearEmpty = "" Dim strInput As String On Error Resume Next strInput = Cstr(varInput) If Err Then Err =0 Exit Function End If On Error Goto 0 'if strInput contains only [ ] or 0 then set to "" Dim lngCounter As Long For lngCounter = 1 To Len(strInput) If Instr(" 0", Mid$(strInput, lngCounter, 1)) = 0 Then ' found a char that isn't 0 or [ ] clearEmpty = strInput Exit For End If Next End Function '++LotusScript Development Environment:2:1:isCaseOf:1:8 Public Function isCaseOf(strInput As String, strCaseList  As String) As Long ' the case list should be ; seperated isCaseOf = False If Len(strInput) > 0 Then strInput = ";" + strInput + ";" strCaseList = ";" + strCaseList + ";" If Instr(strCaseList, strInput) > 0 Then isCaseOf = True End If End If End Function '++LotusScript Development Environment:2:2:SaveCSVFile:1:8 Public Sub SaveCSVFile(ws As sheet) Dim lngColumns As Long Dim lngRows As Long Dim lngRowNumber As Long Dim lngColNumber As Long Dim lngoutputfile As Long Dim FileName As String FileName = SaveAsDialog("*.csv","CSV Files(*.csv)" & Chr(0) & "*.csv" & Chr(0) & "All Files (*.*)" & Chr(0) & "*.*" & Chr(0),"*.csv") If (FileName= "") Then Exit Sub 'no file name! End If lngoutputfile = Freefile Open FileName For Output As #lngoutputfile ws.MoveCellPointer $EndHome,0 'Get the number of rows and cols (make 1 based to match excell) lngRows = ws.ActiveCell.EndRow+1 lngColumns = ws.ActiveCell.EndColumn +1 Dim entireRange As Range Dim strActiveRange As String  strActiveRange = ("A1.."+ ColNumberToA1(lngColumns)+Cstr(lngRows)) Set entireRange = Bind(strActiveRange) For lngRowNumber = 6 To lngRows For lngColNumber = 2 To lngColumns Dim strCSVText As String strCSVText = entireRange.Cell(lngRowNumber-1, lngColNumber-1).CellDisplay strCSVText=replace(strCSVText,","," ") strCSVText=replace(strCSVText,"-","") ' replace a - with nother (to sort out the date format!) strCSVText=replace(strCSVText,"""","'") strCSVText=replace(strCSVText,Chr$(13)," ") strCSVText=replace(strCSVText,Chr$(10)," ") Print #lngoutputfile, strCSVText; If lngColNumber < lngColumns Then Print #lngoutputfile, ","; End If Next If lngRowNumber < lngRows Then Print #lngoutputfile, "" End If Next Close #lngoutputfile End Sub '++LotusScript Development Environment:2:2:SaveAll:1:8 Sub SaveAll() Dim ws As sheet Set ws = [].CurrentSheet If RunRules(ws) Then Call SaveCSVFile(ws) Else Messagebox "There is a problem with some of the data tha t has been entered",0 End If End Sub '++LotusScript Development Environment:2:1:isADate:1:8 Function isADate(strInput As Variant) As Variant On Error Resume Next isADate=0 isADate=Isdate(Cdat(strInput)) If isADate = -1 Then If Cdat(strInput) < Cdat("01/01/2000") Or Cdat(strInput) > Cdat("01/01/2030") Then isADate= 2 End If End Function '++LotusScript Development Environment:2:1:SaveAsDialog:1:8 Function SaveAsDialog (FileName As String , Filter As String, DefExt As String ) As String Dim Title As String Dim FileTitle As String Title = "Save As" Dim OPENFILENAME As tagOPENFILENAME Dim szCurDir As String Dim APIResults As Long 'Give the dialog a caption title. Title = Title & Chr$(0) 'Allocate string space for returned strings FileName = FileName & Chr$(0) & Space$(2255) & Chr$(0) FileTitle = Space$(1255) & Chr$(0) Filter = filter &Chr$(0) DefExt = DefExt & Chr$(0) 'Set up the default directory szCurDir ="" & Chr$(0) 'Set up the data structure before you  call the GetOpenFileName OPENFILENAME.lStructSize = Len(OPENFILENAME) OPENFILENAME.hwndOwner = 0& OPENFILENAME.lpstrFilter = filter OPENFILENAME.nFilterIndex = 1 OPENFILENAME.lpstrFile = FileName OPENFILENAME.nMaxFile = Len(FileName) OPENFILENAME.lpstrFileTitle = FileTitle OPENFILENAME.nMaxFileTitle = Len(FileTitle) OPENFILENAME.lpstrTitle = Title OPENFILENAME.Flags = OFN_PATHMUSTEXIST OPENFILENAME.lpstrDefExt = DefExt OPENFILENAME.hInstance = 0 OPENFILENAME.lpstrCustomFilter = 0 OPENFILENAME.nMaxCustFilter = 0 OPENFILENAME.lpstrInitialDir = szCurDir OPENFILENAME.nFileOffset = 0 OPENFILENAME.nFileExtension = 0 OPENFILENAME.lCustData = 0 OPENFILENAME.lpfnHook = 0 OPENFILENAME.lpTemplateName = 0 'This will pass the desired data structure to the Windows API, 'which will in turn it uses to display the Open Dialog form. APIResults = GetSaveAsFileName(OPENFILENAME) If APIResults <> 0 Then FileName = Cstr( OPENFILENAME.lpstrFile ) FileTitle = Cstr( OPENFILEN AME.lpstrFileTitle ) SaveAsDialog=FileName Else SaveAsDialog = "" End If End Function '++LotusScript Development Environment:2:1:toUniCode:1:8 Function toUniCode(strSource As String) As String Dim strResult As String Dim n As Long For n = 1 To Len(strSource) strResult = strResult & Mid$(strSource,n,1) &Chr$(0) Next strResult= strResult & Chr$(0) toUniCode = strResult End Function '++LotusScript Development Environment:2:1:fromUniCode:1:8 Function fromUniCode(strSource As String, lngLen As Long) As String Dim strResult As String Dim n As Long For n = 1 To lngLen Step 2 strResult = strResult & Mid$(strSource,n,1) Next strResult= strResult fromUniCode = strResult End Function '++LotusScript Development Environment:2:1:isANumeric:1:8 Public Function isANumeric(varInput) As Long isANumeric = False On Error Resume Next strInput = Cstr(varInput) If Err Then Err =0 Exit Function End If On Error Goto 0 'Need to chech each char is in 0-9, For lngCounter = 1  To Len(strInput) 'ASC and <> may be quicker? If Instr("0123456789", Mid$(strInput, lngCounter, 1)) = 0 Then ' non alhpa cound Exit For End If Next If lngCounter > Len(strInput) Then isANumeric = True End If End Function >^^^^ N@ d     Sheet4_   ,  B@     B@       CSV Template Fi_,, B@  B@  """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" PAm??">" ^Click^ ^'++LotusScript Development Environment:2:5:(Options):0:66 '++LotusScript Development Environment:2:5:(Forward):0:1 Declare Sub Click(Source As Buttoncontrol) '++LotusScript Development Environment:2:5:(Declarations):0:2 '++LotusScript Development Environment:2:2:BindEvents:1:129 Private Sub BindEvents(Byval Objectname_ As String) Static Source As BUTTONCONTROL Set Source = Bind(Objectname_) On Event Click From Source Call Click End Sub '++LotusScript Development Environment:2:2:Click:1:12 Sub Click(Source As Buttoncontrol) Call saveall() End Sub"zSaveCSV??">@P Save" W  StiebeoStiebeo123123 PropertyDoc Info AuthorDoc Info CommentsDoc Info Editing TimeDoc Info KeywordsDoc Info Last RevisorDoc Info ObjectDoc Info RevisionsDoc Info Revisions CountDoc Info SubjectDoc Info Title  h  v v  .          X G CMHdrh