Technical Support Forum Index
Technical Support Forum
Access ChemAxon scientists and developers here. For registration and login issues contact website support.

Support Ticket System is replacing forum

This forum was converted into a searchable archive. You cannot add posts here any more. For support please use our new Ticket System.

Create your first ticket
ConvertToOLEAction not updating worksheet.shapes
To watch this topic for replies  Register (enables digests) or give email address:
This topic is locked: you cannot edit posts or make replies.
Display posts from previous:   
    View previous topic :: View next topic    
Author Message
Joshua

Joined: 28 Jan 2011
Posts: 27

View user's profile

Back to top
Link to postPosted: Sun Feb 16, 2014 1:36 amPost subject: ConvertToOLEAction not updating worksheet.shapes Reply with quote

Hello,

I have some code which loops through a range of cells, converts structures to shapes (OLE or picture shapes) and then acts on the resulting shapes. I am having a problem in that after performing the conversion using JChem, excel doesn't recognize that any new shapes have been made. For example (VB.NET code):

'print the # shapes before conversion
System.Diagnostics.Debug.Print("Shapes Before: " & worksheet.Shapes.Count)

        'loop through each cell in the selection and convert if required
	For Each vCell As Excel.Range In Application.Selection
            If vCell.Formula Like "=JCSYSStructure*" Then
                vCell.Select()
                jeJchemForExcel.ExecuteAction("ConvertToOLEAction")
            End If
        Next

'print the # shapes after conversion
System.Diagnostics.Debug.Print("Shapes After: " & worksheet.Shapes.Count)

In the above example the debug output is:

Shapes Before: 1
Shapes After: 1

So, after the call to "ConvertToOLEAction", excel isn't yet aware that new shapes have been added to the shapes list. We can confirm that the "ConvertToOLEAction" did work however, because after the code is finished we can check the structure(s) in the spreadsheet and observe that they have been successfully converted to OLEObjects. in addition, if we immediately run this code snippet again we get the following output:

Shapes Before: 2
Shapes After: 2

Notice that the starting number of shapes is now 2, this is because it is now counting the new OLEObject added by the original call to  "ConvertToOLEAction".

How can we force Excel to recognize the newly added shapes immediately after the first call to  "ConvertToOLEAction" so that I can manipulate and use those new shapes right away?

 

Thanks!

Krisztina
ChemAxon personnel
Joined: 27 May 2011
Posts: 375

View user's profile

Back to top
Link to postPosted: Sun Feb 16, 2014 9:29 amPost subject: Reply with quote

Please, find your question moved to JChem for Excel, Office forum topics.

Best regards,

Krisztina

vhamori
ChemAxon personnel
Joined: 30 Aug 2008
Posts: 912

View user's profile

Back to top
Link to postPosted: Sun Feb 16, 2014 10:35 amPost subject: Reply with quote

Hi Josh,

we try to give you answer from Excel perspective. We will come back to you soon.

Viktor

 

vhamori
ChemAxon personnel
Joined: 30 Aug 2008
Posts: 912

View user's profile

Back to top
Link to postPosted: Sun Feb 16, 2014 2:23 pmPost subject: Reply with quote

Hi Joshua,

checked the issue, and I think this happens because the OLE conversion is executed on a different thread by Excel.

Nevertheless I would have some questions,

1. why do not you select the whole range you would like to convert to OLE shapes? Even if you do not know if the range is a clear structure range or not, it does not matter the Action (ExecuteAction) will be handle it if the particualr cell is a structure cell.

I mean you do not need to filter it with cell.Formula like * "=jcstructure ...".

PLease try it out from teh RIbbon bar (not necessary from code) if you select more cells than you should have the code will skip them, which are not relevant.

2. you can apply Enable/DisableDrawing to get rid of extra refresh of screen and structure, pelase check the API description.

 

3. I gave you a solution in VBA:

Test module

Option Explicit


Public Sub Test()
Dim nativeInterface As Object
Dim objAddin As Office.COMAddIn

