Dealing with an embedded excel spreadsheet

Hi,

I am trying to work with an embedded excel spreadsheet and I am having a lot of problems.

First, when a user inputs data into the spreadsheet then saves and closes, those changes are not visible to anyone else who opens the document, unless they first open the excel spreadsheet. The changes remain visible after the spreadsheet is closed again. I tried to resolve this by "activating" the embedded object. I tried using the following code with I copied straight out of the help file which said it would activate the first embedded object (if any) in a document. However, when I try to run it, I get the error "Object variable not set."

Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument
Dim item As Variant
Set uidoc = workspace.CurrentDocument
Set doc = uidoc.Document
Set item = doc.GetFirstItem("Body")
If item.EmbeddedObjects(0) Is Nothing Then
   Messagebox "No embedded object in document"
   Exit Sub
End If
If item.EmbeddedObjects(0).Type <> EMBED_OBJECT Then
   Messagebox "Object not an embedded object"
   Exit Sub
End If
Call item.EmbeddedObjects(0).Activate(True)

Once this issue is resolved, I would like to be able to search the data contained in all of the excel spreadsheets contained in all of the documents. Any ideas on how to do this?

Thanks,

Zaphod.
LVL 7
Z_BeeblebroxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HemanthaKumarCommented:
Hi

Try to use getobject method to get the handle of the spreadsheet and then use activate method to activate the object.

eg:
Dim workspace as New NotesUIWorkspace
Dim uidoc as NotesUIDocument
Dim handle as Variant
Set uidoc = workspace.CurrentDocument
Set handle = uidoc.GetObject( "Microsoft Excel" )
.........

Good Luck
~Hemanth
0
Z_BeeblebroxAuthor Commented:
OK, I managed to activate my excel spreadsheet. The script that Hemanth provided does not work because (I think) I am running R4 and the script you provided works in R3. For those that are curious, the following script should activate all embedded objects in R4.

Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument
Set uidoc = workspace.CurrentDocument
Set doc = uidoc.Document
If doc.HasEmbedded Then
   Forall o In doc.EmbeddedObjects
      o.Activate(True)
   End Forall
End If

However, this didn't help. After all of that, when I activate the spreadsheet, my HD grinds and a window pops up briefly... but I still can't see the data!!! I still have to open then close the spreadsheet! So what can I do??

Zaphod.
0
HemanthaKumarCommented:
Hi

If the excel is a embedded object in the form, then this code works
........(currentdDoc is the uidoc.Document)

Set object = currentDoc.EmbeddedObjects(0)
     Set excelDocObject = object.Activate(False)
     Set excelAppObject = excelDocObject.Application
     Set excelChart = excelDocObject.Sheets("Chart1")  'Object variable for the main chart
     Set excelminiChart = excelDocObject.Worksheets("sheet1").ChartObjects(1).chart

.........


Good Luck
~Hemanth
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

HemanthaKumarCommented:
HI

    Forgot to tell u one thing, the form with embedded object should have Store Form in Document checked. You should save the document in order to get handle of the embedded object.

Hope this helps

~Hemanth
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Z_BeeblebroxAuthor Commented:
ARRRGGGGGG, after all that, all I had to do was store the form in the document.......... None of that activating stuff was necessary in order for it do immediately display the info. Thanks Hemanth, at least it is working now.

Well, in the process of all of this, I discovered another problem. I need to be able to detect if the object is open for editing, since the user must use a button to exit the form which performs a variety of tasks including updating a few fields, sending an e-mail, etc. The problem is that this only works if the object has been closed, so I need to be able to detect if it is open so I can close it before doing everything else... any ideas?

Zaphod.
0
Z_BeeblebroxAuthor Commented:
Sorry it took so long to give you the points, I have been busy.

Zaphod.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.