How to Import data from excel spreadsheet vb.net?

[sasasasa]

Hello
I am trying to import data from an excel file but the spreadsheet name is causing problem. If I use 'Sheet1' as the name of the spreadsheet from which I want data, then it works fine but I want to change the name of the spreadsheet to the name which has spaces like 'PROJECT SHEET'. Please guide

 Dim ExcelFile As String = "C:\ProjectFile.xlsx"
Dim myDatabasePath as string = "C:\ProjectsDatabase.mdb"

        Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=""Excel 12.0 Xml;HRD=NO"""

        Dim excelConnection As New OleDbConnection(excelConnectionString)
        excelConnection.Open()

        Dim strSQL As String


        strSQL = "SELECT * INTO [MS Access;Database=" + myDatabasePath + "].tblttt " & _
                 "FROM (PROJECT SHEET$B1:E6)" '''<==== 'Problem'

        Dim cmd1 As New OleDbCommand(strSQL, excelConnection)
  cmd1.ExecuteNonQuery()
        cmd1.Dispose()

Please help me.

[sasasasa]

Please reply

[yarbrough40]

have you tried putting it in quotes?
"""PROJECT SHEET"""

you also might try referring to the sheet by its index
Sheets(1)

[sasasasa]

Thanks for your reply. Quotes work, I tried so many things - different brackets, no brackets, changing name but didn't try quotes. It works, thank you so much for your help.

[sasasasa]

How can I update an existing excel files? I want to change certain values in the excel file and I am using

Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xlsx;Extended Properties=""Excel 12.0 Xml;HRD=No"""
        Dim excelConnection As New OleDbConnection(excelConnectionString)
        excelConnection.Open()
Dim strsql As String =  = "UPDATE [Sheet1$A3:A3] SET F1 = 'Test'"
 Dim cmd1 As New OleDbCommand(strsql, excelConnection)
            cmd1.ExecuteNonQuery()
            excelConnection.Close()

It gives me the error saying that there is a requirement of one or more parameters. What should I do? Please help.

[sasasasa]

Please help

[sasasasa]

Please Reply....

[yarbrough40]

One error I see is that you have two "=" signs on line 5. Besides that
can you give more detail? What exactly is requiring a parameter? What is the exact error? Is it a runtime error or is it compilation error?

[sasasasa]

Thanks for your reply
I am sorry two "=" is just typing mistake.
This is the actual error
"No value given for one or more required parameters."

This line is giving error
cmd1.ExecuteNon Query()

It is a runtime error

Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xlsx;Extended Properties=""Excel 12.0 Xml;HRD=No"""  
        Dim excelConnection As New OleDbConnection(excelConnectionString)  
        excelConnection.Open()  
Dim strsql As String = "UPDATE [Sheet1$A3:A3] SET F1 = 'Test'"  
 Dim cmd1 As New OleDbCommand(strsql, excelConnection)  
            cmd1.ExecuteNonQuery()  '<<<-----Error
            excelConnection.Close()

[yarbrough40]

Your code syntax looks fine to me. The only weird thing is the ACE.OLEDB driver you are using. I am not familiar with it. I have always used Microsoft.Jet.O LEDB.4.0. Maybe give that a try.

Have you tried a simple select statement to see if you can retrieve the value of a cell. If it succeeds then you'll know it has something to do with your sql syntax. If it fails then it's probably a connection configuration problem depending on the error.

good luck!

[sasasasa]

ACE.OLEDB driver is for .xlsx files and Microsoft.Jet.O LEDB.4.0 is for .xls
Select statement works fine. Even the insert works too. But when I go an update the cell which has value, it gives me an error. The problem that I have is I do not have any headers in the excel sheet so I can't reference it like that.

[yarbrough40]

ok then the problem is obviously the syntax of the update statement. So at least this should give you a tip on Googling for the solution if you are unable to find success here. Sorry I can't help further.

[sasasasa]

No problem
Thanks anyway for the help you provided.