Dim rngCell As Excel.Range
Dim rngAll As Excel.Range
Dim lCount As Long
Dim waiter As New Wait

   ' Excel addin is the list of Excel specific automation addins
   ' COM Addins are the real Office plugins loaded in at startup
   Set objAddin = Application.COMAddIns("JChemExcel.JChemExcelAddin")
   Set nativeInterface = objAddin.Object
   'Set jeJchemForExcel = objAddin.Object
   'Set nativeInterface = jeJchemForExcel
   
    Set rngAll = Application.Selection
    For Each rngCell In rngAll
        If rngCell.Formula Like "=JCSYSStructure*" Then
            Call rngCell.Select
            Call waiter.BeginWait
            Call nativeInterface.ExecuteAction("ConvertToOLEAction")
            Call waiter.EndWait
        End If
    Next

  
End Sub

Wait class module:

Option Explicit
Private mlCount As Long

Public Sub BeginWait()
    mlCount = ActiveSheet.Shapes.Count
End Sub

Public Sub EndWait()
Dim fTimer As Single
    fTimer = Timer
    Do While Timer <= (fTimer + 10) And ActiveSheet.Shapes.Count <= mlCount
        DoEvents
    Loop
End Sub

 

Viktor

vhamori
ChemAxon personnel
Joined: 30 Aug 2008
Posts: 912

View user's profile

Back to top
Link to postPosted: Sun Feb 16, 2014 2:28 pmPost subject: Test module code Reply with quote

Hi Joshua,

uploading the code in an xlsm here.

Viktor




 Filename: BI.zip    Filesize: 18.53 KB    Downloaded: 170 Time(s)
 Description:  
Joshua

Joined: 28 Jan 2011
Posts: 27

View user's profile

Back to top
Link to postPosted: Sun Feb 16, 2014 7:50 pmPost subject: Reply with quote

hamoriviktor wrote:

less I would have some questions,

1. why do not you select the whole range you would like to convert to OLE shapes? Even if you do not know if the range is a clear structure range or not, it does not matter the Action (ExecuteAction) will be handle it if the particualr cell is a structure cell.

I mean you do not need to filter it with cell.Formula like * "=jcstructure ...".

PLease try it out from teh RIbbon bar (not necessary from code) if you select more cells than you should have the code will skip them, which are not relevant.

3. I gave you a solution in VBA:

Thanks Victor for your prompt response.

Regarding you first question about why I loop through the range rather than simply call ConvertOLEAction on the entire range: I came across a bug while trying it that way. If I have cells in or near the range being converted that contain comments, calling ConvertOLEAction on the entire range will corrupt those comments and actually overwrite some other cells with the text of the comment. The looping solution I show in my code is my workaround to that bug. Unfortunately I am unable to provide a clean way to reproduce this for you so you can see what I'm talking about (e.g. the behavior of the COM call is not always the same as the behavior of performing the same action through the ribbon UI)

 

Regarding the workaround you provided to allow tot function to wait for the new shapes to be added, I will try your solution and let you know how it works.

 

Thanks again,

--Josh

Joshua

Joined: 28 Jan 2011
Posts: 27

View user's profile

Back to top
Link to postPosted: Sun Feb 16, 2014 10:09 pmPost subject: Reply with quote

Update: I added in the "wait" code using DoEvents and indeed this allowed Excel to update the shapes list appropriately. I had to modify my code for converting structures to shapes however. I finally settled on looping through the range and building a single new range of only the subset of cells that contain structures. Then I perform a single range.select() and "ConvertOLEAction" on this range.  Works great!

Thanks again.

--Josh

Joshua

Joined: 28 Jan 2011
Posts: 27

View user's profile

Back to top
Link to postPosted: Mon Feb 17, 2014 1:34 amPost subject: Reply with quote

Okay, following up on the strange comment-mangling bug: see if you can reproduce this scenario.

  1. start with a new worksheet
  2. Put naphthalene in cell A1
  3. Enter some text in both cells A2 and B2 (I used the string "text")
  4. For both A2 and B2, separately right-click, choose "Insert comment" and enter some text for each comment
  5. Select the range A1:B2
  6. Click the "To marvin OLE" button (this successfully converts to OLE shape)
  7. Again select the range A1:B2
  8. Click the "From image/OLE" button

After this step this is what I observe:

  • OLE shape has been correct converted back to structure
  • Cell B1 now contains the text from the comment from A2
  • Cell A2 still has the red triangle indicating it contains a comment, however the comment text is missing

 

