INSERT INTO EXCEL AS DB FROM VISUAL BASIC FORM

Question

I want to put textbox data from userform of visual basic to excel (Sheet1) as Database. I am using Excel 2016 and tool is Visual studio 2013 pro. I have created these form like:

and the code is:

       Imports System.Data.OleDb
       Imports System.IO
       Public Class Form2

   Dim cn As OleDbConnection
   Dim cm As OleDbCommand


Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Const path As String = "C:\\Users\\echat\\Desktop\\Book2.xls"
    ' Const connstring As String = "Provider==Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;HDR=YES;"""
    ' cn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=C:\Connect To Excel\Book2.xls;extended properties=excel 5.0;"
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\Connect To Excel\Book2.xls;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    cn.Open()

    With cm
        .Connection = cn
        .CommandText = "Insert into [Sheet1]values ('" & TextBox1.Text & "','" & TextBox2.Text & "') "
        .ExecuteNonQuery()

    End With

    cn.Close()
    MsgBox("Success", MsgBoxStyle.Information, Text)

End Sub
End Class

I have tried but its giving error in these line: Can Anybody can rectify it?

System.NullReferenceException: Object reference not set to an instance of an object.

I havenot said Excel is Database!!!... I have remarked that "Excel As Database" — Eshan Chattaraj, Jan 08 '18 at 06:27

score 2 · Accepted Answer · answered Jan 05 '18 at 13:37

2

You have not initialized your connection object.

Try this:

   Dim cn As new OleDbConnection
   Dim cm As new OleDbCommand

answered Jan 05 '18 at 13:37

Sasha

1,674
1
16
23

This should solve the problem. But OP should be aware that he should limit the scope of the connection to only where it is used. i.e. declare and dispose of it inside of `Button2_Click`, and any other method using it. – djv Jan 05 '18 at 15:46
I have already declared " Dim cn As new OleDbConnection Dim cm As new OleDbCommand" onto Button2_Click event but it still giving same problem and error – Eshan Chattaraj Jan 08 '18 at 06:30
@EshanChattaraj in the code you provided you havent – Sasha Jan 08 '18 at 09:02

INSERT INTO EXCEL AS DB FROM VISUAL BASIC FORM

1 Answers1