OfficeTalk: Programmatically Update Multiple External Data Connections in Excel 2007 by Using Open XML

Article
05/23/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Create a Windows User Form project that allows you to update external data connections in Excel 2007 workbooks without opening Excel 2007. See how easy it is to work with Office Open XML files. (11 Printed Pages)

Frank Rice, Microsoft Corporation

July 2009

Applies to: Microsoft Office Excel 2007, Open XML Format SDK 2.0

Contents

Overview of Windows Forms Project
Automatically Refresh Data When a Workbook is Opened
Creating Windows Form Projects
Creating User Forms
Adding Code to Projects
Browsing for Search Folders
Updating Data Source Connections
Testing the Project
Conclusion
Additional Resources

Overview of Windows Forms Project

While I was monitoring a Microsoft Office Excel newsgroup recently, a reader expressed a need to programmatically update Excel 2007 files when the connection to an external data source changed. Fortunately, the Office Open XML Formats make this a relatively simple task. In this column, I demonstrate creating a Windows Form project that you can use to do bulk updates of external data connections in one or more Excel 2007 workbooks; all without opening Excel.

For example, assume that one of your Excel 2007 files contains an external link to a Microsoft Access database. If you were to move or rename the database, the connection to that data source would be broken. Further, suppose that you had dozens or hundreds of such files with that connection. Imagine having to open each file, update the connection, refresh the file, and then save it. This could take hours or days to complete. However, by programming against the connections.xml part in the Excel 2007 file when it is opened as an Office Open XML file, you can knock this task out in seconds or minutes.

The project consists of a Windows user form with controls that let you browse to a folder containing Excel 2007 files. You then type the current path to the external data source and the path to the new data source in textboxes. When you click an update button, the project iterates through the Excel files in the target folder and opens each file as an Office Open XML Spreadsheet document. This and other objects are available through the DocumentFormat.OpenXML namespace.

Note

You can find more information about the DocumentFormat.OpenXML namespace in the Open XML Format SDK 2.0. See the Welcome to the Open XML Format SDK 2.0 Web page.

Returning to the project description, the code loads the contents of a connections.xml part, if it exists, into an XML Document and searches for the current data source path. If it is found, the code replaces the current data source path with the new data source path and then saves the file. The form also includes a textbox that displays the number of files that were updated.

Automatically Refresh Data When a Workbook is Opened

Although this step is optional, it will allow the updated data source links to refresh automatically when you open the workbook. This is a one-time step for each workbook that you will be updating.

Open the workbook file in Excel 2007.
Click a cell in the external data range.
On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

Figure 1. Connection Properties on the Data tab
Click the Usage tab.
Under the Refresh control, select the Refresh data when opening the file check box.
Next, click the Microsoft Office button and then click the Excel Options button.
Click the Trust Center tab and click Trust Center Settings.
Click the External Content tab and select the Enable All Data Connections option.
Click OK twice to close the dialog boxes.

Creating Windows Form Projects

In the following steps, you create the Windows Form project in Microsoft Visual Studio 2008 with Visual Basic .NET.

Start Microsoft Visual Studio 2008 (you can also use Microsoft Visual Studio 2005).
On the File menu, point to New, and then click Project.
In the Project types pane, expand Other Languages, expand Visual Basic, and then click Windows.
In the Templates pane, select Windows Forms Application.
In the Name box, type UpdateDataConnectionOOXML and click OK. The project is created and opens to the editor.

Next, add a reference to the DocumentFormat.OpenXML library to the project and then insert that and other references into the code. This library contains the objects and methods you need to work with Excel 2007 files as Office Open XML documents.
On the Project menu, click Add Reference.
In the Add Reference dialog box, on the .NET tab, scroll until you see DocumentFormat.OpenXML, select it, and then click OK.
In the Solution Explorer (the pane on the upper right-side of the window), right-click Form1.vb and then click View Code.

At the top of the code window, add the following statements:

Imports System.IO
Imports System.Xml
Imports DocumentFormat.OpenXml.Packaging

Creating User Forms

In the following steps, you create the user form for the project by adding controls to its design surface.