--Josh

vhamori
ChemAxon personnel
Joined: 30 Aug 2008
Posts: 912

View user's profile

Back to top
Link to postPosted: Mon Feb 17, 2014 11:37 amPost subject: Reply with quote

Hi Josh,

thank you for the bug report, it seems to be an issue on our side, for any reason we might delete the comment/images rather than the physical images.

 

I will register a bug for it which will be fixed in the next patch.

Thank you,
Viktor

vhamori
ChemAxon personnel
Joined: 30 Aug 2008
Posts: 912

View user's profile

Back to top
Link to postPosted: Wed Feb 19, 2014 12:03 amPost subject: Reply with quote

Hi Josh,

from 6.2.1 the comment shapes will stay alive after the conversion back to JC4XL structures.

Viktor


Joshua

Joined: 28 Jan 2011
Posts: 27

View user's profile

Back to top
Link to postPosted: Fri Apr 11, 2014 6:57 pmPost subject: Reply with quote

hamoriviktor wrote:

Hi Josh,

from 6.2.1 the comment shapes will stay alive after the conversion back to JC4XL structures.

Viktor

Hi Viktor,

I want to followup on the above bug related to comments in excel:

  1. First of all, the bug described above does seem to be fixed in 6.2.2. Performing the above sequence of steps now preserves comments, however it now changes the size of the comment box (typically only in the vertical dimension) this can cause trouble since in some cases it makes the box smaller than the comment text, which prevents the user from being able to read the whole thing.
  2. There is a new bug in 6.2.2 related to comments. Please follow the instructions below to try to reproduce:
  • Open New worksheet and place benzene in cell B1
  • Right click cell A2 and choose "Add Comment". Add some comment text
  • Toggle the JChem "Show/Hide" button off and then back on (alternatively, you can also just scroll down and then back up)
  • The structure in cell B1 is no longer drawn, however the cell still contains the =JCSYSStructure... formula
  • Right click cell A2 and chose "Delete Comment"
  • Again toggle the "Show/Hide" button
  • The structure in cell B1 reappears

A comment in a cell one down and one to the left of any structure seems to prevent the structure from being drawn. I have also encountered cases where comments present in cells in the column directly to the left of a structure, but multiple rows down also cause this issue, however I cannot consistently reproduce these cases.

 

Can you please try to reproduce this and let me know what you see?

Thanks,

--Josh

 

vhamori
ChemAxon personnel
Joined: 30 Aug 2008
Posts: 912

View user's profile

Back to top
Link to postPosted: Mon Apr 14, 2014 9:15 amPost subject: Reply with quote

Hi Josh,

we will check the issue.

Viktor

Joshua

Joined: 28 Jan 2011
Posts: 27

View user's profile

Back to top
Link to postPosted: Mon Apr 14, 2014 2:31 pmPost subject: Reply with quote

hamoriviktor wrote:

Hi Josh,

we will check the issue.

Viktor

Thanks. Let me know if you can reproduce it from your end. I'm using Excel 2010 & Win7.

Best,

--Josh

Joshua

Joined: 28 Jan 2011
Posts: 27

View user's profile

Back to top
Link to postPosted: Wed Apr 16, 2014 5:37 pmPost subject: Reply with quote

Hi Viktor. My guess is that you can't reproduce my scenario on your end, yes? I'll see if I can find a different set of conditions that cause my structures to disappear. If I find one I will post it for review.

Best,

--Josh

vhamori
ChemAxon personnel
Joined: 30 Aug 2008
Posts: 912

View user's profile

Back to top
Link to postPosted: Fri Apr 18, 2014 7:20 amPost subject: Reply with quote

Hi Josh,

we could reproduce the issue(s) and registered two more on that area. (Your isssue is fixed now)

It will be fixed in 6.3 and 6.2.3. I do not know the schedule yet.

Please be a scrubscriber to this topc: https://www.chemaxon.com/forum/ftopic5166.html where we notify the customers about the schedule (release plan).

Viktor

Joshua

Joined: 28 Jan 2011
Posts: 27

View user's profile

Back to top
Link to postPosted: Fri Apr 18, 2014 7:52 pmPost subject: Reply with quote

