Import different excels file in vb.net

Question

0.00/5 (No votes)

See more:

I am little bit confusing to save the different name excel file .......I want to import data from excel file in vb.net and save in the database ....I am import my excel file and save data too also.But my problem is that i don't want to fix excel file name or sheet name .Please someone help me

my code is there:

VB

Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
    If validate_data() Then
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCom As System.Data.OleDb.OleDbDataAdapter

        Dim fileBrowse As New OpenFileDialog
        With fileBrowse
            .Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Import data from Excel file"
        End With
        If fileBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim fname As String
            fname = fileBrowse.FileName
            DtSet = New System.Data.DataSet
            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")



            MyCom = New System.Data.OleDb.OleDbDataAdapter("select * from [stulist.xlsx$]", MyConnection)
            MyCom.TableMappings.Add("Table", "Student_Master")
            DtSet = New System.Data.DataSet
            MyCom.Fill(DtSet)
            MyConnection.Close()
            For Each row As DataRow In DtSet.Tables(0).Rows
                obj.InsertData("INSERT INTO Student_Master(Student_Name, Student_Rollno, Student_Enrollment) VALUES ('" & row(0).ToString & "','" & row(1).ToString & "','" & row(2).ToString & "')")
            Next
            MsgBox("Imported Item Successfully Saved")

        End If
    End If
End Sub

and in this program i have fix excel file name in the name of "stulist"but actually i dont want to fix excel file name

Posted 21-Feb-14 0:58am

neha from Allahabad

Add a Solution

Comments

S Houghtelin 21-Feb-14 7:34am

Don't want to be rude, but I don't understand what you mean by "fix", are you saying that you don't want to create a duplicate file?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-02-23T11:04:00

2 notes:
1) bad connection string:
VB

MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")

Please, refer this: http://www.connectionstrings.com/excel/[^]
If you would like to connect to MS Excel 2007 (and higher) file, please use:

CSS

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties="Excel 12.0 Xml;HDR=YES";

For MS Excel 97-2003, please use:

CSS

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls; Extended Properties="Excel 8.0;HDR=Yes;

2) bad command:
CSS

select * from [stulist.xlsx$]

Your command should refer to existing sheet:

CSS

select * from [Sheet1$]

So, if you would like to dynamically connect to any Excel file, you should list all sheets in a workbook[^] first.

By The Way: have a look at my tip: How to: Get Data from Multiple Workbooks using One OleDbConnection?[^]

dpokiya · Answer 2 · 2014-02-24T17:19:00

VB

Private Sub btnbrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbrowse.Click
        Txtnumber.Text = ""

        Dim fBrowse As New OpenFileDialog
        With fBrowse
            .InitialDirectory = "c:\"
            .Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Import data from Excel file"

            Dim answ = .ShowDialog

            If answ = DialogResult.OK Then 'if answer not cancel, etc..
                path = System.IO.Path.GetFullPath(fBrowse.FileName) 'picterebox imagelocation = dlg_openfile.filename
            End If
        End With
    End Sub

sohail awr · Answer 3 · 2014-02-21T05:21:00

Solution 1

Please try this..

VB

MyCom = New System.Data.OleDb.OleDbDataAdapter("select * from [" & fname & "]", MyConnection)

Posted 21-Feb-14 5:21am

sohail awr

Import different excels file in vb.net

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0