10 replies to this topic

[bahrain]

Hello Guys

I'v 2 Questions and I'll appreciate your help..


How can I use the open file dialog??
and how can I import information from Excel ??

I'm using VB.Net 2008


thanx in advance

[Xav]

As to your first question:

• Add the OpenFileDialog from the toolbox to your project.
  
• Give it a name (such as dlgOpen or something)
  
• Use the following code when you want to open it:
  

  If dlgOpen.ShowDialog() = DialogResult.OK Then
    Dim fileName As String = dlgOpen.FileName
  End If
  

As to the second question, it depends on what you want to import. Assuming it is just data, you need the data exported as a CSV file. Then you can read it in using System.IO.

If you need more than just data, you can automate Excel by adding a COM reference and manipulating the API.

[vizhin]

Hi,I have quickly put something together I hope will help you upload data from excel. You need to have reference to Excel Automation Library for the code to work.

Private Sub Upload()

Dim myDataset As New DataSet()


Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " + "C:\Uploads\" + FileName + ";" & _
"Extended Properties=""Excel 8.0;"""

''You must use the $ after the object you reference in the spreadsheet. In this example its sheet1$
Dim myData As New OleDbDataAdapter("SELECT * FROM [sheet1$]", strConn)


myData.TableMappings.Add("Table", "ExcelTest")
myData.Fill(myDataset)

'now the data is in the dataset u can view in grid or save to db

End Sub

[bahrain]

Thanx Xav , vizhin

Can You Explain More pleas..

how can I add reference to Excel Automation Library ??

should the user add $ after all the sheets? .

and if there are more than one sheet would the code work ?

[vizhin]

Hi

Q)How can I add reference to Excel Automation Library ??

A) - right click your project and click "Add References"
- goto the "COM" tab
- look for "Microsoft Excel 11.0 Object library" and double click it
(am using office 2003 and could be different depending on your version)
-the refrence should appear under "References"


Q) Should the user add $ after all the sheets?

A) No you dont have to add "$" on all sheets. You only append "$" to the sheet name in code.

Q) If there are more than one sheet would the code work ?

A) The code will work. However you have to work on each sheet seperately i.e.
Dim mData1 As New OleDbDataAdapter("SELECT * FROM [sheet1$]", strConn)

Dim mData2 As New OleDbDataAdapter("SELECT * FROM [sheet2$]", strConn)

Dim mData3 As New OleDbDataAdapter("SELECT * FROM [sheet3$]", strConn)

Assuming that that your book has sheets : Sheet1,Sheet2 and Sheet3

Regards

[bahrain]

I added The Reference It's not working

I also added
Imports Microsoft.Office.Interop.Excel

and nothing yet !!

What should I do ??

[Xav]

What is the error message you are getting, if any?

[bahrain]

Type 'OleDbDataAdapter' is not defined

This is the error...

[vizhin]

hi

You have to import the following workspace.

-------------------------------------------------------------------------
Imports System.Data.OleDb

[bahrain]

Thanx guys I Found the solution . :)

[Xav]

If you do not wish to import a namespace, you can simply refer to the class by putting the namespace, then a period (.) followed by the class.