How to update OLE graphs from Excel in documents

I'm assisting in the creation of a cross-platform presentation on CD. We choose to export from Powerpoint to HTML, but for a future 2nd project I would prefer a different way.... Domino export to HTML!

I'm writing a notes-database to provide all required functions (similar to powerpoint), but one feature is puzzling me:

Several documents (even hundreds!) contain OLE-objects (MS Excel graphs). Dominos translation to JPG and the export to images is all handled quite well, but when the charts change (e.g. colours), I'll need to update those documents.

Things work if I edit, refresh and save documents one by one, but for the expected number of documents, I would prefer an automated way. I'm even too lazy to answer "Yes" to every refresh proposal!

Has anyone got the solution (pref. LS code)?
LVL 13
CRAKAsked:
Who is Participating?
 
Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
CRAK,

To simulate SendKeys:

Declare Sub keybd_event Lib "user32.dll" (Byval bVk As Byte, Byval bScan As Byte, Byval dwFlags As Long,Byval dwExtraInfo As Long)

Private Const FLG_KEYDOWN= 0
Private Const FLG_KEYUP= 2

Private Const KEY_F6 = &H75

      Call keybd_event(KEY_F6, 0, FLG_KEYDOWN, 0)
      Call keybd_event(KEY_F6, 0, FLG_KEYUP, 0)

I don't know the code for the Enter-key, you must be able to find that out somewhere (maybe even EE!)

Sjef
0
 
ArunkumarCommented:
Top of my head...I dont understand a single word in what you are saying...just to know who wins this one !

Good Luck!
Arun.
0
 
CRAKAuthor Commented:
Rephrase:
In Excel I have created a number of graphs.
Using copy in Excel and Paste special in Notes (RT field) I have created OLE objects (double clicking the graph in notes launches the original file in Excel).
If the charts in the Excel file are modified, I need the OLE objects updated as well.
Opening a document raises a prompt asking if I would like to refresh the object(s) on that document, and when I do I will need to save that change.... all works fine exept that it is a lot of work that I with to automate.

In an update action in a view, I am able locate those documents that hold OLE objects and skip the ones that don't.
Now I need a way to update the object in notes with the latest change in Excel.
(Opening each document in edit mode would probably work, but I don't want to have to press "Yes" repeatedly)

You must have a link to the solution somewhere....!? Perhaps you can even talk me into spending a bit more!

;-)
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
ArunkumarCommented:
So, if you open a document, edit it, refresh it and save it works for you.  Meaning...

Some computed fields change its value in the document ?
Or the OLE changes ?

I may be vague...try a ToolsRefreshSelectedDocs as an agent and run it on selecting document.

Similarly write lotus script and loop it through the selected documents...processing each document with compute with form and saving the document.

But i am not sure if these would work....

Lets give it a shot...
0
 
ArunkumarCommented:
I wonder also...

Looping through all the docs and bringing eachdocument to edit mode using

set uidoc = Workspace.EditDocument(doc)
Call uidoc.Save
Call uidoc.Close

And so on for all the documents.  But i know for sure that this cannot process too many documents since...somewhere it gets to the memory limit.

I am just throwing stones....
0
 
CRAKAuthor Commented:
Tried it before I posted the question.
Even when the document is opened in edit-mode notes (?) askes if I want to refresh.... can't save then, but the updated object does get shown!
...and it's the user interferance (pressing "Yes" for each document) that I want to get rid of.

If you like a challenge: try for yourself in an empty database... All you need is one (saved) chart in an excel sheet, one form with one rich-text field, one document to paste the chart in and one (saved) modification to the chart...

There are 800 points to be earned..... or more....!
0
 
HemanthaKumarCommented:
Are you launching the ole object at open ?
Did you check the property update object from document for ole ?
0
 
CRAKAuthor Commented:
Heman,

Glad to have you here as well: the more the merrier!

About that Launch option: no, I wasn't. And now that I tried it: the question got skipped, the graph got launched in excel and the notes document appeared under it displayed as if it was a lay-out region.

