This is a VBA macro in Microsoft Excel that uses the ADODB library to connect to a SQL Server database and insert data from a range of cells in the worksheet into a table named “Product” in the database.
The macro first declares a new ADODB.Connection object and sets its properties to connect to the database using the SQL Server driver, server name, database name, and login credentials. Then, it declares a Range object and sets it to a specific range of cells in the worksheet. For each row in the range, the macro retrieves the values from the four cells and concatenates them into an SQL INSERT statement that inserts a new row into the “Product” table with the values from the cells. The macro executes the INSERT statement using the ADODB.Connection object’s Execute method, and then moves on to the next row in the range. After all the rows have been processed, the macro closes the connection to the database and displays a message box indicating that the operation is complete.
Sub Button1_Click()
This line defines a new sub-procedure named "Button1_Click", which will be executed when the user clicks on a button in the Excel worksheet that is associated with this macro.
Dim con As ADODB.Connection
This line declares a new variable named "con" of type "ADODB.Connection", which will be used to establish a connection to the SQL Server database.
Set con = New ADODB.Connection
This line creates a new instance of the ADODB.Connection object and assigns it to the "con" variable.
con.Open "driver={sql server};server=DESKTOP-C6A2SGL\IT;database=hs;uid=admin;pwd=admin123;"
This line uses the "Open" method of the ADODB.Connection object to establish a connection to the SQL Server database. The connection string specifies the SQL Server driver, server name, database name, and login credentials needed to access the database.
Dim rng As Range: Set rng = Application.Range("A2:D7")
This line declares a new variable named "rng" of type "Range", which will be used to define the range of cells in the worksheet that contains the data to be inserted into the database. The "Set" keyword initializes the "rng" variable with the range of cells A2:D7 using the Application object's "Range" method.
Dim row As Range
This line declares a new variable named "row" of type "Range", which will be used to represent each row in the "rng" range of cells.
For Each row In rng.Rows
This line starts a loop that will iterate over each row in the "rng" range of cells, assigning each row to the "row" variable.
ID = row.Cells(1).Value
Name = row.Cells(2).Value
qty = row.Cells(3).Value
Price = row.Cells(4).Value
These lines retrieve the values from each of the four cells in the current row of the "rng" range and assign them to variables named "ID", "Name", "qty", and "Price", respectively.
Sql = "insert into Product values(" & ID & ",'" & Name & "' , " & qty & "," & Price & " )"
This line creates an SQL INSERT statement that inserts a new row into the "Product" table in the database with the values retrieved from the cells in the current row. The values are concatenated into the INSERT statement as strings using the & operator.
con.Execute Sql
This line uses the "Execute" method of the ADODB.Connection object to execute the SQL INSERT statement on the database.
Next row
This line ends the current iteration of the loop and moves on to the next row in the "rng" range of cells.
con.Close
This line uses the "Close" method of the ADODB.Connection object to close the connection to the database.
MsgBox "Complete"
the Download Button is not visible.