In the Solution Explorer, right-click Form1.vb, and then click View Designer.
Next, drag the controls listed in Table 1 to the user form design surface and set their properties as well as one form property by clicking the control, and then updating the property pane (lower right-side of the window). If you do not see the Property pane, on the View menu, click Properties Window. The form should look similar to Figure 2.

Figure 2. The Update Connection String user form

Table 1. Add these controls to the User Form

Control Type	Name	Text
Label	Label1	Path to Files
TextBox	txtDirectory
Label	Label2	Old Connection Path
TextBox	txtOldLocation
Label	Label3	New Connection Path
TextBox	txtNewLocation
Button	btnBrowse	Browse
Label	Label4	Files Updated
TextBox	txtNumUpdated	0
Button	btnUpdate	Update
Button	btnClose	Close
Form	Form1	Update Connection String

Adding Code to Projects

In this section, you add code to the project to close the user form as well as create some field variables.

On the user form design surface, double-click the Close button to create the event procedure that is called when you click the button at runtime.
Add the following statement to the btnClose_Click event procedure. This statement closes the form when the Close button is clicked.
```
Close()
```
Next, add the following declarations.
```
Private fileType() As String = {"*.xlsx", "*.xlsm"}
Const spreadsheetmlNamespace As String = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
Private numChanged As Integer = 0
```
fileType is an array variable that contains the file extensions used to filter only Excel 2007 file types. The spreadsheetmlNamespace constant is the default namespace for SpreadsheetML and is used in the project when querying for the current data source path. The numChanged variable is the counter for the number of files that get updated.

Browsing for Search Folders

In this section, you add the procedure that is executed when you click the Browse button. It allows you to browse to and specify the folder containing the Excel files that you will update. .NET has a rich set of objects that simplify working with files and folders.

On the user form design surface, double-click the Browse button.

In the btnBrowse_Click procedure, add the following code:

Dim theFolderBrowser As New FolderBrowserDialog

' Give user instructions in browser dialog.
theFolderBrowser.Description = "Please select a folder."

' Browser is to be used only for selecting an existing folder.
theFolderBrowser.ShowNewFolderButton = False

' Optionally set the starting location to search.
theFolderBrowser.RootFolder = System.Environment.SpecialFolder.MyComputer
' You could use the following statement to start the search at the Desktop.
'theFolderBrowser.RootFolder = System.Environment.SpecialFolder.Desktop

' Set the default browser dialog to the MyDocuments folder.
theFolderBrowser.SelectedPath = My.Computer.FileSystem.SpecialDirectories.MyDocuments

If theFolderBrowser.ShowDialog = Windows.Forms.DialogResult.OK Then
   ' Set the textbox to the folder path.
   Me.txtDirectory.Text = theFolderBrowser.SelectedPath
End If

Updating Data Source Connections

In this section, you add the procedures that update the Excel 2007 files. It does this by doing the following when you click the Update button:

Checks to make sure that the textboxes on the user form are filled in.
Iterates through the files in the target folder and calls the UpdateConnectPath procedure.
Opens the Excel file as a SpreadsheetDocument document which is a type of Office Open XML package.
Retrieves the connections.xml part from the package.
The XML in the part is searched for the old data source path by using an XPath expression.
If the path is not found, an error is generated. However, the error is ignored and the next file is searched.
If the old data source path is found, the path is swapped with the new data source path, and then the updated file is saved.
The counter specifying the number of files that were updated is also incremented.
When the updates are complete, control is returned to the calling procedure. The textbox displaying the number of files updated is populated and a message dialog is displayed that the operation is complete.

The following is the btnUpdate_Click event procedure which is called when you click the Update button.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
   Dim strSourceFile As String = ""
   Dim files() As String
   Dim file As String

   ' Ensure that the textboxes are not blank.
   If (Me.txtDirectory.Text <> "") Then
      strSourceFile = Me.txtDirectory.Text
   Else
      MessageBox.Show("Please select the file path.")
      Exit Sub
   End If

   If (Me.txtOldLocation.Text = "" Or Me.txtNewLocation.Text = "") Then
      MessageBox.Show("Please specify the location of the external data.")
      Exit Sub
   End If

   ' Search the files in the given directory.
   For i As Integer = 0 To fileType.Length - 1
      files = Directory.GetFiles(strSourceFile, fileType(i))
      For Each file In files
         UpdateConnectPath(file)
      Next
   Next

   ' Display the files processed counter.
   Me.txtNumUpdated.Text = numChanged
   numChanged = 0
   MessageBox.Show("Operation complete.")