And about Update object...: I didn't know about the option. However, it seems available only when OLE objects are created on the form in the designer client. In my case, they are made in a field on a document. I can't find an option like it.

Now once I have found an OLE object in a document, I can access its methods (e.g. activate, doverb) and properties (e.g. class, name, source, verbs). I was expecting to get into some "deeper layer" to get the job done but I can't find my way there.
0
 
HemanthaKumarCommented:
You have to refer to the microsoft vba help for more details for excel or word.

And here is the link to it.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrWorkingWithMicrosoftExcelObjects.asp

Once you get the handle to the ole object by getobject or createobject method, from then you are accessing excel/word object.



0
 
zvonkoCommented:
Hi CRAK,

send your actual code please to paunoski@freenet.de
I would like to extend your code so no intervention is needed for refreshing Excel images on selected documents in Notes client.

So long,
zvonko


0
 
CRAKAuthor Commented:
I'll soon get back on this when I get back home (early June).... I'm currently on holiday (England: another challenge: driving on the left side without wrecking the car!)

Haven't done anything specific yet though, zvonko, just a set of random numbers in a table (excel), create a graph from it and paste in into a RT field... No code so far (unless you refer to the handfull of lines to find objects). I'm willing to post the whole lot though... in june, thanks!
0
 
CRAKAuthor Commented:
Zvonko, have you received my message?
0
 
zvonkoCommented:
Yes, I got your mail, thanks.

But I have no idea.

Basically you can not avoid this message:
"This document contains links to external objects. Refresh the links now?"

This message is rooted in the fact that you pasted the special object with the link to source. And so is the message based in OLE implementation.

Sorry.

If you ask how to manage image pasting without this LinkToSource then it gets complicated. It is manageable but extremely difficult. Most of the code you have to talk to Excel to get the graphic into Clipboard and make the PasteSpecial without LinkToSource. Are you asking for this demo?

Or are you more interested to let it be LinkToSource and get the code to answer this YesNo dialog when resaving the documents?

I would like to send your mail to EE buddies here to involve in the problem. Is this OK for you?

0
 
CRAKAuthor Commented:
No problem to involve others. The EE buddies you have in mind probably all know me by now, some may even have my e-mail adress already.

If you (or any of the buddies) is willing to write a demo, I'd surely appreciate that! There's no hurry involved, but lets make sure this question does not get closed by a moderator when some are still thinking of a solution.

Copy/paste is likely to be done manually. It's the refreshing of the chart from Excel that requires improvement. I'm afraid if we paste the chart without a link, that updating the right charts is hard if not impossible.

I'd settle for a loop, opening all documents in editmode, and using a SendKeys-like feature to press the ok button again and again.

Thanks!
0
 
Jean Marie GeeraertsApplication EngineerCommented:
Hm, looks interesting. I don't have that much experience in OLE programming, but if I find anything I will get back to you.
In the meanwhile, I'm just listening in...
0
 
CRAKAuthor Commented:
Welcome on board!
Hope it's not already abandoned by everybody else though...
0
 
Jean Marie GeeraertsApplication EngineerCommented:
We can always remind them in the "Tips & Tricks" topic we just started.
0
 
CRAKAuthor Commented:
You have just alerted them again by e-mail.
Thanks!
0
 
Jean Marie GeeraertsApplication EngineerCommented:
Any time. Now I just hope they find a solution for you...
0
 
ZvonkoSystems architectCommented:
Sorry, now I did remember.
After PasteSpecial->PasteLinkToSource
did it work as you described, with the alert on OpenEdit.

Would you accept a SendKey solution for this problem?

0
 
CRAKAuthor Commented:
Sure....
Never knew SendKeys got reintroduced!
0
 
CRAKAuthor Commented:
A wake-up call....
Increasing points to 300
0
 
ZvonkoSystems architectCommented:
Oh, thanks :-)

Beside this one there is a new one today with exactly the same request.

Perhaps two flies with one flap :-)

0
 
