Some of you* may remember a while back I was having a discussion with 
Darren about COM.

[* Particularly those of you who happen to be Darren.]

Anyway, I think I've hit upon a problem which COM might actually be able 
to solve... Can I use COM to transfer some numbers into Excel and get 
them graphed? And if so, how might I go about doing that?

(The alternative is to use GNUplot. But it does draw particularly ugly 
graphs, and it's awkward and unintuitive to use...)

Post a reply to this message

On 22-6-2013 16:39, Orchid Win7 v1 wrote:
> Some of you* may remember a while back I was having a discussion with
> Darren about COM.
>
> [* Particularly those of you who happen to be Darren.]
>
> Anyway, I think I've hit upon a problem which COM might actually be able
> to solve... Can I use COM to transfer some numbers into Excel and get
> them graphed? And if so, how might I go about doing that?
>
> (The alternative is to use GNUplot. But it does draw particularly ugly
> graphs, and it's awkward and unintuitive to use...)
>

why not simply Metapost?

or POV?

-- 
Everytime the IT department forbids something that a researcher deems
necessary for her work there will be another hole in the firewall.

Post a reply to this message

>> Anyway, I think I've hit upon a problem which COM might actually be able
>> to solve... Can I use COM to transfer some numbers into Excel and get
>> them graphed? And if so, how might I go about doing that?
>>
>> (The alternative is to use GNUplot. But it does draw particularly ugly
>> graphs, and it's awkward and unintuitive to use...)
>>
>
> why not simply Metapost?
>
> or POV?

Well, yeah, I could go reinvent the wheel... or I could quickly utilise 
something that already exists.

There are plenty of other possibilities too - Google Charts, for 
example. What none of these let you easily do is go back into the 
spreadsheet and tweak the data.

Currently my program spits out a CSV file, which I then open in Excel, 
manually select the range of cells I want, manually step through the 
chart wizard, and now I've got a graph. It seems like there ought to be 
some way to automate that...

Post a reply to this message

Le 23/06/2013 16:13, Orchid Win7 v1 nous fit lire :
> Well, yeah, I could go reinvent the wheel... or I could quickly utilise
> something that already exists.
> 
> There are plenty of other possibilities too - Google Charts, for
> example. What none of these let you easily do is go back into the
> spreadsheet and tweak the data.
> 

Ploticus, split your code from your data and the data is a separate (or
more) files, in space separated columns. Never touch the code again.
But mastering ploticus is not easy. (yet, that's what I use for my bank
accounts overview/balancing)

> Currently my program spits out a CSV file, which I then open in Excel,
> manually select the range of cells I want, manually step through the
> chart wizard, and now I've got a graph. It seems like there ought to be
> some way to automate that...

Excel... isn't there some macro language in that flipper ? (or is it a
flight simulator ? or doom-like ?)

Post a reply to this message

On 23/06/2013 3:29 PM, Le_Forgeron wrote:
> Excel... isn't there some macro language in that flipper ? (or is it a
> flight simulator ? or doom-like ?)

Yes there is. Andrew can record a macro then either edit it to be able 
to change the name of the file of keep the name of the input file the same.

Sample code:


Sub Macro6()
'
' Macro6 Macro
'

'
     Columns("A:B").Select
     With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;C:\Users\Stephen\Documents\Book2.csv", 
Destination:=Range("$A$1"))
         .Name = "Book2_1"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .TextFilePromptOnRefresh = False
         .TextFilePlatform = 850
         .TextFileStartRow = 1
         .TextFileParseType = xlDelimited
         .TextFileTextQualifier = xlTextQualifierDoubleQuote
         .TextFileConsecutiveDelimiter = False
         .TextFileTabDelimiter = True
         .TextFileSemicolonDelimiter = False
         .TextFileCommaDelimiter = True
         .TextFileSpaceDelimiter = False
         .TextFileColumnDataTypes = Array(1, 1)
         .TextFileTrailingMinusNumbers = True
         .Refresh BackgroundQuery:=False
     End With
     ActiveSheet.Shapes.AddChart.Select
     ActiveChart.ChartType = xlXYScatterLinesNoMarkers
     ActiveChart.SetSourceData Source:=Range("Sheet4!$A:$B")
End Sub


-- 
Regards
     Stephen

Post a reply to this message

> [* Particularly those of you who happen to be Darren.]

Haven't noticed him around for a while now.

> Anyway, I think I've hit upon a problem which COM might actually be able
> to solve... Can I use COM to transfer some numbers into Excel and get
> them graphed? And if so, how might I go about doing that?

Is this in C#? If so you need to look at Microsoft.Office.Interop.Excel 
- the VS help and google will get you started using it, it's very easy 
to do.

> (The alternative is to use GNUplot. But it does draw particularly ugly
> graphs, and it's awkward and unintuitive to use...)

Mind you Excel's graphs are pretty yucky by default (although the newer 
Office versions seem to have improved this).

If you /are/ on C# and using Windows forms then don't forget you've got 
built in DataVisualization.Charting which can be used to quickly add a 
charting capabilities to your program.

Post a reply to this message

On 23/06/2013 04:04 PM, Stephen wrote:
> On 23/06/2013 3:29 PM, Le_Forgeron wrote:
>> Excel... isn't there some macro language in that flipper ? (or is it a
>> flight simulator ? or doom-like ?)
>
> Yes there is. Andrew can record a macro then either edit it to be able
> to change the name of the file of keep the name of the input file the same.
>
> Sample code:

How would you execute that from outside Excel though?

Post a reply to this message

On 24/06/2013 08:11 AM, scott wrote:
>> [* Particularly those of you who happen to be Darren.]
>
> Haven't noticed him around for a while now.

It seems awful quiet in this newgroup allround, really.

Then again, I now spend all day every day constantly writing code. I 
hardly seem to have time to do anything else with my life now...

>> Anyway, I think I've hit upon a problem which COM might actually be able
>> to solve... Can I use COM to transfer some numbers into Excel and get
>> them graphed? And if so, how might I go about doing that?
>
> Is this in C#? If so you need to look at Microsoft.Office.Interop.Excel
> - the VS help and google will get you started using it, it's very easy
> to do.

Actually this is Haskell. But if it isn't too difficult, I could write 
some C# to actually graph the data once it's in a CSV file...

Post a reply to this message

On 26/06/2013 6:21 PM, Orchid Win7 v1 wrote:
> On 23/06/2013 04:04 PM, Stephen wrote:
>> On 23/06/2013 3:29 PM, Le_Forgeron wrote:
>>> Excel... isn't there some macro language in that flipper ? (or is it a
>>> flight simulator ? or doom-like ?)
>>
>> Yes there is. Andrew can record a macro then either edit it to be able
>> to change the name of the file of keep the name of the input file the
>> same.
>>
>> Sample code:
>
> How would you execute that from outside Excel though?
>
Whoa! now you are changing the question and we all know change requests 
cost money.

So why would you want to execute it from outside excel when you will 
need to open excel to see the graph?

But because it is you I will hazard an answer. Make the macro a startup 
macro.

-- 
Regards
     Stephen

Post a reply to this message

>> How would you execute that from outside Excel though?
>>
> Whoa! now you are changing the question and we all know change requests
> cost money.
>
> So why would you want to execute it from outside excel when you will
> need to open excel to see the graph?

Like I said, I've got a program that generates some data. I want to 
graph that data. Without having to manually press half a dozen buttons.

It's easy enough to open Excel programmatically; what isn't obvious is 
how to make it run code once it opens up. (Indeed, these days that tends 
to be exactly the sort of thing Excel blocks to prevent macro viruses...)

Post a reply to this message