none
Inserting Word-Documents to Excel-Sheets as OleObjects programatically by COM interface RRS feed

  • Question

  • Hello everybody,

    We have to insert Word-Documents as embedded Ole-Objects to an Excel-Sheet. In order to do so, we use to following VB.net code:

     

      Private myDoc As Object
    
      Private myWorksheet As Object
    
    ' ...
    
        Dim fileName As String = "[Somewhere on HardDisk]"
    
        myDoc = myWord.Documents.Open(fileName)
    
        myWorksheet.OLEObjects.Add(fileName:=fileName.ToString, Link:=False, DisplayAsIcon:=False) ...
    
      ' Followed by some code storing the modified Workbook (the one, ' myWorksheet' belongs to) to disk.
    
    
    

     

    In contrast to what happens, if this is done be the Excel user interface, the information about the OleObjects seems not to be stored to the concering Excel file which leades in two error messages, displayed when the file is opened and not being able to edit the OleObjects within the Excel file any more.

     

    Please have a look to the sample file (which I would like to attach here, but that seems to be impossible - please just answer if you want it) and check the properties of the OleObjects in column 'P' of Sheet2. (Please don't care about the german text within the file, which is regardless in terms of the occoring problem.)

     

    What bothers most are the error messages, displayed when the file is opened (by ignorant users) later. So, if there's a solution for getting rid of those messages by the use of some VB code, we would be almost happy. Of course, it would be nice to have to complete solution, though to ability to edit the embedded OleObjects of not that important.

     

    Thank you in advance.


    Stefan Falk
    Wednesday, December 8, 2010 2:15 PM

Answers

  • Hallo Stefan

    Unfortunately, you don't really give any details about what the actual problems are. You could, at least, give the text of the error messages...

    However, when I look at your code snippet I notice something right away that could be causing problems: you're opening the documents first in the Word application. This will put a "lock" on the files so Excel probably cannot embed them properly.

    Comment out the following line and see if you don't get a result more like you expect:

    'myDoc = myWord.Documents.Open(fileName)


    Cindy Meister, VSTO/Word MVP
    Wednesday, December 8, 2010 7:02 PM
    Moderator

All replies

  • Hallo Stefan

    Unfortunately, you don't really give any details about what the actual problems are. You could, at least, give the text of the error messages...

    However, when I look at your code snippet I notice something right away that could be causing problems: you're opening the documents first in the Word application. This will put a "lock" on the files so Excel probably cannot embed them properly.

    Comment out the following line and see if you don't get a result more like you expect:

    'myDoc = myWord.Documents.Open(fileName)


    Cindy Meister, VSTO/Word MVP
    Wednesday, December 8, 2010 7:02 PM
    Moderator
  • Hello Cindy,

    I am sorry for the delay, I was out of office and could not react. My apologies.

    The error message is (translated from german in our own words): Excel found unreadable content in [FileName]. Do you want to recover contents of this workbook? If you trust the source of this workbook, click Yes.

     

    After clicking Yes, a link is provided to the following log XML file:

     

       <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

    - <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

          <logFileName>error034760_01.xml</logFileName>

          <summary>Fehler in Datei 'C:\Users\ralf\Desktop\export.xls'</summary>

    -     <additionalInfo>

              <info>Die Daten für ein oder mehrere Objekte sind verloren gegangen.</info>

          </additionalInfo>

      </recoveryLog>

     

    Translated in our own words:

     

       <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

    - <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

          <logFileName>error034760_01.xml</logFileName>

          <summary>Error in file 'C:\Users\ralf\Desktop\export.xls'</summary>

    -     <additionalInfo>

              <info>Data of one or more objects has been lost.</info>

          </additionalInfo>

      </recoveryLog>

    I hope this may help. Anyway, we'll try your suggestions. Again, apologies for the delay.

     


    Stefan Falk
    Wednesday, December 15, 2010 12:19 PM
  • Hello Cindy,

    your suggestion gave the solution: Of course, we have to close the Word file before embedding it (how stupid we are...). Thanks a lot!

     


    Stefan Falk
    Thursday, December 16, 2010 10:56 AM
  • your suggestion gave the solution: Of course, we have to close the Word file before embedding it (how stupid we are...). Thanks a lot!

    Hi Stefan

    Glad it worked for you and that you're up and running :-)


    Cindy Meister, VSTO/Word MVP
    Thursday, December 16, 2010 3:32 PM
    Moderator