How to Create a Database That Updates Automatically in Excel

[@blakearndt3771]

I can see this video was planned out very well and presented very well by someone who is knowledgeable and genuine. Thanks for sharing 👍👍

[@exceldemy2006]

Thank you! Stay connected with Exceldemy for more helpful content! 🎉❤
Regards,
Exceldemy Team!

[@amruthacv2432]

Method 3 is directly connected to the database right? . I am working on a project where the sql data is formatted to excel and if any changes are made in that then it should also reflect in the database

[@exceldemy2006]

Dear, method 3 describes how to automatically update a pivot table, which represents data in Excel, using VBA code. It does not directly connect to an external database like SQL. To achieve this, VBA must establish a connection to the SQL database and execute SQL commands to update the database when changes are made in Excel.

[@amruthacv2432]

@@exceldemy2006 oh okay do u have a video tutorial for that? Thanks for answering my question

[@exceldemy2006]

@@amruthacv2432 Dear, you are welcome. We will definitely try to create a tutorial on connecting to a database and updating data using Excel VBA. We are delighted to inform you that we have demonstrated a situation in which you make changes in Excel and the changes are reflected in the Database.
Please check the following SOLUTION Overview: www.exceldemy.com/wp-content/uploads/2024/04/Connecting-to-MS-SQL-Server-And-Update-DATABASE-Using-Excel-VBA.gif
You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/04/Amrutha-C-V-SOLVED.xlsm
Necessary Excel VBA Code in Sheet Module:
Public oldValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldValue = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
Dim newValue As Variant

Dim rowFinderME As String
Dim rowFinderWS As String

Set rng = Me.Range("A:D")

If Not Intersect(Target, rng) Is Nothing Then

Application.EnableEvents = False

If Target.Column = 1 Then

MsgBox "The ID column is considered as the primary key.", vbCritical

Me.Cells(Target.Row, Target.Column).Value = oldValue

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Dim cmd As New ADODB.Command

Dim serverName As String
Dim databaseName As String

Dim SQL As String

serverName = "DESKTOP-94N3HCQ\SQLEXPRESS01"
databaseName = "ProductInformation"

conn.ConnectionString = "driver={SQL Server};server=" & serverName & ";database=" & databaseName

conn.ConnectionTimeout = 100
conn.Open
conn.Close

ElseIf Target.Column = 2 Then
ConnMsSQLDatabaseAndUpdate "Name", Target.Value, Target.Offset(0, -1).Value

ElseIf Target.Column = 3 Then
ConnMsSQLDatabaseAndUpdate "Price", Target.Value, Target.Offset(0, -2).Value

ElseIf Target.Column = 4 Then
ConnMsSQLDatabaseAndUpdate "Quantity", Target.Value, Target.Offset(0, -3).Value

End If

Application.EnableEvents = True

End If
End Sub
Public Sub ConnMsSQLDatabaseAndUpdate(FieldToUpdate As String, ValueToUpdate As Variant, ID As Integer)
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim cmd As New ADODB.Command
Dim serverName As String
Dim databaseName As String
Dim SQL As String
serverName = "DESKTOP-94N3HCQ\SQLEXPRESS01"
databaseName = "ProductInformation"
conn.ConnectionString = "driver={SQL Server};server=" & serverName & ";database=" & databaseName
conn.ConnectionTimeout = 100
conn.Open
If conn.State = 1 Then
MsgBox "Database is connected!", vbInformation
End If
SQL = "Update [ProductInformation].[dbo].Product Set " & FieldToUpdate & "='" & ValueToUpdate & "' Where ID='" & ID & "';"
conn.Execute SQL
conn.Close

MsgBox "The data has been updated.", vbInformation
End Sub

[@amruthacv2432]

@@exceldemy2006 thank you very much 😊 🙏

[@exceldemy2006]

​@@amruthacv2432 Dear, you are most welcome. Stay connected to ExcelDemy.

[@GinaEscalante-th4wi]

Would it be possible to do this with multiple websites and make one big database?

[@exceldemy2006]

Dear, Thanks for your question! The answer is YES. You can combine data from multiple websites into one extensive database in Excel that updates automatically.
To do so, use the From Web option under the Data tab to connect to each website and load the data into new sheets. Clean the data using the Power Query Editor. Then, use the Append Queries feature to combine all the tables. Import the combined data into your worksheet. To ensure the database updates automatically, enable the auto-update feature by setting a refresh interval in the Connection Properties.