dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Loading Excel data into VB .net combo boxes..

  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Loading Excel data into VB .net combo boxes..

    Hello,
    First let me start by saying this is a great place for knowledge. I've spent the last 2 or 3 days reading through many posts. Learning tons from the help of everyone's posts.

    I've seen several posts with the topic of loading excel data into text and combo boxes.. and I think im starting to grasp the idea.. but because im trying to pick up Visual basic on my own with no friends/family that have any programming experience.. I'm struggling (surprise surprise)


    Heres what I'm trying to do...

    I have 1 combo box, It will list product names.. Lets say 15 of them.. (I know 15 i could just as easily specify them individually within VB.. but lets assume I NEED to read them from an excel file.(Excel 2003)

    Part 1 :
    Really all i need to know for now, is how do I load an excel worksheet into VB so I can call data up from the cells / rows / columns..

    Part 2 : How do I define Which Cells from Each row or column I want to populate into the combo box... Presuming for the "Product name" combobox I have placed each of the 15 product names in the excel file under Row 1, column A through Row 15 , Column A.


    If anyone could give me a sturdy code to base this off of I would really appreciate it.

    I've seen so many different posts that suggest loading the xls file into SQL to run queries on etc.. I want to avoid this and just call the xls file into VB. (Excel will be installed on any computer running this application if that matters. )

    any help is greatly appreciated!

    -Blair

  2. #2
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    Re: Loading Excel data into VB .net combo boxes..

    May not be a direct answer to your question. But may give you an idea on how to work with excel. This code will write to excel sheet when a button is pressed. The data written is the datagrid values
    Code:
    Private Sub btnexcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexcel.Click
    
            Try
    
          
                Dim excel As Excel.Application
                Dim xWorkBook As Excel.Workbook
                Dim xWorkSheet As Excel.Worksheet
                Dim misValue As Object = System.Reflection.Missing.Value
                Dim row As Integer
                Dim col As Integer
    
    
                excel = New Excel.ApplicationClass
                xWorkBook = excel.Workbooks.Add(misValue)
                xWorkSheet = xWorkBook.Worksheets.Add
    
                xWorkSheet.Name = cboselection.Text & "" & cboyearpay.Text
    
                For row = 0 To DataGrid.Rows.Count - 1
    
                    For col = 0 To DataGrid.Columns.Count - 1
    
                        xWorkSheet.Cells(4, col + 2) = DataGrid.Columns.Item(col).HeaderText
                        xWorkSheet.Cells(row + 5, col + 2) = DataGrid(col, row).Value.ToString()
                        xWorkSheet.Columns.AutoFit()
                    Next
                Next
    
                xWorkSheet.SaveAs("d:\Rent" & cboselection.Text & "" & cboyearpay.Text)
                excel.Quit()
    
                'release all objects  
                releaseobject(excel)
                releaseobject(xWorkBook)
                releaseobject(xWorkSheet)
                Me.Close()
            Catch ex As Exception
                MsgBox("check whether Excel is installed in the machine ", MsgBoxStyle.Critical)
                'MsgBox(ex.Message)
                Me.Close()
            End Try
        End Sub
        'Release objects
        Private Sub releaseobject(ByVal obj As Object)
            Try
                system.runtime.interopservices.marshal.releasecomobject(obj)
                obj = Nothing
            Catch ex As exception
                obj = Nothing
            Finally
                gc.collect()
            End Try
        End Sub

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)