Writing to a text file

R

Rachel Curran

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").value, "#", _
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
 
M

Myrna Larson

If it's written to a CSV file by Excel, there's only one line needed to do
that, and it should contain the SaveAs command. Search the code for SaveAs and
see what you find.

If that's the method used, you don't add fields when writing the file. Excel
automatically saves the entire worksheet. To add more data to the file, you
would have to copy that data to the worksheet being saved.



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").value, "#", _
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Script out of range 3
Excel VBA help: Text file formatting 19
Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
How to hide password? 1
writing excel data into word document 1
import text 1
YYY-MM-DD format is changing when convert data to Flat File 3
importing file into a new file 2

Top