ConvertToOLEAction not updating worksheet.shapes

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!

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

Best regards,

Krisztina

Hi Josh,

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

Viktor

 

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

Hi Joshua,

uploading the code in an xlsm here.

Viktor

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

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

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

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

Hi Josh,

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

Viktor

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

 

Hi Josh,

we will check the issue.

Viktor

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

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

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

Understood. Thanks!

 

--Josh

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: 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. 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

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

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.

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

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.

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

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

--Josh

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

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

Zsolt,

 

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

 

Thanks!