Solved

How to update OLE graphs from Excel in documents

Posted on 2002-05-11
42
1,543 Views
Last Modified: 2013-12-18
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)?
0
Comment
Question by:CRAK
  • 19
  • 7
  • 4
  • +5
42 Comments
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7003692
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
 
LVL 13

Author Comment

by:CRAK
ID: 7004232
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7004352
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7004353
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
 
LVL 13

Author Comment

by:CRAK
ID: 7004737
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7004761
Are you launching the ole object at open ?
Did you check the property update object from document for ole ?
0
 
LVL 13

Author Comment

by:CRAK
ID: 7006898
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7017442
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
 
LVL 10

Expert Comment

by:zvonko
ID: 7026407
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
 
LVL 13

Author Comment

by:CRAK
ID: 7034492
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
 
LVL 13

Author Comment

by:CRAK
ID: 7076490
Zvonko, have you received my message?
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7076577
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
 
LVL 13

Author Comment

by:CRAK
ID: 7077792
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
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7258631
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
 
LVL 13

Author Comment

by:CRAK
ID: 7262494
Welcome on board!
Hope it's not already abandoned by everybody else though...
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7263971
We can always remind them in the "Tips & Tricks" topic we just started.
0
 
LVL 13

Author Comment

by:CRAK
ID: 7265449
You have just alerted them again by e-mail.
Thanks!
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7266687
Any time. Now I just hope they find a solution for you...
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 7542458
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
 
LVL 13

Author Comment

by:CRAK
ID: 7542556
Sure....
Never knew SendKeys got reintroduced!
0
 
LVL 13

Author Comment

by:CRAK
ID: 7731408
A wake-up call....
Increasing points to 300
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 63

Expert Comment

by:Zvonko
ID: 7731463
Oh, thanks :-)

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

Perhaps two flies with one flap :-)

0
 
LVL 13

Author Comment

by:CRAK
ID: 7731492
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
 
LVL 63

Expert Comment

by:Zvonko
ID: 7732368
buddies first :)
0
 
LVL 13

Author Comment

by:CRAK
ID: 7732391
Sounds like charity: buddies for buddies!
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 7732637
Sounds like joke: ladies first :-)
0
 

Expert Comment

by:androgyny7
ID: 7790450
Still looking into this CRAK.
0
 
LVL 13

Author Comment

by:CRAK
ID: 7790613
Harder than it seems, right?
;-)
There's no real hurry!
0
 
LVL 13

Author Comment

by:CRAK
ID: 8339777
Or is it forgotten/abandoned?
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 8339816
I think it's forgotten :-)
Anyway, I still don't know how to do it, so...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10309027
Hi CRAK,

See http://www.httrack.com/

Cheers!
    Sjef
0
 
LVL 13

Author Comment

by:CRAK
ID: 10309064
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10309335
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10309358
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
 
LVL 13

Author Comment

by:CRAK
ID: 10309456
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10309594
CRAK,

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

Sjef
0
 
LVL 13

Author Comment

by:CRAK
ID: 10309682
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
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
ID: 10309957
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
 
LVL 13

Author Comment

by:CRAK
ID: 10585150
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10587310
Splendid!
0
 
LVL 13

Author Comment

by:CRAK
ID: 10668245
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10668296
Dank! :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now