ADO Example Importing SQL Data

Country Abbreviations

For our first ADO example, we're going to use the AdventureWorks sample database provided by Microsoft. You can install a copy of the AdventureWorks database by running the file AdventureWorksDB.msi. You will be using SQL Server 2005 Management Studio Express to view the various database objects. To install Management Studio Express, run S0LServer2005_SSMSEE.msi. 1. Open a new workbook and name it DataAccessSample04.xlsm. 2. Before we begin using ADO in Excel 2007, we must add a reference to...

Macro Recorder Generated Text Import Code

After we stop the Macro Recorder, we are left with code that looks like Listing 2-2. Listing 2-2. Macro-Generated Text Data Import Code Sub GetTextData 2 files maillist.csv, .TextFileTextQualifier xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter False .TextFileColumnDataTypes Array 2, 2, 2 .TextFileTrailingMinusNumbers True Application.Goto Reference maillist Range A1 .Select End Sub One of the first differences to notice about this code when compared to the Access data import is how...

Creating a Custom Task Pane and Data Input Form Using NET

Create Custom Input Forms Powershell

In this example, we'll look at a .NET project that creates a custom task pane and a simulated Excel UserForm. A task pane is a window that anchors itself to the right of an Office application and contains commands to perform various functions. A common Office task pane is the Getting Started task pane, shown in Figure 9-21. Figure 9-21. Getting Started task pane in Excel 2003 Figure 9-21. Getting Started task pane in Excel 2003 Our example task pane will contain commands used by a human...

Creating the XML File That Contains the Markup to Modify the UI

Open a new file in the text editor of your choice, and save it as customUI.xml in the customUI folder. 3. Add the following code to the customUI.xml file < customUI < ribbon startFromScratch true> < tabs> < tab id DataFunctions label XML Data Functions' < group id NewDataControls label New Data> < button id Button1 size large label Get Emps Dept > < button id Button2 size large label Get HR Info > < group id AppendDataControls label...

Building an XML Data Class

Now that we've got an idea of what XML data services Excel provides us, let's build a class that will give us an easy-to-use interface to this functionality. Before we begin, though, let's think about what we'd like our class to do We want it to bring in data from various XML data sources. We want the ability to refresh the data in case the source file is updated either manually or through an automated process . We want to append data from another file that conforms to the same XML schema. We...