J
Joanie Brows
Guest
I have a program that works with huge input and output files. It takes about an hour to run. Id like to add a progress bar so the user doesnt think that the program has frozen. Ive spent the past few hours looking at examples online but I still dont understand what I need to do to get it to work with my program. Heck, I cant even declare it correctly, Im getting errors, the first one being type Backgroundworker is not defined and 2 errors that read Declaration expected on these three lines of code:
Dim bgw As New BackgroundWorker
bgw.WorkerReportsProgress = true
bgw.RunWorkerAsync()
I just removed those lines from my code because they are obviously not correct. Im suppose Im missing some an Imports statement, or a reference to something. I really dont know. A lot of what Ive read says Backgroundworkers are easy to use, I have to disagree. Maybe once one understands how they work, but for me, not so much
Im going to take a shot in the dark here and post my program. I was hoping someone would have the time to show me where and how I can incorporate a backgroundworker/progressbar here. I know its a long shot but, hey, Ive got nothing to loose! Im oozing with desperation right now! lol
P.S. My code may not be the prettiest youve even seen, there are probably better ways to do some of the things Im doing, but it works and does what needs to be done. Dont judge me
Id like for everything that happens from the point that Button1 is clicked till I display a messagebox with the currrent time (messagebox.show("Program completed at " & theTimeIs) in line 394, I believe.
TIA!!!
Joanie
Imports System.IO
Imports System.Data
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel
Imports Shell32
Public Class Form1
Private Class dataOutput
this class is used to store all data for output file at the end of the program
Public category As String = "--"
Public prodDesc As String = "--"
Public code As String = "--"
Public clCount As String = "--"
Public clPer As String = "--"
Public bsCount As String = "--"
Public bsPer As String = "--"
Public perPen As String = "--"
Public uIndex As String = "--"
Public wCL_Count As String = "--"
Public wCL_Per As String = "--"
Public wBS_Count As String = "--"
Public wBS_Per As String = "--"
Public wPer_Pen As String = "--"
Public wIndex As String = "--"
End Class
Private Class Es01
Public cat_Key As String = "--"
Public cat_Desc As String = "--"
Public subcat_Key As String = "--"
Public subCatDesc As String = "--"
Public ans_Type As String = "--"
Public resp_Key As String = "--"
Public response As String = "--"
Public vNU_Card As String = "--"
Public vNU_Col As String = "--"
Public vNU_Punch As String = "--"
Public iN_CD_CL As String = "--"
Public col_Pnch As String = "--"
Public fWidth As String = "--"
Public rInd As String = "--"
Public prop_Codes As String = "--"
End Class
Private dataOutputList As New List(Of dataOutput)
Private Es01List As New List(Of Es01)
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim openFileDialog1 As New OpenFileDialog()
openFileDialog1.InitialDirectory = "F:\primeproduction\primenxt\R212\_CSV-AcxiomRpts\_PNextRpts\"
openFileDialog1.InitialDirectory = "c:\Excel\"
openFileDialog1.Filter = "Excel files (*.csv)|*.csv|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True
Dim totalRows As Integer = 0
Dim outputFilename As String = "--"
Dim TextWrtr As StreamWriter
TextWrtr = System.IO.File.CreateText(outputFilename)
If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
txtInput.Text = openFileDialog1.FileName
Me.DataGridView1.DataSource = Nothing
Dim dataTable As System.Data.DataTable
dataTable = importExcelfileToDataTable(openFileDialog1.FileName)
Me.DataGridView1.DataSource = dataTable
Me.TextBox1.Text = dataTable.Rows.Count
Dim totalColumns As Integer = dataTable.Columns.Count
Dim zippedFile As String = "C:\excel\BGWtemp\temp.zip"
Dim tempUnzippedDir As String = "C:\excel\BGWtemp\tempUnzippedFolder"
Dim es01Folder As String = "C:\excel\BGWtemp\tempEs01Folder"
Dim copyZippedFile As String = "C:\excel\test\ab212a.zip"
File.Copy(copyZippedFile, zippedFile, True)
UnZip(tempUnzippedDir, zippedFile)
ListUnzippedFiles(zippedFile)
SpecifyEs01Files(es01Folder, zippedFile)
CreateEs01List(es01Folder)
Dim rowCount As Integer = 0
Dim rowStart As Integer = 0
Dim rowEnd As Integer = 4
Dim rowOne As Integer = 0
Dim rowTwo As Integer = 1
Dim rowThree As Integer = 2
Dim rowFour As Integer = 3
Dim rowFive As Integer = 4
Dim newCategory As Boolean = False
Dim codeCount As Integer = 0
Dim G7 As Integer = dataTable.Rows(4).Item(2) total number of respondents for the whole file
Dim H14 As Integer = 0 the current columns total respondents within a category
Dim G14 As Integer = 0 the current categories total respondents
Dim H7 As Integer = 0 total respondents for that column. Column number could change, but the row always
remains the same. This is the total from the top section for each column
Dim H10 As Integer = 0 the current columns Target population within a category
Dim H13 As Double = 0 the current columns Row % within a category
Dim H3 As Integer = 0 the target population for the entire column. Column number could change, but
the row will always remain the same.
Dim H6 As Double = 0 the Row % for the entire column. Column number could change, but the row
will always remain the same
Dim H11 As Double = 0 the current columns Target % within a category
Dim H12 As Integer = 0 using this just for testing purposed, not needed for calculations
Dim loopedThroughFirstSection As Boolean = False
Dim outputRowCounter As Integer = 0
Dim thisRow As Integer = 0
Dim newCategoryCount As Integer = 0
Dim thisProdDesc As String = "--"
Dim thisProdA As String = "--"
Dim thisProdB As String = "--"
Dim categoryNotApplicable As Boolean = False
For Each rowOfData As DataRow In dataTable.Rows
For j As Integer = 2 To totalColumns - 1
H3 = dataTable.Rows(0).Item(j)
H6 = dataTable.Rows(3).Item(j) * 100 move decimal over to the right by 2 places. Decimal moved left 2 places when read in, I dont know why but Im moving it back
H7 = dataTable.Rows(4).Item(j)
For i As Integer = rowStart To rowEnd
categoryNotApplicable = False
If dataTable.Rows(rowFive).Item(2) = "N/A" Then
categoryNotApplicable = True
Else
G14 = dataTable.Rows(rowFive).Item(2)
Select Case i
Case rowOne
H10 = dataTable.Rows(i).Item(j)
thisProdB = dataTable.Rows(i).Item(0)
Case rowTwo
H11 = dataTable.Rows(i).Item(j)
H11 = H11 * 100 move decimal place over to the right by 2 places.
Input has % symbol and decimal moves left 2 places when reading in, I dont know why so Im just moving it back.
Case rowThree
H12 = dataTable.Rows(i).Item(j)
Case rowFour
H13 = dataTable.Rows(i).Item(j)
H13 = H13 * 100 move decimal place over to the right by 2 places.
Input has % symbol and decimal moves left 2 places when reading in, I dont know why so Im just moving it back.
Case rowFive
H14 = dataTable.Rows(i).Item(j)
If j = (totalColumns - 1) Then
If dataTable.Rows(i + 1).Item(j) Is DBNull.Value Then
If dataTable.Rows(i + 2).Item(j) Is DBNull.Value Then
newCategory = True
End If
End If
End If
End Select
If thisProdB = "Base Total" Then
thisProdA = "nothing base"
Else
If dataTable.Rows(rowStart - 1).Item(0) Is DBNull.Value Then
thisProdA = thisProdA
Else
thisProdA = dataTable.Rows(rowStart - 1).Item(0)
End If
End If
thisProdDesc = thisProdA & ": " & thisProdB
End If
Next
outputRowCounter += 1
If codeCount = 70 Then
loopedThroughFirstSection = True
End If
If loopedThroughFirstSection = True Then
If categoryNotApplicable = False Then
Dim wBsCount As Integer = H3
Dim bsCount As Integer = H7
Dim wClCount As Integer = H10
Dim clCount As Integer = H14
Dim clPer As String = "--"
If H14 = 0 OrElse G14 = 0 Then
clPer = "0"
Else
Dim XclPer As Double = (H14 / G14)
clPer = XclPer.ToString("0.###")
End If
Dim bsPer As String = "--"
If H7 = 0 OrElse G7 = 0 Then
bsPer = "0"
Else
Dim XbsPer As Double = (H7 / G7)
bsPer = XbsPer.ToString("0.###")
End If
Dim perPen As String = "--"
If H14 = 0 OrElse H7 = 0 Then
perPen = "0"
Else
Dim XperPen As Double = (H14 / H7)
perPen = XperPen.ToString("0.###")
End If
Dim uIndex As String = "--"
If H14 = 0 OrElse G14 = 0 OrElse H7 = 0 OrElse G7 = 0 Then
uIndex = "0"
Else
Dim XuIndex As Double = ((H14 / G14) / (H7 / G7) * 100)
uIndex = XuIndex.ToString("0.###")
End If
Dim wClPer As String = "--"
If H13 = 0 Then
wClPer = "0"
Else
Dim XwClPer As Double = (H13 / 100)
wClPer = XwClPer.ToString("0.###")
End If
Dim wBsPer As String = "--"
If H6 = 0 Then
wBsPer = "0"
Else
Dim XwBsPer As Double = (H6 / 100)
wBsPer = XwBsPer.ToString("0.###")
End If
Dim wPerPen As String = "--"
If H11 = 0 Then
wPerPen = "0"
Else
Dim XwPerPen As Double = (H11 / 100)
wPerPen = XwPerPen.ToString("0.###")
End If
Dim wIndex As String = "--"
If H13 = 0 OrElse H6 = 0 Then
wIndex = "0"
Else
Dim XwIndex As Double = (H13 / 100) / (H6 / 100) * 100
wIndex = XwIndex.ToString("0.###")
End If
If codeCount > 0 Then
If newCategoryCount > 0 Then
Dim newCategoryName As String = "--"
GetCategory(thisProdA, newCategoryName)
Dim thisDataOutput As New dataOutput
With thisDataOutput
.category = newCategoryName
.prodDesc = thisProdDesc
.code = codeCount
.clCount = clCount
.clPer = clPer
.bsCount = bsCount
.bsPer = bsPer
.perPen = perPen
.uIndex = uIndex
.wCL_Count = wClCount
.wCL_Per = wClPer
.wBS_Count = wBsCount
.wBS_Per = wBsPer
.wPer_Pen = wPerPen
.wIndex = wIndex
dataOutputList.Add(thisDataOutput)
End With
End If
End If
Else
For i As Integer = rowStart To rowEnd
Select Case i
Case rowOne
thisProdB = dataTable.Rows(i).Item(0)
Case rowFive
If j = (totalColumns - 1) Then
If dataTable.Rows(i + 1).Item(j) Is DBNull.Value Then
If dataTable.Rows(i + 2).Item(j) Is DBNull.Value Then
newCategory = True
End If
End If
End If
End Select
If thisProdB = "Base Total" Then
thisProdA = "nothing base"
Else
If dataTable.Rows(rowStart - 1).Item(0) Is DBNull.Value Then
thisProdA = thisProdA
Else
thisProdA = dataTable.Rows(rowStart - 1).Item(0)
End If
End If
thisProdDesc = thisProdA & ": " & thisProdB
Next
If codeCount > 0 Then
If newCategoryCount > 0 Then
Dim newCategoryName As String = "--"
GetCategory(thisProdA, newCategoryName)
Dim thisDataOutput As New dataOutput
With thisDataOutput
.category = newCategoryName
.prodDesc = thisProdDesc
.code = codeCount
.clCount = "N/A"
.clPer = "N/A"
.bsCount = "N/A"
.bsPer = "N/A"
.perPen = "N/A"
.uIndex = "N/A"
.wCL_Count = "N/A"
.wCL_Per = "N/A"
.wBS_Count = "N/A"
.wBS_Per = "N/A"
.wPer_Pen = "N/A"
.wIndex = "N/A"
dataOutputList.Add(thisDataOutput)
End With
End If
End If
End If
End If
codeCount += 1
Next
rowCount += 1
codeCount = 0
If newCategory = True Then
rowOne += 7
rowTwo += 7
rowThree += 7
rowFour += 7
rowFive += 7
newCategory = False
newCategoryCount += 1
Else
rowOne += 6
rowTwo += 6
rowThree += 6
rowFour += 6
rowFive += 6
End If
rowStart = rowOne
rowEnd = rowFive
If rowEnd > dataTable.Rows.Count Then
Exit For
End If
Next rowOfData
WriteTextFile(outputFilename, TextWrtr)
WriteExcelFile(outputFilename)
TextWrtr.Close()
DeleteTempEs01Folders(tempUnzippedDir, es01Folder, zippedFile)
End If
Dim theTimeIs As String = DateTime.Now
MessageBox.Show("Program completed at " & theTimeIs)
End Sub
Private Sub UnZip(ByVal tempUnzippedDir, ByVal zippedFile)
Dim sc As New Shell32.Shell()
Create directory in which you will unzip your files .
IO.Directory.CreateDirectory(tempUnzippedDir)
Declare the folder where the files will be extracted
Dim output As Shell32.Folder = sc.NameSpace(tempUnzippedDir)
Declare your input zip file as folder .
Dim input As Shell32.Folder = sc.NameSpace(zippedFile)
Extract the files from the zip file using the CopyHere command .
output.CopyHere(input.Items, 4)
End Sub
Private Sub ListUnzippedFiles(ByVal zippedFile)
Dim sc As New Shell32.Shell()
Dim input As Shell32.Folder = sc.NameSpace(zippedFile)
If input.Items.Count > 0 Then
For i = 0 To input.Items.Count - 1
ListBox1.Items.Add(input.Items.Item(i).Path.ToString)
Next
End If
End Sub
Private Sub SpecifyEs01Files(ByVal es01Folder, ByVal zippedFile)
For Each Item In ListBox1.Items
If Item.ToString.Contains(".ES01") Or Item.ToString.Contains(".es01") Then
Dim i As Integer = ListBox1.Items.IndexOf(Item)
Me.Text = i.ToString
Dim sc As New Shell32.Shell()
Create directory in which you will unzip your files .
IO.Directory.CreateDirectory(es01Folder)
Declare the folder where the files will be extracted
Dim output As Shell32.Folder = sc.NameSpace(es01Folder)
Declare your input zip file as folder .
Dim input As Shell32.Folder = sc.NameSpace(zippedFile)
Extract the files from the zip file using the CopyHere command .
output.CopyHere(input.Items(i), 4)
End If
Next
End Sub
Private Sub DeleteTempEs01Folders(ByVal tempUnzippedDir, ByVal es01Folder, ByVal zippedFile)
Dim attribute As System.IO.FileAttributes = FileAttributes.ReadOnly = False
Dim fileEntries As String() = Directory.GetFiles(tempUnzippedDir)
Dim fileEntries2 As String() = Directory.GetFiles(es01Folder)
Dim fileName As String = "--"
For Each fileName In fileEntries
File.SetAttributes(fileName, attribute)
Next fileName
Dim fileName2 As String = "--"
For Each fileName2 In fileEntries2
File.SetAttributes(fileName2, attribute)
Next fileName2
Directory.Delete(tempUnzippedDir, True) deletes directory and all the files within
Directory.Delete(es01Folder, True)
System.IO.File.Delete(zippedFile)
End Sub
Private Sub GetCategory(ByVal descToSearchFor, ByRef categoryToSet)
Dim resultsFound As Integer = 0
Dim matchSwitch As Boolean = False
Dim searchForLength As Integer = descToSearchFor.length
Dim es01Search = From e As Es01 In Es01List Where e.subCatDesc Like descToSearchFor
For Each e As Es01 In es01Search
If descToSearchFor Like e.subCatDesc Then
categoryToSet = e.cat_Desc
resultsFound += 1
End If
If resultsFound > 0 Then
matchSwitch = True
Exit For
End If
Next
Do While matchSwitch = False
Dim s As String = descToSearchFor
Dim sLength As Integer = s.Length
Dim newStringToSearch As String = "--"
Dim newStringLength As Integer = sLength
Dim i As Integer = 1
resultsFound = 0
Dim es01Search2
For Each character As Char In s
If newStringLength = 1 Then
categoryToSet = "CATEGORY NOT FOUND!!!!!"
matchSwitch = True
Exit For
Else
newStringToSearch = s.Substring(0, s.Length - i)
es01Search2 = From e2 As Es01 In Es01List Where e2.subCatDesc Like newStringToSearch
For Each e2 As Es01 In es01Search2
If newStringToSearch = e2.subCatDesc Then
categoryToSet = e2.cat_Desc
resultsFound += 1
End If
If resultsFound > 0 Then
matchSwitch = True
Exit Do
End If
Next
End If
newStringLength = newStringToSearch.Length
i += 1
Next
Loop
If resultsFound = 0 Then
categoryToSet = "CATEGORY NOT FOUND!!!"
End If
End Sub
Private Sub WriteTextFile(ByVal outputFilename, ByRef TextWrtr)
outputFilename = "C:\excel\BAckGroundWorkerTest.txt"
If File.Exists(outputFilename) Then
File.Delete(outputFilename)
End If
TextWrtr = System.IO.File.CreateText(outputFilename)
Dim lineOfText As String = "--"
Dim lineCounter As Integer = 0
Dim headers As String = "--"
Dim FinalOutput = From L As dataOutput In dataOutputList
For Each L As dataOutput In FinalOutput
If lineCounter = 0 Then
headers = "Category" & vbTab & "Prod_Desc" & vbTab & "Code" & vbTab & "Cl_Count" & vbTab & _
"Cl_Per" & vbTab & "Bs_Count" & vbTab & "Bs_Per" & vbTab & "Per_Pen" & vbTab & "U_INdex" & _
vbTab & "W_CL_Count" & vbTab & "W_Cl_Per" & vbTab & "W_Bs_Count" & vbTab & "W_Bs_Per" & _
vbTab & "W_Per_Pen" & vbTab & "W_Index"
TextWrtr.WriteLine(headers)
End If
lineOfText = L.category & vbTab & L.prodDesc & vbTab & L.code & vbTab & L.clCount & vbTab & L.clPer & _
vbTab & L.bsCount & vbTab & L.bsPer & vbTab & L.perPen & vbTab & L.uIndex & vbTab & L.wCL_Count & _
vbTab & L.wCL_Per & vbTab & L.wBS_Count & vbTab & L.wBS_Per & vbTab & L.wPer_Pen & vbTab & L.wIndex
TextWrtr.WriteLine(lineOfText)
lineCounter += 1
Next
End Sub
Private Sub WriteExcelFile(ByVal outputFilename)
Try
~~> Define your Excel Objects
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xlRange As Excel.Range
outputFilename = "C:\excel\CSV_OUTPUT_A1_ColumnWidth.xlsx"
If File.Exists(outputFilename) Then
File.Delete(outputFilename)
End If
~~> Add a New Workbook
xlWorkBook = xlApp.Workbooks.Add
~~> Display Excel
xlApp.Visible = True
~~> Set the relebant sheet that we want to work with
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
Set column widths
xlWorkSheet.Columns("A:B").ColumnWidth = 41
xlWorkSheet.Columns("C").columnwidth = 6
xlWorkSheet.Columns("D:O").columnwidth = 12
With xlWorkSheet
.Range("A1").Value = "Category"
.Range("B1").Value = "Prod_Desc"
.Range("C1").Value = "Code"
.Range("D1").Value = "CL_Count"
.Range("E1").Value = "CL_Per"
.Range("F1").Value = "Bs_Count"
.Range("G1").Value = "Bs_Per"
.Range("H1").Value = "Per_pen"
.Range("I1").Value = "U_Index"
.Range("J1").Value = "W_CL_Count"
.Range("K1").Value = "W_CL_Per"
.Range("L1").Value = "W_Bs_Count"
.Range("M1").Value = "W_Bs_Per"
.Range("N1").Value = "W_Per_pen"
.Range("O1").Value = "W_Index"
Dim thisRow As Integer = 2
Dim FinalOutput = From L As dataOutput In dataOutputList
For Each L As dataOutput In FinalOutput
Dim aCell As String = "A" & thisRow
Dim bCell As String = "B" & thisRow
Dim cCell As String = "C" & thisRow
Dim dCell As String = "D" & thisRow
Dim eCell As String = "E" & thisRow
Dim fCell As String = "F" & thisRow
Dim gCell As String = "G" & thisRow
Dim hCell As String = "H" & thisRow
Dim iCell As String = "I" & thisRow
Dim jCell As String = "J" & thisRow
Dim kCell As String = "K" & thisRow
Dim lCell As String = "L" & thisRow
Dim mCell As String = "M" & thisRow
Dim nCell As String = "N" & thisRow
Dim oCell As String = "O" & thisRow
.Range(aCell).Value = L.category
.Range(bCell).Value = L.prodDesc
.Range(cCell).Value = L.code
.Range(dCell).Value = L.clCount
.Range(eCell).Value = L.clPer
.Range(fCell).Value = L.bsCount
.Range(gCell).Value = L.bsPer
.Range(hCell).Value = L.perPen
.Range(iCell).Value = L.uIndex
.Range(jCell).Value = L.wCL_Count
.Range(kCell).Value = L.wCL_Per
.Range(lCell).Value = L.wBS_Count
.Range(mCell).Value = L.wBS_Per
.Range(nCell).Value = L.wPer_Pen
.Range(oCell).Value = L.wIndex
thisRow += 1
Next
End With
xlWorkBook.SaveAs(outputFilename)
xlWorkBook.Close()
xlApp = Nothing
xlRange = Nothing
xlWorkSheet = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Public Function importExcelfileToDataTable(ByVal xlsFileFullName As String) As System.Data.DataTable
Dim returnDT As New System.Data.DataTable()
If File.Exists(xlsFileFullName) Then
Microsoft.Office.Interop.Excel
Create new Application.
Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
// Open Excel Workbook
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(xlsFileFullName)
//Get sheet the first work sheet Get sheet, if not found return null;
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = xlWorkBook.Worksheets(1) get the first worksheet.
//Get range
Dim xlRange As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.UsedRange
//Load all cels into Array
Dim arrayRanges(,) As Object = xlRange.Value This line loads the range of the excel file into an array
xlWorkBook.Close()
xlApp = Nothing
xlRange = Nothing
xlWorkSheet = Nothing
//** import into Datatable by converting the object array to a table
returnDT = convertToDataTable(arrayRanges)
End If
Return returnDT
End Function
Public Function convertToDataTable(ByVal array As Object(,)) As System.Data.DataTable
Dim row As Integer
Dim column As Integer
Dim intRows As Integer
Dim intCols As Integer
Dim dataTable As New System.Data.DataTable()
Dim dRow As DataRow
intRows = array.GetLength(0)
If intRows <> 0 Then
If intRows <> 0 Then
intCols = array.GetLength(1)
If intCols <> 0 Then
For column = 1 To intCols
trim the column if has leading spaces
Dim holdValue As Object = array(6, column)
Dim holdString As [String]
If holdValue IsNot Nothing Then
holdString = DirectCast(holdValue, [String])
holdString = holdString.Trim()
Else
assign column name if column blank
holdString = "No_Name_Col" & column.ToString()
End If
dataTable.Columns.Add(New DataColumn(holdString))
Next
Skip the first few rows, that should be used for the column heading
For row = 7 To intRows
dRow = dataTable.NewRow()
For column = 1 To intCols
need to assign row by column name so do the following
trim the column if has leading spaces
Dim holdValue As Object = array(6, column)
Dim holdString As [String]
If holdValue IsNot Nothing Then
holdString = DirectCast(holdValue, [String])
holdString = holdString.Trim()
Else
assign column name if column blank
holdString = "No_Name_Col" & column.ToString()
End If
dRow(holdString) = array(row, column)
Next
dataTable.Rows.Add(dRow)
Next
End If
End If
End If
dRow = Nothing
Return dataTable
End Function
Public Sub CreateEs01List(ByVal es01Folder)
Dim fileEntries As String() = Directory.GetFiles(es01Folder)
Dim numberOfEs01Files As Integer = 0
Dim fileName As String = "--"
For Each fileName In fileEntries
numberOfEs01Files += 1
Dim eS01Rdr As StreamReader
eS01Rdr = File.OpenText(fileName)
Dim lineCounter As Integer = 0
Do While eS01Rdr.Peek() >= 0
lineCounter += 1
Dim currentRec As String = eS01Rdr.ReadLine
Dim field() As String = currentRec.Split("|"c)
Dim thisEs01Rec As New Es01
With thisEs01Rec
.cat_Key = field(0)
.cat_Desc = field(1) set category equals this
.subcat_Key = field(2)
.subCatDesc = field(3) search for
.ans_Type = field(4)
.resp_Key = field(5)
.response = field(6)
.vNU_Card = field(7)
.vNU_Col = field(8)
.vNU_Punch = field(9)
.iN_CD_CL = field(10)
.col_Pnch = field(11)
.fWidth = field(12)
.rInd = field(13)
.prop_Codes = field(14)
End With
Es01List.Add(thisEs01Rec)
Loop
eS01Rdr.Close()
Next fileName
End Sub
End Class
-Joni
Continue reading...
Dim bgw As New BackgroundWorker
bgw.WorkerReportsProgress = true
bgw.RunWorkerAsync()
I just removed those lines from my code because they are obviously not correct. Im suppose Im missing some an Imports statement, or a reference to something. I really dont know. A lot of what Ive read says Backgroundworkers are easy to use, I have to disagree. Maybe once one understands how they work, but for me, not so much
Im going to take a shot in the dark here and post my program. I was hoping someone would have the time to show me where and how I can incorporate a backgroundworker/progressbar here. I know its a long shot but, hey, Ive got nothing to loose! Im oozing with desperation right now! lol
P.S. My code may not be the prettiest youve even seen, there are probably better ways to do some of the things Im doing, but it works and does what needs to be done. Dont judge me
Id like for everything that happens from the point that Button1 is clicked till I display a messagebox with the currrent time (messagebox.show("Program completed at " & theTimeIs) in line 394, I believe.TIA!!!
Joanie
Imports System.IO
Imports System.Data
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel
Imports Shell32
Public Class Form1
Private Class dataOutput
this class is used to store all data for output file at the end of the program
Public category As String = "--"
Public prodDesc As String = "--"
Public code As String = "--"
Public clCount As String = "--"
Public clPer As String = "--"
Public bsCount As String = "--"
Public bsPer As String = "--"
Public perPen As String = "--"
Public uIndex As String = "--"
Public wCL_Count As String = "--"
Public wCL_Per As String = "--"
Public wBS_Count As String = "--"
Public wBS_Per As String = "--"
Public wPer_Pen As String = "--"
Public wIndex As String = "--"
End Class
Private Class Es01
Public cat_Key As String = "--"
Public cat_Desc As String = "--"
Public subcat_Key As String = "--"
Public subCatDesc As String = "--"
Public ans_Type As String = "--"
Public resp_Key As String = "--"
Public response As String = "--"
Public vNU_Card As String = "--"
Public vNU_Col As String = "--"
Public vNU_Punch As String = "--"
Public iN_CD_CL As String = "--"
Public col_Pnch As String = "--"
Public fWidth As String = "--"
Public rInd As String = "--"
Public prop_Codes As String = "--"
End Class
Private dataOutputList As New List(Of dataOutput)
Private Es01List As New List(Of Es01)
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim openFileDialog1 As New OpenFileDialog()
openFileDialog1.InitialDirectory = "F:\primeproduction\primenxt\R212\_CSV-AcxiomRpts\_PNextRpts\"
openFileDialog1.InitialDirectory = "c:\Excel\"
openFileDialog1.Filter = "Excel files (*.csv)|*.csv|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True
Dim totalRows As Integer = 0
Dim outputFilename As String = "--"
Dim TextWrtr As StreamWriter
TextWrtr = System.IO.File.CreateText(outputFilename)
If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
txtInput.Text = openFileDialog1.FileName
Me.DataGridView1.DataSource = Nothing
Dim dataTable As System.Data.DataTable
dataTable = importExcelfileToDataTable(openFileDialog1.FileName)
Me.DataGridView1.DataSource = dataTable
Me.TextBox1.Text = dataTable.Rows.Count
Dim totalColumns As Integer = dataTable.Columns.Count
Dim zippedFile As String = "C:\excel\BGWtemp\temp.zip"
Dim tempUnzippedDir As String = "C:\excel\BGWtemp\tempUnzippedFolder"
Dim es01Folder As String = "C:\excel\BGWtemp\tempEs01Folder"
Dim copyZippedFile As String = "C:\excel\test\ab212a.zip"
File.Copy(copyZippedFile, zippedFile, True)
UnZip(tempUnzippedDir, zippedFile)
ListUnzippedFiles(zippedFile)
SpecifyEs01Files(es01Folder, zippedFile)
CreateEs01List(es01Folder)
Dim rowCount As Integer = 0
Dim rowStart As Integer = 0
Dim rowEnd As Integer = 4
Dim rowOne As Integer = 0
Dim rowTwo As Integer = 1
Dim rowThree As Integer = 2
Dim rowFour As Integer = 3
Dim rowFive As Integer = 4
Dim newCategory As Boolean = False
Dim codeCount As Integer = 0
Dim G7 As Integer = dataTable.Rows(4).Item(2) total number of respondents for the whole file
Dim H14 As Integer = 0 the current columns total respondents within a category
Dim G14 As Integer = 0 the current categories total respondents
Dim H7 As Integer = 0 total respondents for that column. Column number could change, but the row always
remains the same. This is the total from the top section for each column
Dim H10 As Integer = 0 the current columns Target population within a category
Dim H13 As Double = 0 the current columns Row % within a category
Dim H3 As Integer = 0 the target population for the entire column. Column number could change, but
the row will always remain the same.
Dim H6 As Double = 0 the Row % for the entire column. Column number could change, but the row
will always remain the same
Dim H11 As Double = 0 the current columns Target % within a category
Dim H12 As Integer = 0 using this just for testing purposed, not needed for calculations
Dim loopedThroughFirstSection As Boolean = False
Dim outputRowCounter As Integer = 0
Dim thisRow As Integer = 0
Dim newCategoryCount As Integer = 0
Dim thisProdDesc As String = "--"
Dim thisProdA As String = "--"
Dim thisProdB As String = "--"
Dim categoryNotApplicable As Boolean = False
For Each rowOfData As DataRow In dataTable.Rows
For j As Integer = 2 To totalColumns - 1
H3 = dataTable.Rows(0).Item(j)
H6 = dataTable.Rows(3).Item(j) * 100 move decimal over to the right by 2 places. Decimal moved left 2 places when read in, I dont know why but Im moving it back
H7 = dataTable.Rows(4).Item(j)
For i As Integer = rowStart To rowEnd
categoryNotApplicable = False
If dataTable.Rows(rowFive).Item(2) = "N/A" Then
categoryNotApplicable = True
Else
G14 = dataTable.Rows(rowFive).Item(2)
Select Case i
Case rowOne
H10 = dataTable.Rows(i).Item(j)
thisProdB = dataTable.Rows(i).Item(0)
Case rowTwo
H11 = dataTable.Rows(i).Item(j)
H11 = H11 * 100 move decimal place over to the right by 2 places.
Input has % symbol and decimal moves left 2 places when reading in, I dont know why so Im just moving it back.
Case rowThree
H12 = dataTable.Rows(i).Item(j)
Case rowFour
H13 = dataTable.Rows(i).Item(j)
H13 = H13 * 100 move decimal place over to the right by 2 places.
Input has % symbol and decimal moves left 2 places when reading in, I dont know why so Im just moving it back.
Case rowFive
H14 = dataTable.Rows(i).Item(j)
If j = (totalColumns - 1) Then
If dataTable.Rows(i + 1).Item(j) Is DBNull.Value Then
If dataTable.Rows(i + 2).Item(j) Is DBNull.Value Then
newCategory = True
End If
End If
End If
End Select
If thisProdB = "Base Total" Then
thisProdA = "nothing base"
Else
If dataTable.Rows(rowStart - 1).Item(0) Is DBNull.Value Then
thisProdA = thisProdA
Else
thisProdA = dataTable.Rows(rowStart - 1).Item(0)
End If
End If
thisProdDesc = thisProdA & ": " & thisProdB
End If
Next
outputRowCounter += 1
If codeCount = 70 Then
loopedThroughFirstSection = True
End If
If loopedThroughFirstSection = True Then
If categoryNotApplicable = False Then
Dim wBsCount As Integer = H3
Dim bsCount As Integer = H7
Dim wClCount As Integer = H10
Dim clCount As Integer = H14
Dim clPer As String = "--"
If H14 = 0 OrElse G14 = 0 Then
clPer = "0"
Else
Dim XclPer As Double = (H14 / G14)
clPer = XclPer.ToString("0.###")
End If
Dim bsPer As String = "--"
If H7 = 0 OrElse G7 = 0 Then
bsPer = "0"
Else
Dim XbsPer As Double = (H7 / G7)
bsPer = XbsPer.ToString("0.###")
End If
Dim perPen As String = "--"
If H14 = 0 OrElse H7 = 0 Then
perPen = "0"
Else
Dim XperPen As Double = (H14 / H7)
perPen = XperPen.ToString("0.###")
End If
Dim uIndex As String = "--"
If H14 = 0 OrElse G14 = 0 OrElse H7 = 0 OrElse G7 = 0 Then
uIndex = "0"
Else
Dim XuIndex As Double = ((H14 / G14) / (H7 / G7) * 100)
uIndex = XuIndex.ToString("0.###")
End If
Dim wClPer As String = "--"
If H13 = 0 Then
wClPer = "0"
Else
Dim XwClPer As Double = (H13 / 100)
wClPer = XwClPer.ToString("0.###")
End If
Dim wBsPer As String = "--"
If H6 = 0 Then
wBsPer = "0"
Else
Dim XwBsPer As Double = (H6 / 100)
wBsPer = XwBsPer.ToString("0.###")
End If
Dim wPerPen As String = "--"
If H11 = 0 Then
wPerPen = "0"
Else
Dim XwPerPen As Double = (H11 / 100)
wPerPen = XwPerPen.ToString("0.###")
End If
Dim wIndex As String = "--"
If H13 = 0 OrElse H6 = 0 Then
wIndex = "0"
Else
Dim XwIndex As Double = (H13 / 100) / (H6 / 100) * 100
wIndex = XwIndex.ToString("0.###")
End If
If codeCount > 0 Then
If newCategoryCount > 0 Then
Dim newCategoryName As String = "--"
GetCategory(thisProdA, newCategoryName)
Dim thisDataOutput As New dataOutput
With thisDataOutput
.category = newCategoryName
.prodDesc = thisProdDesc
.code = codeCount
.clCount = clCount
.clPer = clPer
.bsCount = bsCount
.bsPer = bsPer
.perPen = perPen
.uIndex = uIndex
.wCL_Count = wClCount
.wCL_Per = wClPer
.wBS_Count = wBsCount
.wBS_Per = wBsPer
.wPer_Pen = wPerPen
.wIndex = wIndex
dataOutputList.Add(thisDataOutput)
End With
End If
End If
Else
For i As Integer = rowStart To rowEnd
Select Case i
Case rowOne
thisProdB = dataTable.Rows(i).Item(0)
Case rowFive
If j = (totalColumns - 1) Then
If dataTable.Rows(i + 1).Item(j) Is DBNull.Value Then
If dataTable.Rows(i + 2).Item(j) Is DBNull.Value Then
newCategory = True
End If
End If
End If
End Select
If thisProdB = "Base Total" Then
thisProdA = "nothing base"
Else
If dataTable.Rows(rowStart - 1).Item(0) Is DBNull.Value Then
thisProdA = thisProdA
Else
thisProdA = dataTable.Rows(rowStart - 1).Item(0)
End If
End If
thisProdDesc = thisProdA & ": " & thisProdB
Next
If codeCount > 0 Then
If newCategoryCount > 0 Then
Dim newCategoryName As String = "--"
GetCategory(thisProdA, newCategoryName)
Dim thisDataOutput As New dataOutput
With thisDataOutput
.category = newCategoryName
.prodDesc = thisProdDesc
.code = codeCount
.clCount = "N/A"
.clPer = "N/A"
.bsCount = "N/A"
.bsPer = "N/A"
.perPen = "N/A"
.uIndex = "N/A"
.wCL_Count = "N/A"
.wCL_Per = "N/A"
.wBS_Count = "N/A"
.wBS_Per = "N/A"
.wPer_Pen = "N/A"
.wIndex = "N/A"
dataOutputList.Add(thisDataOutput)
End With
End If
End If
End If
End If
codeCount += 1
Next
rowCount += 1
codeCount = 0
If newCategory = True Then
rowOne += 7
rowTwo += 7
rowThree += 7
rowFour += 7
rowFive += 7
newCategory = False
newCategoryCount += 1
Else
rowOne += 6
rowTwo += 6
rowThree += 6
rowFour += 6
rowFive += 6
End If
rowStart = rowOne
rowEnd = rowFive
If rowEnd > dataTable.Rows.Count Then
Exit For
End If
Next rowOfData
WriteTextFile(outputFilename, TextWrtr)
WriteExcelFile(outputFilename)
TextWrtr.Close()
DeleteTempEs01Folders(tempUnzippedDir, es01Folder, zippedFile)
End If
Dim theTimeIs As String = DateTime.Now
MessageBox.Show("Program completed at " & theTimeIs)
End Sub
Private Sub UnZip(ByVal tempUnzippedDir, ByVal zippedFile)
Dim sc As New Shell32.Shell()
Create directory in which you will unzip your files .
IO.Directory.CreateDirectory(tempUnzippedDir)
Declare the folder where the files will be extracted
Dim output As Shell32.Folder = sc.NameSpace(tempUnzippedDir)
Declare your input zip file as folder .
Dim input As Shell32.Folder = sc.NameSpace(zippedFile)
Extract the files from the zip file using the CopyHere command .
output.CopyHere(input.Items, 4)
End Sub
Private Sub ListUnzippedFiles(ByVal zippedFile)
Dim sc As New Shell32.Shell()
Dim input As Shell32.Folder = sc.NameSpace(zippedFile)
If input.Items.Count > 0 Then
For i = 0 To input.Items.Count - 1
ListBox1.Items.Add(input.Items.Item(i).Path.ToString)
Next
End If
End Sub
Private Sub SpecifyEs01Files(ByVal es01Folder, ByVal zippedFile)
For Each Item In ListBox1.Items
If Item.ToString.Contains(".ES01") Or Item.ToString.Contains(".es01") Then
Dim i As Integer = ListBox1.Items.IndexOf(Item)
Me.Text = i.ToString
Dim sc As New Shell32.Shell()
Create directory in which you will unzip your files .
IO.Directory.CreateDirectory(es01Folder)
Declare the folder where the files will be extracted
Dim output As Shell32.Folder = sc.NameSpace(es01Folder)
Declare your input zip file as folder .
Dim input As Shell32.Folder = sc.NameSpace(zippedFile)
Extract the files from the zip file using the CopyHere command .
output.CopyHere(input.Items(i), 4)
End If
Next
End Sub
Private Sub DeleteTempEs01Folders(ByVal tempUnzippedDir, ByVal es01Folder, ByVal zippedFile)
Dim attribute As System.IO.FileAttributes = FileAttributes.ReadOnly = False
Dim fileEntries As String() = Directory.GetFiles(tempUnzippedDir)
Dim fileEntries2 As String() = Directory.GetFiles(es01Folder)
Dim fileName As String = "--"
For Each fileName In fileEntries
File.SetAttributes(fileName, attribute)
Next fileName
Dim fileName2 As String = "--"
For Each fileName2 In fileEntries2
File.SetAttributes(fileName2, attribute)
Next fileName2
Directory.Delete(tempUnzippedDir, True) deletes directory and all the files within
Directory.Delete(es01Folder, True)
System.IO.File.Delete(zippedFile)
End Sub
Private Sub GetCategory(ByVal descToSearchFor, ByRef categoryToSet)
Dim resultsFound As Integer = 0
Dim matchSwitch As Boolean = False
Dim searchForLength As Integer = descToSearchFor.length
Dim es01Search = From e As Es01 In Es01List Where e.subCatDesc Like descToSearchFor
For Each e As Es01 In es01Search
If descToSearchFor Like e.subCatDesc Then
categoryToSet = e.cat_Desc
resultsFound += 1
End If
If resultsFound > 0 Then
matchSwitch = True
Exit For
End If
Next
Do While matchSwitch = False
Dim s As String = descToSearchFor
Dim sLength As Integer = s.Length
Dim newStringToSearch As String = "--"
Dim newStringLength As Integer = sLength
Dim i As Integer = 1
resultsFound = 0
Dim es01Search2
For Each character As Char In s
If newStringLength = 1 Then
categoryToSet = "CATEGORY NOT FOUND!!!!!"
matchSwitch = True
Exit For
Else
newStringToSearch = s.Substring(0, s.Length - i)
es01Search2 = From e2 As Es01 In Es01List Where e2.subCatDesc Like newStringToSearch
For Each e2 As Es01 In es01Search2
If newStringToSearch = e2.subCatDesc Then
categoryToSet = e2.cat_Desc
resultsFound += 1
End If
If resultsFound > 0 Then
matchSwitch = True
Exit Do
End If
Next
End If
newStringLength = newStringToSearch.Length
i += 1
Next
Loop
If resultsFound = 0 Then
categoryToSet = "CATEGORY NOT FOUND!!!"
End If
End Sub
Private Sub WriteTextFile(ByVal outputFilename, ByRef TextWrtr)
outputFilename = "C:\excel\BAckGroundWorkerTest.txt"
If File.Exists(outputFilename) Then
File.Delete(outputFilename)
End If
TextWrtr = System.IO.File.CreateText(outputFilename)
Dim lineOfText As String = "--"
Dim lineCounter As Integer = 0
Dim headers As String = "--"
Dim FinalOutput = From L As dataOutput In dataOutputList
For Each L As dataOutput In FinalOutput
If lineCounter = 0 Then
headers = "Category" & vbTab & "Prod_Desc" & vbTab & "Code" & vbTab & "Cl_Count" & vbTab & _
"Cl_Per" & vbTab & "Bs_Count" & vbTab & "Bs_Per" & vbTab & "Per_Pen" & vbTab & "U_INdex" & _
vbTab & "W_CL_Count" & vbTab & "W_Cl_Per" & vbTab & "W_Bs_Count" & vbTab & "W_Bs_Per" & _
vbTab & "W_Per_Pen" & vbTab & "W_Index"
TextWrtr.WriteLine(headers)
End If
lineOfText = L.category & vbTab & L.prodDesc & vbTab & L.code & vbTab & L.clCount & vbTab & L.clPer & _
vbTab & L.bsCount & vbTab & L.bsPer & vbTab & L.perPen & vbTab & L.uIndex & vbTab & L.wCL_Count & _
vbTab & L.wCL_Per & vbTab & L.wBS_Count & vbTab & L.wBS_Per & vbTab & L.wPer_Pen & vbTab & L.wIndex
TextWrtr.WriteLine(lineOfText)
lineCounter += 1
Next
End Sub
Private Sub WriteExcelFile(ByVal outputFilename)
Try
~~> Define your Excel Objects
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xlRange As Excel.Range
outputFilename = "C:\excel\CSV_OUTPUT_A1_ColumnWidth.xlsx"
If File.Exists(outputFilename) Then
File.Delete(outputFilename)
End If
~~> Add a New Workbook
xlWorkBook = xlApp.Workbooks.Add
~~> Display Excel
xlApp.Visible = True
~~> Set the relebant sheet that we want to work with
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
Set column widths
xlWorkSheet.Columns("A:B").ColumnWidth = 41
xlWorkSheet.Columns("C").columnwidth = 6
xlWorkSheet.Columns("D:O").columnwidth = 12
With xlWorkSheet
.Range("A1").Value = "Category"
.Range("B1").Value = "Prod_Desc"
.Range("C1").Value = "Code"
.Range("D1").Value = "CL_Count"
.Range("E1").Value = "CL_Per"
.Range("F1").Value = "Bs_Count"
.Range("G1").Value = "Bs_Per"
.Range("H1").Value = "Per_pen"
.Range("I1").Value = "U_Index"
.Range("J1").Value = "W_CL_Count"
.Range("K1").Value = "W_CL_Per"
.Range("L1").Value = "W_Bs_Count"
.Range("M1").Value = "W_Bs_Per"
.Range("N1").Value = "W_Per_pen"
.Range("O1").Value = "W_Index"
Dim thisRow As Integer = 2
Dim FinalOutput = From L As dataOutput In dataOutputList
For Each L As dataOutput In FinalOutput
Dim aCell As String = "A" & thisRow
Dim bCell As String = "B" & thisRow
Dim cCell As String = "C" & thisRow
Dim dCell As String = "D" & thisRow
Dim eCell As String = "E" & thisRow
Dim fCell As String = "F" & thisRow
Dim gCell As String = "G" & thisRow
Dim hCell As String = "H" & thisRow
Dim iCell As String = "I" & thisRow
Dim jCell As String = "J" & thisRow
Dim kCell As String = "K" & thisRow
Dim lCell As String = "L" & thisRow
Dim mCell As String = "M" & thisRow
Dim nCell As String = "N" & thisRow
Dim oCell As String = "O" & thisRow
.Range(aCell).Value = L.category
.Range(bCell).Value = L.prodDesc
.Range(cCell).Value = L.code
.Range(dCell).Value = L.clCount
.Range(eCell).Value = L.clPer
.Range(fCell).Value = L.bsCount
.Range(gCell).Value = L.bsPer
.Range(hCell).Value = L.perPen
.Range(iCell).Value = L.uIndex
.Range(jCell).Value = L.wCL_Count
.Range(kCell).Value = L.wCL_Per
.Range(lCell).Value = L.wBS_Count
.Range(mCell).Value = L.wBS_Per
.Range(nCell).Value = L.wPer_Pen
.Range(oCell).Value = L.wIndex
thisRow += 1
Next
End With
xlWorkBook.SaveAs(outputFilename)
xlWorkBook.Close()
xlApp = Nothing
xlRange = Nothing
xlWorkSheet = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Public Function importExcelfileToDataTable(ByVal xlsFileFullName As String) As System.Data.DataTable
Dim returnDT As New System.Data.DataTable()
If File.Exists(xlsFileFullName) Then
Microsoft.Office.Interop.Excel
Create new Application.
Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
// Open Excel Workbook
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(xlsFileFullName)
//Get sheet the first work sheet Get sheet, if not found return null;
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = xlWorkBook.Worksheets(1) get the first worksheet.
//Get range
Dim xlRange As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.UsedRange
//Load all cels into Array
Dim arrayRanges(,) As Object = xlRange.Value This line loads the range of the excel file into an array
xlWorkBook.Close()
xlApp = Nothing
xlRange = Nothing
xlWorkSheet = Nothing
//** import into Datatable by converting the object array to a table
returnDT = convertToDataTable(arrayRanges)
End If
Return returnDT
End Function
Public Function convertToDataTable(ByVal array As Object(,)) As System.Data.DataTable
Dim row As Integer
Dim column As Integer
Dim intRows As Integer
Dim intCols As Integer
Dim dataTable As New System.Data.DataTable()
Dim dRow As DataRow
intRows = array.GetLength(0)
If intRows <> 0 Then
If intRows <> 0 Then
intCols = array.GetLength(1)
If intCols <> 0 Then
For column = 1 To intCols
trim the column if has leading spaces
Dim holdValue As Object = array(6, column)
Dim holdString As [String]
If holdValue IsNot Nothing Then
holdString = DirectCast(holdValue, [String])
holdString = holdString.Trim()
Else
assign column name if column blank
holdString = "No_Name_Col" & column.ToString()
End If
dataTable.Columns.Add(New DataColumn(holdString))
Next
Skip the first few rows, that should be used for the column heading
For row = 7 To intRows
dRow = dataTable.NewRow()
For column = 1 To intCols
need to assign row by column name so do the following
trim the column if has leading spaces
Dim holdValue As Object = array(6, column)
Dim holdString As [String]
If holdValue IsNot Nothing Then
holdString = DirectCast(holdValue, [String])
holdString = holdString.Trim()
Else
assign column name if column blank
holdString = "No_Name_Col" & column.ToString()
End If
dRow(holdString) = array(row, column)
Next
dataTable.Rows.Add(dRow)
Next
End If
End If
End If
dRow = Nothing
Return dataTable
End Function
Public Sub CreateEs01List(ByVal es01Folder)
Dim fileEntries As String() = Directory.GetFiles(es01Folder)
Dim numberOfEs01Files As Integer = 0
Dim fileName As String = "--"
For Each fileName In fileEntries
numberOfEs01Files += 1
Dim eS01Rdr As StreamReader
eS01Rdr = File.OpenText(fileName)
Dim lineCounter As Integer = 0
Do While eS01Rdr.Peek() >= 0
lineCounter += 1
Dim currentRec As String = eS01Rdr.ReadLine
Dim field() As String = currentRec.Split("|"c)
Dim thisEs01Rec As New Es01
With thisEs01Rec
.cat_Key = field(0)
.cat_Desc = field(1) set category equals this
.subcat_Key = field(2)
.subCatDesc = field(3) search for
.ans_Type = field(4)
.resp_Key = field(5)
.response = field(6)
.vNU_Card = field(7)
.vNU_Col = field(8)
.vNU_Punch = field(9)
.iN_CD_CL = field(10)
.col_Pnch = field(11)
.fWidth = field(12)
.rInd = field(13)
.prop_Codes = field(14)
End With
Es01List.Add(thisEs01Rec)
Loop
eS01Rdr.Close()
Next fileName
End Sub
End Class
-Joni
Continue reading...