CRAKAuthor Commented:
I noticed.... already posted a link to this one!
I wonder which is getting solved 1st (not "faster")... the 50pt or the 300 pt one!
0
 
ZvonkoSystems architectCommented:
buddies first :)
0
 
CRAKAuthor Commented:
Sounds like charity: buddies for buddies!
0
 
ZvonkoSystems architectCommented:
Sounds like joke: ladies first :-)
0
 
androgyny7Commented:
Still looking into this CRAK.
0
 
CRAKAuthor Commented:
Harder than it seems, right?
;-)
There's no real hurry!
0
 
CRAKAuthor Commented:
Or is it forgotten/abandoned?
0
 
Jean Marie GeeraertsApplication EngineerCommented:
I think it's forgotten :-)
Anyway, I still don't know how to do it, so...
0
 
Sjef BosmanGroupware ConsultantCommented:
Hi CRAK,

See http://www.httrack.com/

Cheers!
    Sjef
0
 
CRAKAuthor Commented:
The browser isn't the problem Sjef.
It's how to update the pasted graphs in Notes (Embedded object) when the graphs have changed in Excel (OLE sources).
(By the way, I'm using windows, not Linux/Unix)
0
 
Sjef BosmanGroupware ConsultantCommented:
CRAK,

Ah, if I understand you correctly, you change graphs in Excel, and afterwards you intend to update them in Notes? So you don't open the graph-OLE in Notes, modify it and save it in the document itself?

I haven't got a clue how to do this. Only thing maybe is the following:
- open document in edit-mode
- start the OLE (if that's possible)
- handle the changes using COM (again, if that's possible)
- save the document

For the rest: I dunno, man! I got little experience with OLE myself :(

Sjef
0
 
Sjef BosmanGroupware ConsultantCommented:
CRAK,

Linux/Unix?? You should have read the whole page! See also: http://www.httrack.com/page/2/en/index.html

I used this tool to export a website to a CD 4 years ago. I suppose they thought of many improvements in the meantime. It wasn't easy to do it, but exporting HTML from Notes wasn't necessary anymore. Just create the web-database and httrack wil do the rest.

Sjef
0
 
CRAKAuthor Commented:
Well, when you open a document it already ask If I'd like to update the embedded object. So I clicked "Yes".
For a handfull of doc's that would not be such a hassle. However, we're talking about hundreds of charts per presentation here!
I could easily write an agent to open all those doc's in edit mode and save them again, but I with to get rid of clicking "yes yes yes yes yes.....".

I'll keep the tool in mind, but I already had different one for that part of the job.
0
 
Sjef BosmanGroupware ConsultantCommented:
CRAK,

And a NotesTimer, with SendKeys for an Enter, would that be sufficient?

Sjef
0
 
CRAKAuthor Commented:
Never tried Sendkeys, but have thought of it before. I still have "SendKeys := @Unavailable" set.
I will try.... I'll fist try to work my way through a different task list...  ;-)

I'll see if I can get rid of the timer too! E.g. using a refresh at post open.

Hey... all of a sudden this question is no longer on the abandoned list!
0
 
CRAKAuthor Commented:
Sjef,
Just a note to keep this question open (cleaning up is closing in).
I just tested the code to send a space (=32 or &H20; should be enough to press OK right?) to a field. That works.
Setting a timer, popping a messagebox and waiting for it to close appreared not to work. Does the messagebox even delay timer events? That could get nasty: it's notes that raises the question to refresh....
I'll try that later.... it's getting late.
Points will be yours for the results so far. I'll raise to 500 if I survive the last hurdle....
0
 
Sjef BosmanGroupware ConsultantCommented:
Splendid!
0
 
CRAKAuthor Commented:
Glad I didn't test this on all 300 (+) docs....
They updated ok (simply used GetFirstDocument, EditDocument, sent a space down / up, Save, Close and GetNextDocument, all without timer), but save and close didn't work for some odd reason. All updated doc's remained unsaved/open.
Will work that out later.
Thanks!
0
 
Sjef BosmanGroupware ConsultantCommented:
Dank! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.