-
October 31st, 2009, 09:32 AM
#1
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
-
November 1st, 2009, 12:48 AM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|