Understood. Thanks!

 

--Josh

Joshua

Joined: 12 Feb 2015
Posts: 29

View user's profile

Back to top
Link to postPosted: Wed Sep 07, 2016 8:24 pmPost subject: Reply with quote

joshuahoran wrote:
hamoriviktor wrote:

Hi Josh,

from 6.2.1 the comment shapes will stay alive after the conversion back to JC4XL structures.

Viktor

Hi Viktor,

I want to followup on the above bug related to comments in excel:

  1. First of all, the bug described above does seem to be fixed in 6.2.2. Performing the above sequence of steps now preserves comments, however it now changes the size of the comment box (typically only in the vertical dimension) this can cause trouble since in some cases it makes the box smaller than the comment text, which prevents the user from being able to read the whole thing.
  2. There is a new bug in 6.2.2 related to comments. Please follow the instructions below to try to reproduce:
  • Open New worksheet and place benzene in cell B1
  • Right click cell A2 and choose "Add Comment". Add some comment text
  • Toggle the JChem "Show/Hide" button off and then back on (alternatively, you can also just scroll down and then back up)
  • The structure in cell B1 is no longer drawn, however the cell still contains the =JCSYSStructure... formula
  • Right click cell A2 and chose "Delete Comment"
  • Again toggle the "Show/Hide" button
  • The structure in cell B1 reappears

A comment in a cell one down and one to the left of any structure seems to prevent the structure from being drawn. I have also encountered cases where comments present in cells in the column directly to the left of a structure, but multiple rows down also cause this issue, however I cannot consistently reproduce these cases.

 

Can you please try to reproduce this and let me know what you see?

Thanks,

--Josh

 

Hello,

 

I would like to reopen a bug report related to the issue highlighted above. Presently I am observing the same vanishing structure problem when comments are present in certain cells. To reproduce this issue (in 6.3.2800.674):

 

  • Open New worksheet and place benzene in cell C2
  • Right click cell C4 and choose "Add Comment". Add some comment text
  • Toggle the JChem "Show/Hide" button off and then back on (alternatively, you can also just scroll down and then back up)
  • The structure in cell C2 is no longer drawn, however the cell still contains the =JCSYSStructure... formula
  • Right click cell C4 and chose "Delete Comment"
  • Again toggle the "Show/Hide" button
  • The structure in cell C2 reappears
Zsolt
ChemAxon personnel
Joined: 18 Jun 2010
Posts: 70

View user's profile

Back to top
Link to postPosted: Thu Sep 08, 2016 9:18 amPost subject: Reply with quote

Hi Josh,

I try to reproduce what you reported in your last post and I have some questions:

1. Dou you use the 16.3.2800.674 version of JChem for Office? I think that 6.3.2800.674 is just a typo.

2. Could you please send me some information about the Windows and Office version - including the bit version (64 or 32 bit version)?

Thank you in advance for your reply!

Best regards,

Zsolt

Joshua

Joined: 12 Feb 2015
Posts: 29

View user's profile

Back to top
Link to postPosted: Thu Sep 08, 2016 3:03 pmPost subject: Reply with quote

zskribanek wrote:

Hi Josh,

I try to reproduce what you reported in your last post and I have some questions:

1. Dou you use the 16.3.2800.674 version of JChem for Office? I think that 6.3.2800.674 is just a typo.

2. Could you please send me some information about the Windows and Office version - including the bit version (64 or 32 bit version)?

Thank you in advance for your reply!

Best regards,

Zsolt

Hi Zsolt. Yes it was a typo on my part. It should be "16.3.2800.674"

I am using Windows 10 with Excel 16.0.6741.2063 (32 bit).

I should note that I recently upgraded from Excel 2013 and I noticed the same issue with that version of excel. Also I recently downgraded JChem for Excel from the most current version and I noticed that the structure disappearing issue was a much bigger problem in the most recent JCHEM4Excel build -- the downgrade actually helped.

I think this disappearing structure bug has been around in some form for years. It was briefly fixed in early 2014 (see previous posts), but the problem has been incrementally creeping back ever since.

Thanks.

Zsolt
ChemAxon personnel
Joined: 18 Jun 2010
Posts: 70

View user's profile

