Sometimes you want to get your Kildrummy® CostMANAGER Database into Excel without having to generate a new Excel file from the client and integrating it manually into your Excel Workbook.
No problem! In this post I'll walk you through how to pull data directly from CostMANAGER directly into Excel 2007.
First open up the Excel Workbook where you want your data to end up. In this example I'm going to use a new one. Next go to the 'Data' section of the ribbon, and pick 'From Other sources':
This will bring up a list of possible source types. We're going to use 'SQL Server':
Selecting this option brings up the Data Connection Wizard for a SQL Server connection. Here we enter the Server\Instance where our Kildrummy® CostMANAGER database lives and our user credentials so we can connect. Obviously yours will be different from mine:
The next step is to select our Kildrummy® CostMANAGER database. We are also invited to select a table to query at this point, since we're going to override this anyway I've chosen not to specify one by unchecking the 'Connect to specific table' option:
The last step of the Data Connection Wizard is to save this connection information for later use. I'm just going to use the default file name but alter the Description and Friendly Name to something a little bit clearer, then click on Finish:
Okay, so now we have a connection defined and saved, but we haven't actually used it for anything yet. Excel notices this too, and on exiting the Data Connection Wizard starts the Import Data process so we can actually set up the import details. Somewhat redundantly this starts out with our being asked for our Kildrummy® CostMANAGER login credentials and a table again. The table is still irrelevant so I'm just leaving it on the default:
At last! The import data dialog, For this example I'm just going to insert the Kildrummy® CostMANAGER data as a standard table and make it a new worksheet by selecting the 'New worksheet' option. We also need to make sure we click the 'Properties' button here rather than 'OK':
This gives us the 'Connection Properties' box. We make changes on the 'Definition' tab here so that instead of getting data from an internal Kildrummy® CostMANAGER table, we get it from a named Export:
There are two changes I'm making. The first is to change the 'Command type' value from 'Table' to 'SQL', as the second change is to replace the 'Command text' with a small chunk of SQL code that calls into the Kildrummy® CostMANAGER Data Exchange API to get the Export data. For my example of an Export called 'Export' that SQL is:
SET NOCOUNT ON; DECLARE @id UNIQUEIDENTIFIER;
EXEC spDE_GetConfigId 'Export', 23, @id OUTPUT;
EXEC spDE_Export @id;
A quick explanation of what this does. On the first line we ask SQL Server to return only the data and not the data as well as counts of records (it confuses Excel to get the counts as well as the data); we also set up a variable to hold the internal ID of our Export. On the second line we ask the Kildrummy® CostMANAGER Data Exchange API to give us that internal ID for the Export named 'Export', and on the third we ask it to answer the data for the Export with that internal ID.
So, if you need to the data for an Export Configuration named something other than 'Export', it is the second line you need to change. Swap 'Export' in single quotes for the actual name of your Export in single quotes.
Having made those changes I click 'OK' and promptly get a long-winded warning box:
Don't worry about this. It's just telling us that our connection isn't the same as the one we have saved on disk. Click 'Yes' here, then click 'OK' on the import data dialogue and, after a wait for the data to be retrieved from the server the Kildrummy® CostMANAGER the Export data should appear as a new Worksheet just as I requested:
Voilà!
From here you can do any of the things you would normal do with an Excel Data table. For instance refreshing it should always pull the most recent version of the data from Kildrummy® CostMANAGER.
This is obviously not the shortest procedure in the world, but I hope you can see that it can be quite useful. Good luck with your Kildrummy® CostMANAGER Data Exports!
0 Comments