Paste OLE Object dialog settings

I am trying to use the .sendcommand key-in "clipboard paste" from a vba to embed a selected portion of a temporary excel spreadsheet into a MicroStation model and it seemed to be working fine. However I've found that if settings within the Paste OLE Object dialog have been changed from Paste as: Embedded, Method: By Size, the command won't run correctly. How can I specify the correct settings before I run the command? I don't see any documentation or anything in user preferences to do this.

Help is appreciated,

Stephanie

Parents
  • Typically my next step would be to see if the VBA macro recorder could identify any usable access strings you could get/set via CExpression.  However this dialog does not provide any access strings for use.  There is an "OLECNTR PASTEMETHOD" keyin that should allow you to set the paste method using either ("ByObjectSize" or "By2CornerPoints") as an argument although I do not see either method activating as one would expect.

    HTH,

    Bob



  • Hi Bob,

    Thanks for the quick response. That looks promising. Unfortunately when I tried implementing the "copy paste" command, it appears to paste the clipboard contents (a range of cells from an Excel file)  as textnodes without any of the spreadsheet formatting. If I do a manual copy to the clipboard and then use the edit paste command from MicroStation it looks like a table. Do you know how I would do this programmatically? After I get there, I can try the keyin you suggested.

    Stephanie

Reply
  • Hi Bob,

    Thanks for the quick response. That looks promising. Unfortunately when I tried implementing the "copy paste" command, it appears to paste the clipboard contents (a range of cells from an Excel file)  as textnodes without any of the spreadsheet formatting. If I do a manual copy to the clipboard and then use the edit paste command from MicroStation it looks like a table. Do you know how I would do this programmatically? After I get there, I can try the keyin you suggested.

    Stephanie

