I'm attempting to understand this code, it was written by several
people and now I have to try and amend it: the code is:


Dim meWorkbook As Workbook
Dim inputInvoiceWorkbook As Workbook
Dim totalrateval, totalactualNI, actNIcol As Integer


'################################################# #############################
Private Static Sub cbInvoiceBrowse_Click()
Dim processingDir As Range

If processingDir Is Nothing Then
Set processingDir = meWorkbook.Sheets("Data").Range("B5")
End If

'On Error GoTo cancelFileSelect
dlgFile.dbFileOpen.DialogTitle = "Select input invoice..."
dlgFile.dbFileOpen.filename = processingDir.value & "*.xls"

dlgFile.dbFileOpen.ShowOpen

Me.tbFilename.Text = dlgFile.dbFileOpen.filename
processingDir.value = VBA.Left(Me.tbFilename.Text,
VBA.InStrRev(Me.tbFilename.Text, "\"))
cancelFileSelect:
End Sub

'################################################# #############################
Private Sub cbOpen_Click()
Dim sheet As Worksheet
Dim isCPC As Boolean

closeInputInvoiceWorkbook
Set inputInvoiceWorkbook = Workbooks.Open(tbFilename.Text)

Me.lbSheetSelect.Clear
For Each sheet In inputInvoiceWorkbook.Sheets
Me.lbSheetSelect.AddItem (sheet.Name)
If meWorkbook.Sheets("Data").Range("B6").value Then
' If invoiceEngine.checkCPC(sheet) Then
frmInvoiceProcessor.xbCPCExport.Enabled = True
'frmInvoiceProcessor.xbCPCExport.value = 1
' End If
End If
Next sheet
' temp test with being able to cpc regardless of if we think we have
cpc data.
'frmInvoiceProcessor.xbCPCExport.Enabled = True

Me.cbProcess.Enabled = True
Me.cbSelectAll.Enabled = True
Me.cbUnselectAll.Enabled = True
End Sub

Private Sub cbxSupplier_Change()

End Sub

Private Sub cmdCleanse_Click()
Dim index As Integer
Dim sheetName As String

If Me.tbInvoiceNumber = "" Then
MsgBox "Please enter 'Batch Number' before pressing " &
Me.cbProcess.Caption
GoTo fail
End If

invoiceEngine.setCPCRate (VBA.val(Me.tbCPCRate.value))

index = 0
While index < Me.lbSheetSelect.ListCount
If Me.lbSheetSelect.Selected(index) Then
sheetName = Me.lbSheetSelect.List(index)
Me.lbSheetSelect.RemoveItem index
Me.tbSheetName.Text = sheetName
Me.Repaint
cleanse_sheet inputInvoiceWorkbook, sheetName,
Me.xbCPCExport.value
Else
index = index + 1
End If
Wend

Dim Msg, Style, Title, response
Msg = "Do you want to save cleansed sheet and Continue ?"
Style = vbYesNo + vbSystemModal + vbQuestion + vbDefaultButton2
Title = "Sheet(s) Cleansed."
response = MsgBox(Msg, Style, Title)
If response = vbYes Then
'save cleansed file
Dim sheet As Worksheet
inputInvoiceWorkbook.SaveAs inputInvoiceWorkbook.Path &
"\CLEANSED_" & " " & inputInvoiceWorkbook.Name '& Me.tbInvoiceNumber
'refresh sheet list box
Me.lbSheetSelect.Clear
For Each sheet In inputInvoiceWorkbook.Sheets
Me.lbSheetSelect.AddItem (sheet.Name)
If meWorkbook.Sheets("Data").Range("B6").value Then
frmInvoiceProcessor.xbCPCExport.Enabled = True
End If
Next sheet
Me.cbProcess.Enabled = True
Me.cbSelectAll.Enabled = True
Me.cbUnselectAll.Enabled = True
Else
'close file
'exit form
Dim alertstatus As Boolean
alertstatus = Application.DisplayAlerts
Application.DisplayAlerts = False
'closeInputInvoiceWorkbook
Me.Hide
Application.DisplayAlerts = alertstatus
End If

fail:
End Sub

'################################################# #############################
Private Sub cbProcess_Click()
Dim index As Integer
Dim sheetName As String

' Disable some of the buttons
Me.cbOpen.Enabled = False
' Me.cbRateBrowse.Enabled = False
Me.cbInvoiceBrowse.Enabled = False
' Set busy cursor
Me.MousePointer = fmMousePointerHourGlass

' check if we have an invoice number - fail if not
If Me.tbInvoiceNumber = "" Then
MsgBox "Please enter 'Batch Number' before pressing " &
Me.cbProcess.Caption
GoTo fail
End If

'set odbc lookup connect data
If Not ODBCLookupModule.initializeDb(Me.txtDSN, Me.txtUserId,
Me.txtPassword) Then
MsgBox "Failed to initialise lookup database."
GoTo fail
End If

' set VAT rate (just in case)
invoiceEngine.setVatRate (VBA.val(Me.tbVAT.value))
invoiceEngine.setCPCRate (VBA.val(Me.tbCPCRate.value))

invoiceEngine.setSupplier (Me.cbxSupplier.value)
invoiceEngine.setInvType (Me.cbxType.value)
invoiceEngine.setBatchNo (Me.tbInvoiceNumber)

' Create output wb & files
createOutputWorkbook meWorkbook, Me.xbMargin.value, Me.xbCPCExport
'If Me.xbCPCExport.value Then
cpcExport.initCPCExport
Replace(meWorkbook.Sheets("Data").Range("B7").valu e, "#", _
Me.tbInvoiceNumber.value)
'End If

' tell process to get on with it for all the selected sheets
index = 0
While index < Me.lbSheetSelect.ListCount
If Me.lbSheetSelect.Selected(index) Then
sheetName = Me.lbSheetSelect.List(index)
Me.lbSheetSelect.RemoveItem index
Me.tbSheetName.Text = sheetName
Me.Repaint
invoiceEngine.process_sheet inputInvoiceWorkbook,
sheetName, _
Me.xbCPCExport.value, Me.tbInvoiceNumber.value
Else
index = index + 1
End If
Wend

' Tidy GUI
Me.tbSheetName.Text = "<<COMPLETED - SAVE"
ODBCLookupModule.finalizeDb
Me.cbSaveAs.Enabled = True
fail:
Me.MousePointer = fmMousePointerDefault


End Sub

'################################################# #############################
Sub ProgressBar(percent As Integer)
ProgressBar1.value = percent
ProgressBar1.Refresh
End Sub

'################################################# #############################
Private Sub cbRateBrowse_Click()
dlgFile.dbFileOpen.DialogTitle = "Select lookup workbook..."

dlgFile.dbFileOpen.ShowOpen

Me.tbRateFilename.Text = dlgFile.dbFileOpen.filename

cbOpen.Default = True
End Sub

Private Sub cbProcess_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

'################################################# #############################
Private Sub cbSelectAll_Click()
Dim index As Integer

For index = 0 To lbSheetSelect.ListCount - 1
lbSheetSelect.Selected(index) = True
Next index
End Sub

'################################################# #############################
Private Sub cbUnselectAll_Click()
Dim index As Integer

For index = 0 To lbSheetSelect.ListCount - 1
lbSheetSelect.Selected(index) = False
Next index
End Sub


'################################################# #############################
Private Sub tbFilename_Change()
Me.cbOpen.Enabled = True
End Sub

'################################################# #############################
Private Static Sub cbSaveAs_Click()
Dim alertstatus As Boolean
Dim msgResponse As Integer
Dim saveDir As Range
Dim wbFilename As String
Dim marginWBFilename As String
Dim emailAddress As String

emailAddress = meWorkbook.Sheets("Data").Range("B11")

If saveDir Is Nothing Then
Set saveDir = meWorkbook.Sheets("Data").Range("B8")
End If

If errorLog.numberErrors 0 Then
msgResponse = MsgBox("ProCur Invoice workbook has errors saved
in error tab, save error tab?", vbYesNoCancel)
If (msgResponse = vbNo) Then
errorLog.clearErrorLog
ElseIf msgResponse = vbCancel Then
Exit Sub
End If
End If

dlgFile.dbFileOpen.DialogTitle = "Choose filename to save ProCur
invoice..."
dlgFile.dbFileOpen.filename = saveDir.value & "OUTPUT_" & _
VBA.Mid(Me.tbFilename.value,
VBA.InStrRev(Me.tbFilename.value, "\") + 1) 'Me.tbInvoiceNumber.value
& " "
dlgFile.dbFileOpen.ShowSave
wbFilename = dlgFile.dbFileOpen.filename

' must do this before errorlog.exitErrorLog !!!!
'If Me.xbCPCExport.value Then
cpcExport.exitCPCExport (errorLog.numberErrors = 0),
emailAddress
'End If

' Inc invoice number (must unlock while doing that)
Me.tbInvoiceNumber.Locked = False
' Save and close new workbook
cleanOutputWorkbook
' close error log sheet before saving
errorLog.exitErrorLog

saveDir.value = VBA.Left(wbFilename, VBA.InStrRev(wbFilename,
"\"))
marginWBFilename = saveDir.value & "MARGIN_" & VBA.Mid(wbFilename,
VBA.InStrRev(wbFilename, "\") + 1)
saveOutputWorkbook wbFilename, marginWBFilename,
Me.tbFilename.Text, Me.tbInvoiceNumber.Text
closeOutputWorkbook

If Me.xbCPCExport Then
Me.tbInvoiceNumber.value = Me.tbInvoiceNumber.value + 1
Me.tbInvoiceNumber.Locked = True
End If

' We're done so hide me
UserForm_Hide
End Sub



'################################################# #############################
Private Static Sub UserForm_Activate()
Dim firstActiveWorkbook As Workbook
If firstActiveWorkbook Is Nothing Then
Set firstActiveWorkbook = ActiveWorkbook
End If
Set meWorkbook = firstActiveWorkbook
Dim Version

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ VERSION @@@@@
Const InternalVersion As String = "1.7.10 (Beta)"
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ VERSION @@@@@

Version = meWorkbook.Sheets("Data").Range("B9").value
If Version < InternalVersion Then
MsgBox "I think I'm version " & InternalVersion & " but
Version in Data sheet is " & Version & ". Cannot Continue"
UserForm_Hide
Exit Sub
End If

Me.Caption = "ProCur Invoice Processor v" & InternalVersion

Me.tbFilename.value = ""
Me.lbSheetSelect.Clear
Me.cbSaveAs.Enabled = False
Me.cbProcess.Enabled = False
Me.cbSelectAll.Enabled = False
Me.cbUnselectAll.Enabled = False
Me.cbOpen.Enabled = False
Me.cbInvoiceBrowse.Enabled = True
Me.txtDSN = "Prod"
Me.txtUserId = "**hidden***"
Me.txtPassword = "**hidden****"
Me.ProgressBar1.value = 0

frmInvoiceProcessor.xbCPCExport.value = 1
'frmInvoiceProcessor.xbCPCExport.Enabled = True
frmInvoiceProcessor.xbCPCExport.value = 0
frmInvoiceProcessor.xbCPCExport.Enabled = False

If cbxType.ListCount < 1 Then
cbxType.AddItem "OB"
cbxType.AddItem "CPC"
cbxType.AddItem "ST"
cbxType.Style = fmStyleDropDownList
cbxType.BoundColumn = 1
cbxType.ListIndex = 0
cbxType.Enabled = True

Dim supRow As Integer
supRow = 13
Do While meWorkbook.Worksheets("Data").Cells(supRow, 11).value
< ""
cbxSupplier.AddItem
meWorkbook.Worksheets("Data").Cells(supRow, 11).value
supRow = supRow + 1
Loop
cbxSupplier.Style = fmStyleDropDownList
cbxSupplier.BoundColumn = 1
cbxSupplier.ListIndex = 0
cbxSupplier.Enabled = True
End If

End Sub

'################################################# #############################
Private Sub cbCancel_Click()
Dim msgResponse As Integer

msgResponse = MsgBox("Close Invoice Processing Macro (All
processed sheets will be lost)?", vbYesNo)
If (msgResponse = vbYes) Then
If Me.xbCPCExport.value Then
cpcExport.exitCPCExport False, ""
End If
errorLog.exitErrorLog
closeOutputWorkbook
UserForm_Hide
End If

End Sub

'################################################# #############################
Private Sub UserForm_Hide()
Me.tbSheetName.Text = ""
' done with input workbook
closeInputInvoiceWorkbook
ODBCLookupModule.finalizeDb
Me.Hide
End Sub

'################################################# #############################
Sub closeInputInvoiceWorkbook()
On Error GoTo fail
If Not (inputInvoiceWorkbook Is Nothing) Then
Dim alertstatus As Boolean
alertstatus = Application.DisplayAlerts
Application.DisplayAlerts = False
inputInvoiceWorkbook.Close
Set inputInvoiceWorkbook = Nothing
Application.DisplayAlerts = alertstatus
End If
fail:
End Sub

'################################################# #############################
Private Sub xbCPCExport_Change()
If Not (meWorkbook Is Nothing) Then
If xbCPCExport.value Then
' Me.tbInvoiceNumber.ControlSource = "'[" & meWorkbook.Name
& "]Data'!B10"
Me.tbInvoiceNumber.Locked = False
'Me.tbInvoiceNumber.MousePointer = fmMousePointerNoDrop
Else
' Me.tbInvoiceNumber.value = ""
' Me.tbInvoiceNumber.ControlSource = ""
Me.tbInvoiceNumber.Locked = False
Me.tbInvoiceNumber.MousePointer = fmMousePointerDefault
End If
End If
End Sub

Somehow this code (or maybe you need all the relevant code) cleanses a
particular spreadsheet, then outputs 3 more spreadsheets and a csv
file. The csv file data is compiled of the input spreadsheet and also
lookup into a Oracle database for certain values and then writes them
to a csv file. I need to know how it wrires to the csv file so I can
add other fields to the csv file.

Please help

Regards

Rachelle