Re: [Summary-Talk] Excel macros

[Donald Radlund <radlund@xxxxxxxxxxxxxxxxxxx>]

Ok I got it bringing in the data perfectly but now I want to keep my
formatting in the excel file. How do I do that or am I just SOL? Is there a
way to only select certain columns to import or would I have to set up my
report to only give me those columns?
-- 
Donald Radlund
Technical Director
Twelve Oaks Media
847.202.9400


> From: Jason Linhart <jason@xxxxxxxxxxx>
> Reply-To: <summary-talk@xxxxxxxxxxxxxxxxx>
> Date: Tue, 04 Mar 2008 13:54:06 -0500
> To: <summary-talk@xxxxxxxxxxxxxxxxx>
> Subject: Re: [Summary-Talk] Excel macros
> 
> Donald Radlund wrote:
>> Can someone help me get started with creating some excel macros to fetch
>> data from summary?
> 
> There are slightly different ways to do this in various version of
> Excel, though they are all essentially the same.
> 
> First you need to create a saved query file. This is a plain text file
> that contains something like:
> 
> WEB
> 1
> http://summary.net:7000/~demo/fullxls/00.xls
> 
> This file should get saved with a ".iqy" extension.
> 
> The first two lines are fixed, they should always look exactly like
> that. The third line is a URL to a Summary report in Excel format, in
> this example it is the Hourly Report from the live Summary demo site.
> Towards the bottom right of any Summary report, in Summary Plus, SP
> Lite, or SP, is an icon that looks something like an 3-d bar chart. That
> icon is a link to the current report in Excel format. Excel is fairly
> clever and can often get something useful from the HTML versions of the
> reports, but things work much more reliably when you have Excel fetch
> the data in Excel format.
> 
> Then you go to an Excel worksheet and select a cell that you want to
> contain the query. Then you tell Excel to run a saved query. Exactly how
> you do that varies from one version of Excel to another. On my copy it
> is in the Data menu, Get External Data, Run Saved Query . . . In other
> versions of Excel it might be Data menu, Import External Data, Import
> Data . . . or something similar to those two. Select the .iqy file you
> just created.
> 
> Excel will then prompt you for where it should place the imported data.
> I like to place each imported report on it's own sheet but you can put
> the data where ever you want. Just keep in mind that the height might
> vary, so leave lots and lots of room below the insertion point.
> 
>   From there it is simply a matter of figuring out what data you want and
> how to arrange it within the spreadsheet. In newer versions of Excel
> queries can be configured to update automatically or upon request. Look
> for the External Data toolbar and see what options it offers you.
> 
> Enjoy
> Jason
> 
> -- 
> Jason@xxxxxxxxxxx
> --
> Dr. Seuss books . . . can be read and enjoyed on several levels. For
> example, 'One Fish Two Fish, Red Fish Blue Fish' can be deconstructed
> as a searing indictment of the narrow-minded binary counting system.
>    -- Peter van der Linden, Expert C Programming, Deep C Secrets

-------------
Go to <http://summary.net/list.html> to update subscription info.