Excel Tables

by Zack Barresse

  In the Import Data dialog box, select the check box Add this data to the Data Model. Note that Excel selects this check box by default if the check box Enable selection of multiple tables is also selected. TIP

  When you import data into an Excel workbook, loading it into the Data Model has the advantage of exposing that data to other analysis tools, such as Power Pivot.

  If the database administrator for a database has created a stored routine (call a stored procedure in SQL Server) for your use, then you need a custom query, often specifying parameters. To define a custom query, select any table on the Select Database and Table page of the Data Connection Wizard (it doesn't matter which one), click Next, and click Finish to display the Import Data dialog box. Then click Properties to open the Connection Properties dialog box and navigate to its Definition tab. The default query command text is displayed in the Command text text box. Change the Command type to "SQL" and enter the custom query string in the Command text text box. Click OK to execute the query.

  By default, creating a query connection to SQL Server creates an ODC (Office Database Connection) file, which is, by default, stored in the folder "C:UsersUSERNAMEMyDocumentsMy Data Sources".

  There are many different types of data sources available, and sometimes the default connection templates that Excel presents do not work. In these cases, Excel provides tools to define custom connections. A helpful website for determining connection strings for these custom connections is www.connectionstrings.com. Many examples of connection strings are available there, along with articles and Q&A forums.

  Table Names

  Excel imports each database table selected into a new Excel Table on a new sheet in the active workbook. It creates Table names using the format "Table_Name", where "Name" is the name of the database table or view in the SQL Server database. If you import a single table, the Table's name is "Table_ServerName_DatabaseName_TableName", where "ServerName" is the name of the server, "DatabaseName" is the name of the database, and "TableName" is the name of the Table or view imported.

  NOTE

  Crosstab queries cannot be returned natively when you connect to Access from Excel. There are two workarounds: Use Microsoft Query by selecting DATA | Get External Data | From Other Sources | From Microsoft Query or use VBA.

  Working with Data from Text Files

  Sometimes data comes in the form of simple text files. While this is not common these days, many applications still export data in text file format. Text files are usually in the form of a CSV (comma-separated value) file, a TXT (tab-delimited) file, or a PRN (fixed-field or space-delimited) file.

  Excel still creates a data connection when importing text files, but it does not put data into a Table, as it does with other data connections. This is due to the lack of information about the data contained in a text file. Databases have certain rules, like Excel Tables, such as unique field/column names, whereas text files are not bound by these rules. Excel loads data as it finds it in the text file into a range of cells without any formatting; it does not try to force the data into a Table or other structured format.

  CAUTION

  If a Table is inserted on data that is connected to a text file, Excel displays a warning, as shown in the next figure. The warning states that the connection will be severed if the Table is inserted.

  Error that appears when you insert a Table over a text file data connection.

  Connecting to a Text File

  To choose the text file to import, you select DATA | Get External Data | From Text. Default file formats are PRN, TXT, and CSV. After the file is selected, Excel opens the Text Import Wizard dialog box. The wizard walks you through three steps:

  1. Select Delimited for CSV and TXT files or select Fixed width for PRN flies. Then click Next.

  2. For a CSV or TXT file, select Comma for a CSV file or Tab for a TXT file. Then click Next. For a PRN file, define the column breaks and click Next.

  3. Define the format to be used to interpret each column in the text file. Click Finish. The Import Data dialog box appears, as shown in the next figure. Enter the destination range and click OK to import the data.

  Import Data dialog box.

  NOTE

  To refresh a data connection to a text file, you need to go through the import process again.

  Working with Data from Azure Marketplace

  The Azure Marketplace (datamarket.azure.com) is a repository of data and data mining applications. It is a cloud infrastructure and platform that is used to build, deploy, and manage cloud-based services. Data available from the Azure Marketplace can be exported to text files, pulled into a Power Pivot Data Model, or imported directly into a Table. If a queried record set is large, it may have to be imported directly into the Data Model, which supports many more rows than a worksheet.

  NOTE

  Connecting to the Azure Marketplace from Excel requires both the URL for the data and a primary Account Key. Both are available from the Azure site. If an Account Key is missing or invalid you will be prompted to verify it, as shown in the next figure.

  An error message with a missing or invalid Account Key when connecting to an Azure data source.

  At the Azure Marketplace home page, click the Data link in the top menu bar. The data page appears, listing all the catalogs or data sets available; some of them are free and some have an associated fee. Select one of these data sources to view that product's page, which displays the cost per transaction (query) and information about the data. Click the Signup link to open the Signup page. Click the check box stating that you agree to the terms of the agreement and click the Signup link to go to the Receipt page.

  On the Receipt page, you see one or more links for using the data. These are the most commonly displayed links:

  EXPLORE THIS DATASET—Opens a browser tab that provides both the URL and primary Account Key required to access the data from within Excel, as well as an interactive view of the data set.

  MICROSOFT POWERPIVOT FOR EXCEL 2010—Opens a browser tab that enables the export of the data set to other software, such as Power Pivot.

  TABLEAU SOFTWARE—Opens a browser tab with detailed instructions on how to import the data into Tableau.

  LEARN HOW TO USE THIS DATA IN VISUAL STUDIO—Opens a browser tab to the Microsoft Developer Network (MSDN) help topic that explains how to use the data in Visual Studio.

  Click any of the displayed download options or use your URL and primary Account Key from within Excel to retrieve the data. You then get a standard data connection in Excel. The following is an example of a connection string:

  Data Source=URL_Goes_Here;Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=Basic;User ID=AccountKey;Persist Security Info=false;Base Url=URL_Goes_Here

  Once you're connected to the Azure Marketplace data set or catalog, the connection is like any other data connection and can be refreshed so you can keep updated with the most current data. Keep in mind that every connection counts as a transaction.

  9 Automating Tables with VBA

  This chapter discusses how to automate Tables using Visual Basic for Applications (VBA). VBA is a programming language you can use to extend Excel's functionality far beyond what Microsoft provides with the installed application. VBA allows you to automate complex or repetitive tasks. For example, you can use VBA to format a worksheet received daily from an outside source, pull data from a web page once a week, or build a complex function that can be incorporated into a worksheet formula.

  The following are some examples of automating Excel Tables:

  You are building an application to be used by less experienced users, and you want to lock down the Table while p
roviding a few simple functions for adding, editing, and deleting records.

  You want to provide functionality to edit groups of records in a user form, such as all the transactions for a single order (initial deposit, payment to factory, freight, duty, final payment, etc.).

  Your Table supports other workbook functionality, such as recording event information in a log, and you want to insert rows into the Table, update existing Table data, and find and retrieve existing rows in the Table.

  You want to audit a Table and display any data and consistency errors that need to be fixed.

  To work through this chapter, it's best if you have at least a moderate level of experience programming, are aware of the Excel Object Model, and want to extend Excel's Table functionality beyond what is provided in the Excel application user interface.

  TIP

  One of the easiest ways to learn how to automate Excel is to use the macro recorder. You turn on the macro recorder by selecting DEVELOPER | Code | Record Macro. When you use the recorder, most actions are recorded in the VBA environment as VBA statements. Remember to turn off the macro recorder when the steps being automated are complete. You can view and edit recorded VBA statements by selecting DEVELOPER | Code | Macros, selecting the macro name, and clicking Edit. Excel names the recorded macros “Macro1”, “Macro2”, etc. Note that the code generated by the macro recorder is not the most efficient, and you can very often improve it with some minor editing.

  If the DEVELOPER tab is not visible, enable it by selecting FILE | Options | Customize Ribbon and enabling (checking on) Developer tab. In Excel 2007, select Office Button | Excel Options | Popular | Show Developer tab in the ribbon.

  To record a macro in Excel 2003, select Tools | Macro | Record New Macro.

  VBA, Excel, and Objects

  Before you go any further in this chapter, it is important that you have a basic understanding of what objects are and how to use them when automating Excel using VBA. This section presents a brief review of objects and how to use them in the Excel environment. You can skip this section if you already have experience working with objects in the Excel VBA environment.

  In the VBA environment, the Excel application presents the things you can access as the Excel Object Model. Consider these basic Excel objects:

  Application—The Excel application.

  Workbooks—A collection of all the workbooks currently open in the Excel application. A collection, in a generic sense, is a set of objects. In this case, the Workbooks collection is a set of individual Workbook objects.

  Workbook—A single workbook.

  Worksheets—A collection of all the worksheets in a workbook.

  Worksheet—A single worksheet or tab in a workbook.

  Cells—A collection of all the cells in a sheet.

  Range—A set of one or more cells on a single worksheet. The cells can be discontinuous. Any cell reference is a Range object.

  Notice that the objects in this list correspond to specific things in the Excel application environment that you see when working with workbooks. This is true with any Object Model: Each object refers to a thing that exists (a workbook, a person, a place) and that has properties (a person's age, a place's address.)

  Every object belongs to a parent object; for example, a Workbook object's parent is the Application object, and a Cell object's parent is a Worksheet object. One exception is the Application object, which has no parent; it is the highest-level object available when you're looking at the Excel Object Model. When describing the context of an object or method, these terms are often used:

  Parent—The object that exposes the object in question. For example, the Cell object's parent is the Worksheet object.

  Member—A property or method exposed by its parent. For example, the Cell object is a member of the Worksheet object.

  Every object has members. A member can be a simple attribute, such as a cell's value, or it can be another object or collection of objects, such as the Workbook object's member Worksheets; the Worksheets object is a member of the Workbook object, and both have a number of properties and methods. A member can also be a method that does an action.

  When referencing an object, you have to start with the highest-level parent. You find each subordinate object by typing the parent object followed by a period and then the child member. For example, to reference the value of cell A1 on "Sheet1" in the workbook "My Workbook.xlsx", you use the following VBA syntax:

  Application.Workbooks("My Workbook.xlsm").Worksheets("Sheet1").Cells(1, 1).Value

  NOTE

  Instead of using the cell reference A1, the example above uses 1, 1, with the Cells object translating to row 1, column 1. There are ways to use the A1 syntax, but we don’t cover them here.

  Be aware that the Excel Object Model exposes default objects depending on what application element is currently active. Many programmers take advantage of this and use shortcuts in their code. For example, the following syntax references cell A1 in whatever worksheet happens to be active when the code is executing:

  Cells(1, 1).Value

  While this syntax works, it is not considered good practice to use it unless you actually want to reference the active worksheet. However, in that case, we recommend using the ActiveSheet object to provide documentation to other developers that you did, in fact, intend to reference the active sheet:

  Application.ActiveSheet.Cells(1, 1).Value

  The one object that is implied throughout the Excel VBA environment is the Application object, and you can omit it with no problem. Therefore, the following references are non-ambiguous anywhere in the Excel VBA environment:

  Workbooks("My Workbook.xlsm").Worksheets("Sheet1").Cells(1, 1).Value

  ActiveSheet.Cells(1, 1).Value

  And these references are just as non-ambiguous within Excel:

  Application.Workbooks("My Workbook.xlsm").Worksheets("Sheet1").Cells(1, 1).Value

  Application.ActiveSheet.Cells(1, 1).Value

  You can assign a reference to any object to a variable as long as that variable is the same type as the object or is defined as a generic Object type. The primary reason for doing this is convenience. If you're referencing an object repeatedly, then setting a variable to reference that object can result in less code that is both easier to read and maintain. For example, you can assign the reference to "Sheet1" from above to a variable:

  Dim TargetWorksheet As Worksheet

  Set TargetWorksheet = Workbooks("My Workbook.xlsm").Worksheets("Sheet1")

  Now the reference to A1 becomes:

  TargetWorksheet.Cells(1, 1).Value

  There are two things to note from this example. First, the Set keyword assigns the reference. VBA requires the use of the Set keyword when assigning references to objects. Also, the variable is a reference to the object, not a copy of the object. You can have any number of variables containing a reference to the same object instance.

  NOTE

  You may have noticed that we’re now using the term object reference rather than just object. As you learn more about object-oriented programming, you will learn about objects, object instances, and object references. An object includes the Object Model (properties and methods that belong to it) and the code that governs how it behaves. An example of an object is the Worksheet object. An object instance is a specific instance of that object and includes the data and property values associated with that object instance. Worksheets “Sheet1” and “Sheet2” are examples of object instances; both are instances of the Worksheet object. A variable that references that object contains an object reference.

  When you define variables that reference objects, you must define a variable as the same type as the object b
eing referenced or as a generic Object type. In general, you should use the generic Object type only if that same variable will be assigned references to other objects of different types; when you use a generic Object type, the VBA editor can't assist you with IntelliSense. (IntelliSense presents a list of an object's properties and methods as you enter the object reference followed by a period.)

  For the rest of the discussion on automating Tables, we use the following references (objects):

  ThisWorkbook—The workbook in which the code is running. This is convenient when the only workbook being referenced is the one in which the code resides. ThisWorkbook is easier to work with than Workbooks("My Workbook.xlsm"), especially when the workbook name may change.

  Me—When the code resides in a worksheet's code module, Me is a convenient way to reference the Worksheet object in which the code resides. CAUTION

  Excel Online does allow Excel automation with VBA to exist in a file (when viewed in a browser), but it does not execute the code. Any code written behind a workbook is ignored when you open that workbook in the Excel Online application, but the code is retained.

  ListObject Object

  Excel uses the ListObject object to expose a Table in the Excel Object Model. It is contained in the collection ListObjects, which belongs to the Worksheet object. Use this syntax to reference a specific Table on a worksheet:

  ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

  TIP

  Name your Table something other than the default name. Doing so will help you both when writing your code and later, when you are looking at your code trying to figure out what you did. It will also be helpful to someone else looking at your code.

  Because ListObjects is a collection of Tables, you can also access it with an index number: