dcsimg
Results 1 to 3 of 3

Thread: Need another pair of eyes: editing listbox through if statement

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2012
    Posts
    42

    Need another pair of eyes: editing listbox through if statement

    Here's what i'm dealing with:

    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.

    Code:
            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

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,902

    Re: Need another pair of eyes: editing listbox through if statement

    Is it not working, and if so, in what way? You stated that you ran a hard coded list through the paces and it worked. From that I would guess that it isn't working in practice, but I'm not sure if it is the replacemnt if statement that doesn't work, or something else.

    Have you put a breakpoint in the code and stepped through it to see what it is doing?
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2012
    Posts
    42

    Re: Need another pair of eyes: editing listbox through if statement

    Doh, typed a response and forgot to push submit: as it turns out I was missing at some point a bunch of spaces were added to my lines. Nothing some trim's couldn't fix and it is working fine. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width