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.
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
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 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
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
I can see this video was planned out very well and presented very well by someone who is knowledgeable and genuine. Thanks for sharing 👍👍
Thank you! Stay connected with Exceldemy for more helpful content! 🎉❤
Regards,
Exceldemy Team!
Would it be possible to do this with multiple websites and make one big database?
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.
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
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.
@@exceldemy2006 oh okay do u have a video tutorial for that? Thanks for answering my question
@@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
@@exceldemy2006 thank you very much 😊 🙏
@@amruthacv2432 Dear, you are most welcome. Stay connected to ExcelDemy.