Openfiledialoge1 loads excel file and adds it line by line into the excelnames listbox.
I have two list boxes: medianames which are the names provided, and DatabaseName which has our list of names.
The if statement should check the list of names in excelnames, and if there is a match in the "medianames" it replaces it with the respective index from "databasenames"
The last piece compares this excelnames list (now updated) to a master list (mlnames), and adds names to another listbox (namesleftlb) if the excelnames item is NOT found in the master list.
excelnames.Items.Clear() 'opening file With OpenFileDialog1 .ShowReadOnly = False .Filter = "All Files|*.*|Excel Files (*)|*;*.xls;*.xlsx" .FilterIndex = 2 If .ShowDialog = DialogResult.OK Then APP1 = New Excel.Application workbook1 = APP1.Workbooks.Open(.FileName) worksheet1 = workbook1.Worksheets("sheet1") End If End With 'adding contents of excel to excelnames listbox (i think the problem stems from here) Dim objRange As Excel.Range For intLoopCounter = 1 To CInt(worksheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row) objRange = worksheet1.Range("A" & intLoopCounter) excelnames.Items.Add(objRange.Value.Trim.ToUpper()) Next intLoopCounter 'replacing names in excelnames with comparison rule Dim i As Integer Dim az As Integer For i = 0 To MediaName.Items.Count() - 1 az = excelnames.FindStringExact(MediaName.Items(i)) If az > -1 Then Dim strinput As String strinput = DatabaseName.Items(i) excelnames.Items.RemoveAt(excelnames.FindStringExact(MediaName.Items(i))) excelnames.Items.Insert(0, strinput) End If Next 'comparing excelnames to masterlist namesleftlb.Items.Clear() namesleftlb.Items.AddRange(excelnames.Items.Cast(Of String).Except(mlnames.Items.Cast(Of String)).ToArray) namesleftlb.SelectedIndex = 0
I ran a hard coded list through the paces and the replacement if statement worked, I'm assuming I have something loaded as a string array or something when I pull the file from excel but this part is beyond me and would appreciate suggestions. Thanks