Editing an SQL Server table in Excel - SQL Server Wiki - SQL Server - Toad World

Editing an SQL Server table in Excel

SQL Server Community

Editing an SQL Server table in Excel

Anyone who lives in Microsoft Excel every day will get comfortable in that environment. This combined with the increasingly powerful toolset means you will be tempted to use Excel for much more than simple spreadsheets. How would you like to edit your database informationright from within Excel? In this article I would like to show you a neat way to edit SQL server data using Excel and ODBC!

Now, the immediate solution that comes to mind is to use an Excel sheet as an external Table in the SQL Server. This means that:

  1. The Excel sheet must be on the server
  2. You need a version of SQL Server and Excel that support this feature, along with the appropriate driver
  3. You need an agreeable DBA to allow this!

This, unfortunately, is not usually acceptable for a number of reasons.

Instead, here's a solution that allows editing a table within Excel that does not have these limitations and is likely to be more DBA-friendly! Editing SQL Data Using Microsoft Excel

If you want to follow along with this article we have provided an example to download called Update SQLServer.xlsm. It is an Excel file in which each sheet can be set to reflect an SQL Server's table.

When the sheet becomes active a macro will get the table's data from the server and display it. When a cell value is changed in the sheet the change will be written to the server (unless it's to a primary key column, which cannot be changed).

The idea here is that we retrieve the data from the table into the sheet, and when the user edits any cell, we write the change back to the server. To ensure we write the data into the correct row, we need to know a unique value in the table - basically, the primary key (which is unique in SQL Server). Because it must stay unique, and because we need it to edit the rest of the data, the current implementation does not allow changing the primary keys. For similar reasons, currently the implementation doesn't allow adding new rows to the table. Connecting to the Server

To use this file, first you have to connect it to your SQL Server. To do that, you'll need to update the connection string found in the Workbook_SheetActivate function:

sConnectionString = "Server=YOURSERVER;Database=YOURDB;UID=YOURUSERID;Pwd=YOURPASSWORD"

Replace:

  • 'YOURSERVER' with your server's address
  • 'YOURDB' with the database you wish to edit
  • 'YOURUSERID' with a user name that can edit the data
  • 'YOURPASSWORD' with the password of that user

Contents

Retrieving the Contents of a Table

Create a sheet in the file for each table you wish to edit, and name it in the name of the table. The Workbook_SheetActivate macro will automatically retrieve the data from the table; it is automatically run by Excel when you change sheets. Let's look at the contents of this macro:

Opening the connection to the database:

Set con = New ADODB.Connection
con.Provider = "sqloledb"
sConnectionString = "Server=YOURSERVER;Database=YOURDB;UID=YOURUSER;Pwd=*******"
con.OpensConnectionString

Now retrieve the primary key information, using SQL Server's table meta data:

Set rs = con.Execute("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.TABLE_NAME = '" & Sh.name & "'")

Fill up the primary key information

  While (Not rs.EOF)
    pk.AddCStr(rs(0))
    rs.MoveNext
  Wend

Next we clean up the data in the sheet. We then read the data from the table and populate the sheet with the field names on the first row and the data under it:

' Now get the table's data
Set rs = con.Execute("SELECT * FROM " & Sh.name)

' Set the name of the fields
  Dim TheCellsAs Range
  Set TheCells = Sh.Range("A1")
  For i = 0 Tors.Fields.Count - 1
     TheCells.Offset(0, i).Value = rs.Fields(i).name
  Next i

' Get value for each field
nRow = 1
  While (Not rs.EOF)
      For i = 0 Tors.Fields.Count - 1
         TheCells.Offset(nRow, i).Value = rs(i)
      Next
      rs.MoveNext
      nRow = nRow + 1
  Wend
nRecordCount = nRow - 1

That's basically it for getting the data. Note that whenever the user moves the selection to a new cell, we use the Workbook_SheetSelectionChange macro to remember the old value:

Private Sub Workbook_SheetSelectionChange(ByValSh As Object, ByVal Target As Range)
  If (Not bIgnoreChange) Then
' Remember the old value
     oldValue = Application.ActiveCell.Value
  End If
End Sub

This is important because we don't want to allow editing the cells in some cases, and remembering the value will allow us to change it back if necessary.

The editing part is done in the Workbook_SheetChange macro, which is called by Excel whenever the user changes a value in a cell. First we make sure that this sheet is actually connected to the server, and that something actually changed. If so, we then perform some tests to ensure that the value changed is in a cell we can actually write to the server:

' Don't allow changes in the column names or outside of the table borders
If Target.Row< 2 Or Sh.Cells(Target.Row ,1).Text = "" Or Sh.Cells(1, Target.Column) = "" Or (Target.Row>nRecordCount + 1) Then Target.Value = oldValue oldValue = Application.ActiveCell.Value MsgBox "You can only edit items inside the table" Exit Sub End If ' If this change is in a primary key column - if so, we can't edit it
If (IsInPrimaryKey(Sh.Cells(1, Target.Column).Text)) Then Target.Value = oldValue oldValue = Application.ActiveCell.Value MsgBox "This column is a part of the primary key, so it cannot be changed" Exit Sub End If

If we passed those tests, the value change be changed. So we build the SQL update query that will do the actual work:

' Build the primary key from the data in this row
  Dim Names As Range
  Set Names = Sh.Range("A1")