End Sub

The following is the UpdateConnectPath procedure which is called from the btnUpdate_Click event procedure.

Private Sub UpdateConnectPath(ByVal strSourceFile As String)
   Dim oxmlNode As XmlNode

   Try
      ' Open the workbook.
      Dim wkb As SpreadsheetDocument = SpreadsheetDocument.Open(strSourceFile, True)

      ' Manage namespaces to perform Xml XPath queries.
      Dim nt As NameTable = New NameTable
      Dim nsManager As XmlNamespaceManager = New XmlNamespaceManager(nt)
      nsManager.AddNamespace("sh", spreadsheetmlNamespace)

      ' Get the connections part from the package.
      Dim xdoc As XmlDocument = New XmlDocument(nt)
      ' Load the XML in the part into an XmlDocument instance.
      xdoc.Load(wkb.WorkbookPart.ConnectionsPart.GetStream)

      ' Find the SourceFile attribute.
      oxmlNode = xdoc.SelectSingleNode("/sh:connections/sh:connection/@sourceFile", nsManager)

      ' Update the files processed counter.
      If (oxmlNode IsNot Nothing And oxmlNode.Value <> Me.txtNewLocation.Text) Then
         numChanged += 1
      End If

      ' Replace the old path with the new path.
      oxmlNode.Value = Replace$(oxmlNode.Value, Me.txtOldLocation.Text, Me.txtNewLocation.Text)
      xdoc.Save(wkb.WorkbookPart.ConnectionsPart.GetStream)

      ' Find the connection string attribute.
      oxmlNode = xdoc.SelectSingleNode("/sh:connections/sh:connection/sh:dbPr/@connection", nsManager)

      ' Replace the old path with the new path and save the document.
      oxmlNode.Value = Replace$(oxmlNode.Value, Me.txtOldLocation.Text, Me.txtNewLocation.Text)
      xdoc.Save(wkb.WorkbookPart.ConnectionsPart.GetStream)

      Catch
         ' Some files have no external connections so ignore any errors.
      End Try

End Sub

Testing the Project

To test the project, I performed the following steps:

Create an Access database named MySampleDB.accdb with a single table named MyTable that looks like Figure 3.

Figure 3. The MyTable table in the MySampleDB.accdb Access database
Save the database to a folder named C:\MySampleLocation.
Create a couple of Excel 2007 workbooks and save them to the C:\MySampleLocation folder.
In each Excel 2007 file, create an external link from the workbook to the MySampleDB.accdb database table by clicking on the Data tab and clicking From Access in the Get External Data group.
In the Select Data Source dialog box, navigate to the C:\MySampleLocation folder, select MySampleDB.accdb and then click Open.
In the Import Data dialog box, leave Table selected and click OK.
Save and close the workbook.
Create a second folder named C:\MySecondLocation and copy the MySampleDB.accdb file to it.
Open the MySampleDB.accdb database and change the MyTable table to that seen in Figure 4.

Figure 4. The updated MyTable table
Finally, open the UpdateDataConnectionOOXML project and start the project by clicking the Debug menu and then clicking Start Without Debugging.
Click the Browse button and navigate to the C:\MySampleLocation folder.
In the old and new connection paths textboxes, type the information shown in Figure 5 and click the Update button.

After the data source paths have been updated, the operation complete message is displayed.

Figure 5. The Operation Complete message is displayed

Conclusion

In this column, I demonstrated how you can update the data source connections in Excel 2007 workbooks without the need to open Excel 2007. By adding a little code, you could modify the project to recursively search for and update files in subfolders as well. I encourage you to explore the Open XML SDK 2.0 and the information it contains to see what else you can do with Office Open XML files.

Additional Resources

More information about the topics covered in this column can be found at the following locations.