Importing SMS Queries into Excel

The Query Extract tool is functionally the same whether you use the template version or the workbook version. This chapter describes how to use the template version.

When you run the SMS Query Extract tool, the first macro that runs is ThisWorkBook.Workbook_Open. This macro makes the connection to the SMS Provider and is the starting point for using this tool. This macro is run automatically when you first run the tool. If you ever stop the macros in this tool and want to restart them, simply run this macro again.

Copy Smsextract.xlt to an appropriate location on your local drive, such as C:\Program Files\Microsoft Office\Templates. Then, run Excel and create a new workbook based on this template, as shown in the following procedure.

  To import SMS Query results into an Excel workbook

1. In Excel 97, on the File menu, click New.
2. Select the Smsextract.xlt template to base the new workbook on.
3. Depending on your Excel settings, when you create the new workbook, a dialog box might appear, stating that the workbook you are opening contains macros. If you want to use this tool, click Enable Macros. If you don’t want to run the macros immediately, you can run the ThisWorkbook.Workbook_Open macro at a later time.

   When the ThisWorkbook.Workbook_Open macro runs, the SMS Login dialog box opens.



4. In the SMS Login dialog box, type the name of the site system acting as your site server (do not use leading slashes), your user name (in domain\user syntax), and password. Click OK.

   ---

   Note   The user account that you use must have been granted WMI permissions by your SMS site administrator, either by membership in a group that has been granted rights, or individually. For information about granting rights to WMI data and the SMS Provider, see Chapter 19, “Creating Administrative Reports,” in the SMS 2.0 Administrator’s Guide.

   ---

   The first time you run this tool, you will see the values “.” for the server name and “smsdemo” for the user name. Replace these values by entering your own values for server and user names. The values you supply will be saved in the registry of the computer you are using when running this tool. The next time you use this tool, the appropriate server and user names will be obtained from the registry. Even if you create a new workbook from the template, the server and user names will be read from the registry values of the computer you are working on.

5. The Select Site Query to Import dialog box opens, as shown in the following figure. This dialog box displays all the SMS queries found on the specified site server. Select one or more queries to import. By using the CTRL key, you can select multiple queries. When you have finished selecting the queries you want to import, click OK.



The workbook imports the results returned by each selected query. Each query’s result set is displayed in an individual worksheet within the workbook. The columns display the class attribute being queried; for each row is an instance of data returned by the query for that attribute, as shown in the following figure.



If a query, such as the All Systems with Specified Software File Name and File Size query, requires input, a dialog box appears so that you can type in the value needed. You are notified when any query returns an empty result, and the tool skips the display of that query.

6. Save the workbook by either accepting the default file name or by specifying a new file name.

You can open and run a previously saved Query Extract workbook. When you do so, you are prompted to choose whether to resave or discard the previous data. If you save the data, new spreadsheets will be added to the workbook to hold additional result sets. If you choose to discard the data, the old data is removed and the new workbook will contain only the newly imported query results.