PDA

View Full Version : Excel CopyPicture and Binary Stream



stanl
06-01-2007, 07:19 AM
I just wrote a short Excel macro to import a mainframe .prn file, sort and parse data to output over 250 graphs which are then placed in a Word Document.

I use CopyPicture from Excel [a range that includes both the graph and descriptive data] then Paste into the word document, issue a page break, then repeat the process until end of data. Takes under 30 seconds and is pretty slick.

I would like to move from CopyPicture directly to an Access binary field in a table, so that I can save months of graphs and export individually when required.

I know I can 1. use copy picture 2. paste into an Excel chart object 3. reformat the chart size 4. export to a gif file 5. Open up an ADODB.Stream object, load the file and place into the recordset field object.

I would like to know if there is some trick to go directly from the Clipboard (after CopyPicture) into the ADODB.Stream....

I've done some googling but could find nothing. Any suggestions appreciated:banghead: Stan

stanl
06-02-2007, 06:28 AM
AaaarrggHH! :banghead: I would have hoped someone would have either said "No Can Do" or offered a method,

Anyway, I did it the old-fashioned way, and discovered that using the Chart Export is great - for 1 instance - but I was rapidly creating 255 instances.

So, for future reference [AFAIMC] :

1. Create a new worksheet called 'chart'
2. Create the Initial Chart Object and assign it to that worksheet
3. Obviously your will be running a For..next or While...Endwhile loop

4. After each chart is exported.....

use ActiveChart.ChartArea.Clear

Then redo step #2

Then [at least I found], put in a delay of about 1/2 a second to prevent chart garbage.

Stan