nColumn = 0
sWhere = ""
  While (Names.Offset(0, nColumn).Text <> "")
      If (IsInPrimaryKey(Names.Offset(0, nColumn).Text)) Then
          If (sWhere<> "") Then
              sWhere = sWhere& " AND "
          End If
          sWhere = sWhere&Sh.Cells(1, nColumn + 1).Text & " = " & MakeSQLText(Sh.Cells(Target.Row, nColumn + 1))
      End If
      nColumn = nColumn + 1
  Wend
  

And perform the change:

' Update the server!
sSQL = "UPDATE " & Sh.name & " SET " &Sh.Cells(1, Target.Column).Text & " = " &MakeSQLText(Target.Text) & " WHERE " &sWhere
con.ExecutesSQL
oldValue = Application.ActiveCell.Value

The Solution In Action

To demonstrate the functionality of this solution, we opened a connection the VersaForm's database and edited the facilities table.

Let's take a look at the table in MSQuery:

Next we open the file and rename one of the sheets to 'facilities':

Then we edited the facility name in Excel:

Now we refresh the MSQuery, which should reflect the table:

That's it; we have edited our SQL data table from within Excel!

Summary

Hopefully this article demonstrates how you and your colleagues can relatively easily edit your SQL Server data using only Microsoft Excel.

Unfortunately we have not implemented a solution for inserting new records, and obviously our solution precludes editing of primary keys. We hand solving those issues over to you, dear reader! Got any suggestions or questions? Feel free to comment or get in touch!

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter

107605 6 /
Follow / 12 Oct 2014 at 1:23pm

I built something similar, but still feel there must be a simpler (less/no VBA approach)

tickett.wordpress.com/.../updateable-excel-frontend-for-sql-view

Follow / 29 May 2015 at 3:02pm

Found this article on updating database tables using excel however when I tried it, it does not update the database how can I find or contact the person that publish this article to see if I am doing something wrong.  

Article Name: Editing an SQL Server table in Excel (The author's name is: Yoav Ezer how do I contact him? )

Web Link: www.toadworld.com/.../10392.editing-an-sql-server-table-in-excel.aspx

I have Microsoft Office Excel 2010 and have SQL Server 2014 on my p.c.

So this is what I did:

Open SQL Server Management Studio and clicked on connect.

Downloaded the excel document from the web: “ Update SQLServer.xlsm”

Open Update SQL Server.xlsm document and removed the existing tabs,

Added a new tab and renamed it to one of my SQL Server 2014 database tables.

From the “Data” ribbon I selected “Connections: but nothing was there at first,

Next I chose “From Other Sources” where I was able to point to the database, select the database and the table I needed, this created a connection.

When I click on view source code I changed the connection so it would point to my local server.

I see the data from the table but when I changed the contents of a column nothing changes on the database

Please help me figure out what I am missing. There is nothing on the forum that tells me how to contact the author to find out if something I am doing is wrong. Thank you

Follow / 29 May 2015 at 3:36pm

I found the same error and after examining the code found that the following was not correct.  

Look for the code snippet in the VBA editor 'Sh.Cells(1, Target.Row).Text' and replace it with 'Sh.Cells(Target.Row, 1).Text' in the paragraph:

 ' Don't allow changes in the column names or outside of the table borders

  If Target.Row  nRecordCount + 1) Then

      Target.Value = oldValue

      oldValue = Application.ActiveCell.Value

      MsgBox "You can only edit items inside the table"

      Exit Sub

  End If

Oddly enough the correct code is stated in the main Wiki commentary page.

Hope this helps.

Follow / 29 May 2015 at 3:37pm

Also,

If your SQl Server column names contain spaces you will need to add square brackets to the SQL table names as follows:

REPLACE THIS CODE...

  ' Build the primary key from the data in this row

  Dim Names As Range

  Set Names = Sh.Range("A1")

  nColumn = 0

  sWhere = ""

  While (Names.Offset(0, nColumn).Text <> "")

      If (IsInPrimaryKey(Names.Offset(0, nColumn).Text)) Then

          If (sWhere <> "") Then

              sWhere = sWhere & " AND "

          End If

          sWhere = sWhere & Sh.Cells(1, nColumn + 1).Text & " = " & MakeSQLText(Sh.Cells(Target.Row, nColumn + 1))

      End If

      nColumn = nColumn + 1

  Wend

WITH THIS CODE ...

 ' Build the primary key from the data in this row

  Dim Names As Range

  Set Names = Sh.Range("A1")

  nColumn = 0

  sWhere = ""

  While (Names.Offset(0, nColumn).Text <> "")

      If (IsInPrimaryKey(Names.Offset(0, nColumn).Text)) Then

          If (sWhere <> "") Then

              sWhere = sWhere & " AND "

          End If

          sWhere = sWhere & "[" & Sh.Cells(1, nColumn + 1).Text & "]" & " = " & MakeSQLText(Sh.Cells(Target.Row, nColumn + 1))

      End If

      nColumn = nColumn + 1

  Wend

AND REPLACE THIS CODE...

 ' Update the server!

  sSQL = "UPDATE " & Sh.name & " SET " & Sh.Cells(1, Target.Column).Text & " = " & MakeSQLText(Target.Text) & " WHERE " & sWhere

  con.Execute sSQL

  oldValue = Application.ActiveCell.Value

WITH THIS CODE...

  ' Update the server!

  sSQL = "UPDATE " & Sh.name & " SET [" & Sh.Cells(1, Target.Column).Text & "] = " & MakeSQLText(Target.Text) & " WHERE " & sWhere

  con.Execute sSQL

  oldValue = Application.ActiveCell.Value

Hope this helps2.

Follow / 4 May 2016 at 6:42pm

I tried using this, but my connection uses windows authentication and I can't figure out how to update the connection string with the proper code.