Children
  • Sorry, there is no convenient API available.  You also may want to consider an alternate workflow because the OLE object pasted in this way provides only a "formatted" picture, vs. usable vector (linked) graphics like DDE Links copy/paste option provides (though no formatting).

    If you want more control and being able to dynamically update the image, consider having VBA code in Excel to export the Excel selection as a picture to either a network share, or local if the spreadsheet is local.  Then in MicroStation create a Raster Reference attachment and position the exported Excel image to a known/common location.  Using this workflow your design files would never need to be manually or programmatically updated, since they would always be "current" to the exported image (with formatting) from Excel.

    If you want to use the OLE image in MicroStation from the clipboard using the object's "By Size" setting, you will need to drive the MicroStation interface using DMSG keyins (code snip below).  You would have to change the action/sequence to initiate a "by corners" setting, but that could be done easily - by going "down" one item, then back "up".

    Sub TEST_ClipboardPasteExcelSelection()    ' Start clipboard paste command, verifying OLE object is present
        CadInputQueue.SendCommand "CLIPBOARD PASTE"
        If CommandState.CommandName <> "Paste OLE Object" Then
            MsgBox "OLE object not present in Clipboard. Exiting.."
            Exit Sub
        End If
       
        ' Work-around for OLECNTR PASTEMETHOD By2CornerPoints and ByObjectSize not operating.
        ' Method: "By Size" (a.k.a. ByObjectSize)
        CadInputQueue.SendCommand "DMSG FOCUSDIALOG -79"    ' Send Focus to ToolSettings
        CadInputQueue.SendCommand "DMSG CURSOR NEXTFIELD"   ' Tab key
        CadInputQueue.SendCommand "DMSG ACTION OKAY"        ' Enter key
        CadInputQueue.SendCommand "DMSG CURSOR LINEEND"     ' End key
        CadInputQueue.SendCommand "DMSG ACTION OKAY"        ' Enter key
       
        ' Coordinate to place (center of) clipboard object at.
        Dim ptCenter As Point3d: ptCenter = Point3dFromXYZ(0, 0, 0)
        CadInputQueue.SendDataPoint ptCenter
       
    End Sub

    HTH,
    Bob



    Answer Verified By: Stephanie Doherty 

  • Bob,

    I think your proposed workaround could do what I want, but how would I know (programmatically) what the current selections are in the Paste OLE Object dialog box, so that I only change them if needed?

    Thanks,

    Stephanie

  • Unknown said:
    Sorry, there is no convenient API available.

    There is no direct Bentley MicroStation programming API to set the clipboard options.  You will need to drive the interface using techniques like provided.  Note:  If you tried the above and it did not work, I have modified the code to use the "End" key to make sure that the bottom "By Size" option is used consistently, vs. the prior Cursor Down Arrow key that may wrap to the top selection - sorry for that oversight.

    IMHO. Exporting the image using Excel's object model (you can access/reference directly from the MicroStation VBA engine - if you choose to) and save the image as a .jpg to a local or network share and reference as a MicroStation Raster Image should provide a lot more control.  Imagine if the content of one or more of the images you paste - updates.  Now you will need to write code to try and correctly identify one or more OLE images embedded within one or more MicroStation design file models; where some may or may not need updating. If you have raster references you simply update the image and all design files are automatically updated.  If you need to export a single deliverable to a contractor (e.g. a single design file) you can merge to master.

    HTH,
    Bob



    Answer Verified By: Stephanie Doherty 

  • Bob,

    One of the other options I need to specify in that window is that the pasted object be embedded. Since this is the middle option of the 3, I can't us the LINEBEGIN or LINEEND cursor simulations. I tried using both of them, followed by arrow keys to get to Embedded, but it didn't seem to take. Is there some other option I can use?

    Thanks,

    Stephanie

  • Hi Stephanie,

    Sorry for the delayed response.  I have been away on several other projects and issues.  I have added a quick reference for MicroStation's DMSG keyins that map the DMSG key-in to what you would use on the in the MicroStation interface (mostly - keyboard or mouse actions).  Please try using the keyboard combinations, then add the same number of DMSG keyins and the equivalent action found in the attached document (dmsg-keyin-equivalents.pdf).

    HTH,
    Bob



  • Mr. Hook,

    I downloaded your dmsg keyins but I am still unable to set the Paste OLE Object parameters using dmsg.  I have discovered that ALT-DownArrow seems to activate the drop-down menus but there is no ALT-DownArrow listed in your DMSG Keyin Equivalents.

    Any suggestions as to how I can write VBA to paste a link to an Excel spreadsheet automagically?

    Thanks,

    Al

  • Try to "drive the interface" by doing the necessary actions - only using the keyboard keys listed below:

    • Tab (Use to cycle through each dialog item)
    • Enter (Use to Select the item in focus - or it's corresponding text field)
    • Home (Use to navigate to the top of a list)
    • End (Use to navigate to the bottom of a list)
    • Arrow Keys: Up, Down, Left, or Right (Make sure to keep a count of of each required keystroke to choose the correct option)

    Each of the keys listed above have a corresponding DMSG keyin listed in the .pdf file.

    I think your Alt-Down is equivalent to simply using the "Enter" above once you have "Tabbed" to the appropriate item (or text of the item) you want to change.

    HTH,
    Bob



  • Mr. Hook,

    Unfortunately I've already tried "driving the interface" as you say.  Using DSMG ACTION OKAY to simulate the Enter key does nothing.  All subsequent commands fail.  But ALT-DownArrow will correctly "drive the interface" and open the Paste OLE Object menus (Paste as:, Method:) for selection.  But I'm guessing there's no DSMG equivalent to ALT-DownArrow.

    If there is some setting in the million possibilities of MicroStation that would enable the DSMG ACTION OKAY to work, I would appreciate any suggestions you care to make.

    Thanks,

    Al

  • Technically at this point we should start a new thread since we are troubleshooting/defining a "new" problem.  Either through a new thread or this one, please post your MicroStation or product version, the exact command being used, and a screen capture clearly showing what exact option you are interacting with and the exact value you are trying to set.  With that information I (or others) should be able to help in providing more precise steps or options required to help you towards your solution.

    Thank you,

    Bob



  • Mr. Hook,

    My MicroStation is version 08.11.07.443; My InRoads is version 08.11.07.428.  I believe I've discovered the source of the problem.  Running the macro from the VBA interface causes the focus to immediately return to the VBA interface when the VBA line containing DSMG ACTION OKAY completes.  That is why subsequent commands fail.  The focus is no longer set on the Paste as: menu and subsequent commands are not directed there.  Running the macro without the VBA interface using the MicroStation keyin "VBA run macroname" fixes that problem.

    My next problem is I can't figure out how to open the "Show Extended Settings" on the Past OLE Object menu using DMSG Keyin Equivalents.  I need to set the scale.  Any suggestions?

    Thanks,

    Al