Back to top
Link to postPosted: Fri Sep 09, 2016 10:24 amPost subject: Reply with quote

Hi Josh,

thank you for the information about the Windows and Office version.

We can reproduce the vanishing structure problem if the cursor remains in the comment field and the added comment field is still in edit mode.

Can you reproduce the problem only if the comment field is in edit mode?

Of course we have registered the issue in our issue tracking system and we will let you know if we will have the fix for the problem.

Best regards,

Zsolt

Joshua

Joined: 12 Feb 2015
Posts: 29

View user's profile

Back to top
Link to postPosted: Fri Sep 09, 2016 3:45 pmPost subject: Reply with quote

Hi Zsolt,

The disappearing structure happens outside of the comment edit mode -- so you were unable to reproduce what I am seeing. I have spent more time on this and now think that this bug is state-dependent and that's why you couldn't reproduce. I think I have tracked down the required state to reproduce the problem -- which is linked to the fact that I am constantly converting structures to and from images for the purposes of copying to other applications. Try to reproduce using the modified steps below.

  1. Open New worksheet and place benzene in cell C2
  2. Select cell C2, and click "To image" in the JChem toolbar
  3. With cell C2 still selected, click "From Image"  in the JChem toolbar
  4. Right click cell C4 and choose "Add Comment". Add some comment text
  5. Toggle the JChem "Show/Hide" button off and then back on (alternatively, you can also just scroll down and then back up)
  6. The structure in cell C2 is no longer drawn, however the cell still contains the "=JCSYSStructure..." formula
  7. Right click cell C4 and chose "Delete Comment"
  8. Again toggle the "Show/Hide" button
  9. The structure in cell C2 reappears

Notes:

  • You can also switch the order and perform the to/from image after you have added the comment. You should see the same disappearing structure phenomenon.
  • This problem propagates across all open sheets and workbooks(!). Meaning that if you open two new workbooks, each containing two sheets with a structure in C2 and a comment in C4, and then you convert only one of the structures to and then from image, all four structures in cell C4 across all worksheets and workbooks will disappear. 
  • This issue isn't restricted to cells C2/C4 -- I have observed this behavior across many locations. The C2/C4 combo is just what I've been using for testing.

 

Thanks.

Zsolt
ChemAxon personnel
Joined: 18 Jun 2010
Posts: 70

View user's profile

Back to top
Link to postPosted: Mon Sep 12, 2016 7:36 amPost subject: Reply with quote

Hi Josh,

thanks a lot for the detailed description! 

I can reproduce exactly the same behaviour what you reported. We will investigate how to solve the problem.

Best regards,

Zsolt

Joshua

Joined: 12 Feb 2015
Posts: 29

View user's profile

Back to top
Link to postPosted: Wed Sep 14, 2016 7:46 pmPost subject: Reply with quote

Thanks Zsolt. Let me know when the fix is released and I will upgrade to test it out for you.

--Josh

Zsolt
ChemAxon personnel
Joined: 18 Jun 2010
Posts: 70

View user's profile

Back to top
Link to postPosted: Thu Sep 15, 2016 11:24 amPost subject: Reply with quote

Hi Josh,

thank you for your help!

We have the fix for the vanishing structure problem. The next week release will contain the fix according to our plan.

Best regards, Zsolt

Zsolt
ChemAxon personnel
Joined: 18 Jun 2010
Posts: 70

View user's profile

Back to top
Link to postPosted: Fri Sep 30, 2016 9:22 amPost subject: Reply with quote

Hi Josh,

our latest release contains the fix for the vanishing structure problem what you have reported.

https://www.chemaxon.com/jchem4office/changes/changes.html
http://www.chemaxon.com/download/jchem-for-office/

Thank you again for your help to investigate and to understand the problem!

Best regards,

Zsolt

Joshua

Joined: 12 Feb 2015
Posts: 29

View user's profile

Back to top
Link to postPosted: Fri Sep 30, 2016 2:41 pmPost subject: Reply with quote

Zsolt,

 

I installed the update yesterday. It looks good so far. I'll let you know if I find any new behavior.

 

Thanks!

This topic is locked: you cannot edit posts or make replies.
Page 1 of 1


To watch this topic for replies   Register (enables digests) or give email address  
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum