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.