none
Saving OLEObject content to a file

    Question

  • Greetings, colleagues

    I have spent quite some time on trying to solve the following problem: I get the OLEObject from the Excel worksheet, e.g.

    Excel.OLEObject  myOLEObject = (Excel.OLEObject) currentSheet.OLEObjects(1);

    I need to to save this myOLEObject object back to file on hard drive (in my Excel 2003 managed code extension).

    The only thing I know about the OLE object is that it was created in Excel 2003 through "Insert -> Object -> Create from file". So the exact data type of this OLE object is unknown. Casting it to Bitmap or Word document does not solve the problem. I.e. it may be a bitmap image, a word document, etc. including a generic binary file of unknown format.

    Manually an Excel 2003 user can click on this embedded object and then in the context menu choose "Package Object -> Edit Package -> File -> Save content" to save the embedded object back to file. That is why I was hoping there must be some Save() method so that I could simply write, say, myOLEObject.Save("C:\myObject.bin"). But there is no Save() method for OLEObject. I also tried to use Windows clipboard to retrieve object from the clipboard:

    myOLEObject.Copy();

    IDataObject  iData = Clipboard.GetDataObject();

    Object  myObj = (Object) iData.GetData("Office Drawing Shape Format");

    But then I can only save myObj to a file using binary formatter, which saves additional file header and footer together with the object content. This causes another problem of parsing the stored file format (and I could not find the format description either):

    if (iData.GetDataPresent("Office Drawing Shape Format"))

    {

       FileStream fstr = new FileStream("./FileFromExcel.bin", FileMode.Create);

       BinaryFormatter myFormatter = new BinaryFormatter();

       myFormatter.Serialize(fstr, myObj);

       fstr.Close();

    }

    I could equally use "Embedded Object" as data format to get data from clipboard, but the result is almost the same.

    I would be very grateful if anyone who faced similar problem would give me advice / a hint how to solve my problem. Again clipboard use may not be necessary, but I just don't know how to get object data from OLEObject (i.e. to which class should I cast OLEObject.object, right?) and then to save it to file by means of FileStream.

    Many thanks for your help, 

    Anton

    Tuesday, August 22, 2006 1:15 PM

Answers

  • Hi Anton,

     

    Your problem is that an Excel.OLEObject is *not* an IOLEObject.  It is a wrapper around an IOLEObject.  The wrapper itself is an Excel object that doesn't implement any interesting interfaces (which is why your casts were failing).  To get to the underlying IOLEObject, you need to call the Object property on your Excel.OLEObject (i.e. myOleObject.Object).  Once you do that, you can cast to the persistence interfaces as required.

     

    You might be interested in reviewing the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2106198&SiteID=1  This particular example is Word, and the embedded object is Excel.  Instead of using the persistence interfaces to save, we just use IDispatch to call Excel's save method.  In any event, the code to get at the underlying OLE object is very similar.  Instead of calling OLEFormat.Object, you'll need to call OLEObject.Object.

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

     

    Wednesday, November 07, 2007 9:12 PM
    Answerer
  • Hi,

     

    You need to be a bit more specific about what you tried, what kind of embedded objects you are working with, and what you mean by "it didn't work" before I can really comment. 

     

    That said, an embedded object is not a document file; it is a persisted representation of the object that is controlled entirely by the object and written to whatever storage the container provides.  Depersistence is handled entirely by the object and may well be through an entirely different mechanism than loading an ordinary document file.  In other words, you can't expect to write persisted bits to a file and then turn around and open the file in the appropriate application.  While in some cases this might work, there is nothing in the OLE specification that requires the persistence format to match the file format.  In addition because it represents object state, the persisted bits will likely include information beyond the contents of the file. 

     

    In all likelihood, what you have is a blob that wraps the actual document file around some additional information relating to the object itself.  If that persistence format happens to be documented, you would just need to do some surgery on the output to extract the actual document file bits.  If it isn't documented, see the next paragraph.

     

    In the link I mentioned above, we access the running embedded object via IDispatch and then use Excel's own Save method to force it to write the bits out to a valid document file.  If that isn't an option, the only way to reconstitute the object would be to load the application, QueryInterface for the appropriate IPersistXXX interface and then call the Load method, passing in the persisted blob.

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

     

    Friday, November 09, 2007 4:38 PM
    Answerer
  • Hi Kienn,

     

    There is a sample that saves an Excel file embedded in a Word document which is provided in the link I referenced in one of my previous posts to this thread.  If your embedded object supports IDispatch and exposes a Save mechanism, the easiest solution would be to activate the embedded object and just call that.  Otherwise you will have to use one if the IPersist interfaces to write the object out to the corresponding storage that you provide.  As I mentioned to the last poster, there is no requirement that this persistence format match the document file format.  If it doesn't, the only way to load it back into the application will be to launch the application and then call IPersistXXX.Load--passing the persistence data back in.  I don't have a sample on how to do that at the moment, but maybe if I get some time next week I'll blog about it.

     

    BTW if you are embedding the exe as a package, there is no easy solution for extracting it.  It can be done, but you are in for a tremendous amount of work.  See the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2295536&SiteID=1

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

     

    Friday, November 09, 2007 4:43 PM
    Answerer

All replies

  • Did you try getting a hold of object's IPersistStorage interface, or IPersistStream or IPersistFile?

    Usually persistable OLE objects implement one (or more) of the above interfaces.

    Wednesday, August 23, 2006 6:30 AM
  • Misha, spasibo.

    I tried this before but could use neither of the above interfaces. It would be great to know how to do it properly since I am not very strong in low level interfaces. I tried the following:

    System.Runtime.InteropServices.UCOMIPersistFile myPersistableObject = (System.Runtime.InteropServices.UCOMIPersistFile) myOLEObject;

    But I always get the invalid cast exception. Same thing happens if I try to cast to UCOMIStream or UCOMIMoniker interfaces. I also tried to use OLEObject.Object property, but it does not return any object or value - looks like it is not suitable for a generic file kept as embedded objects in a Excel sheet.

    Would appreciate any help or a correct sample how to cast to IPersistFile interfaces (including which object to cast to it).

    If I can't solve it "gracefully" I will have to parse the binary data stream (of a data format whose description I could not find) to cut the file body.

    Misha, thanks for your attempt anyway,

    Anton

     

    Wednesday, August 23, 2006 11:19 AM
  • As I planned I solved this problem in a "terrible" way. Since I do not know how to solve it better, I have decided to publish my solution - maybe it will be helpful for those who face similar issue or maybe somebody will share his experience and suggest a more elegant approach.

    So the initial conditions are: I have an Excel workbook which has a generic file embedded as OLE object (packaged object) into its worksheet. I need to develop a C# code in a VSTO project to save this embedded file to the hard disk. I will have one little simplification here - though the embedded file is generic, I will know its extension (e.g. "zip" or "bin") in my code. For easier explanation I will use "zip" extension.

    My "terrible" solution in a nutshell:

    1) Since I cannot use OLEObject.Object property (I could not find how to deal with it or how to cast it to some persistable interface) copy the OLE object to the clipboard ( OLEObject.Copy() )

    2) Get the clipboard data and to the object cast to IDataObject interface

    3) Get the object data from the IDataObject object specifying "Native" format (on which I could not find description).

    4) Create a memory stream from the just obtained "Native" format object data.

    5) Parse the obtained memory stream and determine the stream position where the actual file content starts and ends.

    6) Extract and copy the actual file content from memory stream to filestream (thus saving the ZIP file to the disk)

    Here is the code which I develop to implement the above solution:

    using Microsoft.Office.Interop.Excel;

    /* ... */

    private void CopyEmbeddedFileToDisk(string fileLocation)
    {

      /* Some code */

      try
      {

       /*
        * Retrieves OLE Object from worksheet
        */
       OLEObject embeddedOLEObject = (OLEObject) internalWorkSheet.OLEObjects(1);
      
       /*
        * Copies data from Excel OLE Object to clipboard and retrieves it back as object
        */
       embeddedOLEObject.Copy();
       IDataObject iData = Clipboard.GetDataObject();
       string objType = "Native";
       Object objToSave = iData.GetData(objType);
       Clipboard.SetDataObject("");
      
       /*
        * Creates stream to process object data.
        */
       MemoryStream objStream = (MemoryStream) objToSave;
       long sizeOfMyObj = objStream.Length;
      
       /*
        * Specifies embedded file extension, which is stored in object stream "header" 3 times in filenames
        */
       string fileExtension = ".ZIP";

       /*
        * Finds 3 occurrences of fileExtension. The "Native" format has a content header, which
        * contains the filename 3 times: one pure filename and then 2 filenames with full path.
        * After last filename there are 5 extra bytes and then the actual file content starts. 
        * The actual file content ends 2 bytes before the "Native" format data ends 
        */
       long startOffset = 5L;
       long endOffset = 2L;
       long foundPosition = 0;
       for (int occurrence = 1; occurrence <= 3; occurrence++)
       {
        foundPosition = FindInStream(fileExtension, false, foundPosition, objStream) + fileExtension.Length;
        if (foundPosition == -1)
         break;
       }

       /*
        * If the specified string was found 3 times, then goes on and extracts the actual file content
        */
       if (foundPosition != -1)
       {
     
        /*
         * Position where actual file content starts.
         */
        long fileContentStartPosition = foundPosition + startOffset;
       
        /*
         * Copying file content from the object stream to the filestream
         */
        byte b;
        FileStream fileStream = new
        FileStream(fileLocation, FileMode.Create);
        objStream.Seek(fileContentStartPosition, SeekOrigin.Begin);
        for (long i = 1; i <= sizeOfMyObj - fileContentStartPosition - endOffset; i++)
        {
         b = (byte) objStream.ReadByte();
         fileStream.WriteByte(b);
        }
        objStream.Flush();
        objStream = null;
        fileStream.Flush();
        fileStream.Close();
        File.SetAttributes(fileLocation, FileAttributes.Normal);
       }
      }

      catch (Exception error)
      {
       /* some code */ MessageBox.Show("Error: " + error.Message + "\n\n" + error.StackTrace.ToString());
      }
                                                                                     
     }
     
     /// <summary>
     ///  Finds a string of characters in a stream.
     /// </summary>
     /// <param name="textToFind">
     ///  String to find, e.g. ".ZIP"
     /// </param>
     /// <param name="caseSensitive">
     ///  Specifies whether the search will be case sensitive (true) or not (false)
     /// </param>
     /// <param name="startPos">
     ///  Position in stream to start search from
     /// </param>
     /// <param name="sourceStream">
     ///  Source stream where the search will be performed
     /// </param>
     /// <returns>
     ///  Position in stream where the search string is found. Or (-1) if the string is not found
     /// </returns>
     private long FindInStream (string textToFind, bool caseSensitive, long startPos, MemoryStream sourceStream)
     {
      /*
       * Converts sought string to an array of chars and gets the array length
       */
      char[] charsToFind = textToFind.ToCharArray();
      long textToFindLength = textToFind.Length;

      /*
       * Converts source stream to an array of bytes and gets the array length
       */
      byte[] bufArray = sourceStream.ToArray();
      long bufLength = bufArray.Length;
      
      /*
       * Performs string search in the source stream
       */
      for (long x = startPos; x < bufLength; x++)
      {
       bool firstByteFound;
       /*
        * Finds out whether the first byte of the string is found in the stream
        */
       if (caseSensitive)
       {
        firstByteFound = (bufArray[x] == (byte) charsToFind[0]);
       }
       else
       {
        firstByteFound = (bufArray[x] == (byte) (charsToFind[0].ToString().ToLower())[0]) ||
         (bufArray[x] == (byte) (charsToFind[0].ToString().ToUpper())[0]);
       }
           
       if (firstByteFound)
       {
        long y;

        /*
         * Finds out whether all next bytes of the string follow the first found byte in the stream
         */
        for (y = 1; y < textToFindLength; y++)
        {
         if ((x + y) >= bufLength)
          break;
         bool nextByteFound;
         if (caseSensitive)
         {
          nextByteFound = (bufArray[x + y] != (byte) charsToFind[ y]);
         }
         else
         {
          nextByteFound = (bufArray [x + y] != (byte) (charsToFind[ y].ToString().ToLower())[0] &&
           bufArray [x + y] != (byte) (charsToFind[ y].ToString().ToUpper())[0]);
         }
         if (nextByteFound)
          break;
        }
        if (y == textToFindLength)
        {
         sourceStream.Flush();
         return (x);
        }
       }
      }
      sourceStream.Flush();
      return (-1);
     }

    Sorry for any formatting problems in the code above. I would be happy to use the OLEObject.Object property, so if anybody can show me a code sample that makes use of OLEObject.Object, please share your experience.

    I also would like to add that I don't like the solution I proposed above - I am not sure what this "Native" clipboard format is, whether it is same on all platforms and in all locales. I guessed about the "Native" format structure by just comparing the Native format data with the original data (original file). So my approach is at least not that serious. It is also very resource consuming - transferring potentially huge data to clipboard and streams (in case the embedded file is very big). But I could not find how to cope with this problem better.

    Glad if my solution helps anybody else,

    Anton 

    Monday, August 28, 2006 11:14 AM
  • Hi Anton

    Have you tried asking in the Excel.Programming newsgroup whether anyone there knows how to do this? Don't mention .NET or anything like that. Just ask if anyone knows how to automate that command to save a generic embedded object.

    I'm guessing this is the Windows Packager. It must have an OLE server interface, or Excel couldn't use it. Question is: can you get to it through the Excel OM. Some of the Excel programming folks are pretty well-versed in VB-lore and the Windows API. One have them may have figured it out - and if they give you the VB-COM info, you may be able to run with that...

    Tuesday, August 29, 2006 8:27 AM
    Moderator
  • Cindy, thank you for your reply.

    Frankly saying my solution is just an attempt to somehow solve the situation with my current level of knowledge (i.e. I would not admit that the initial problem is solved now). So thanks a lot for giving me a piece of advice.

    Just please correct me: When you recommended to visit the Excel.Programming newsgroup did you actually mean http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming&lang=en&cr=US ? If not, please tell me where this newsgroup lives.

    And I admit that you are right - the embedded OLE object can be manually saved in Excel by some "Object Packager" which is a separate window and on which I also could not find much information. It has a pretty valuable capability to save package content to a file - i.e. what I am trying to perform from my C#.NET code.

    thanks and nice day,

    Anton

    Tuesday, August 29, 2006 9:25 PM
  • Hi Anton

    Yes, that's the group I mean :-) Just don't mention C# to them, there: they're "allergic" to .NET.

    Wednesday, August 30, 2006 6:59 AM
    Moderator
  • Thanks, Cindy.

    I will try not to annoy VBA guys with .NET questions. I have not asked my question there yet, but I have an additional comment on the "non-standard" solution I proposed. The solution worked correctly on both Windows XP Pro sp2 and on Windows 2000 sp4. So this strange "Native" clipboard format somehow works well. Of course the solution I proposed is not pure OLE object saving solution, it is rather a way to extract a file from clipboard and save it to disk, provided the file was originally copied to clipboard. So it has nothing to do with this forum at all :-)

    Anton 

    Thursday, August 31, 2006 9:23 AM
  • An update from the Excel.Programming forum:

    The only suggestion I got from the Excel.Programming guys was similar to my own "invention" I described earlier - the approach was to copy OLE object to clipboard and to retrieve it back from clipboard and then to cut the file content:

    http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming&tid=59d62e82-86fb-4f49-9e77-b1740cd1948a&cat=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1&mid=b71faa1b-f121-43c7-b6df-704f56d5221b

    If no more replies follow, I will stop my investigation of the problem.

    Anton

    Friday, September 01, 2006 7:19 PM
  • Hey Anton, I know the forum was like a year ago, but I run into the same issue and need your help with this. The context from the link to the microsoft progamming guys is no longer there and I was wondering if you could provide with the VB code to save OLEObject to a hard drive. Thanks.

    Wednesday, November 07, 2007 8:53 PM
  • Hi Anton,

     

    Your problem is that an Excel.OLEObject is *not* an IOLEObject.  It is a wrapper around an IOLEObject.  The wrapper itself is an Excel object that doesn't implement any interesting interfaces (which is why your casts were failing).  To get to the underlying IOLEObject, you need to call the Object property on your Excel.OLEObject (i.e. myOleObject.Object).  Once you do that, you can cast to the persistence interfaces as required.

     

    You might be interested in reviewing the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2106198&SiteID=1  This particular example is Word, and the embedded object is Excel.  Instead of using the persistence interfaces to save, we just use IDispatch to call Excel's save method.  In any event, the code to get at the underlying OLE object is very similar.  Instead of calling OLEFormat.Object, you'll need to call OLEObject.Object.

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

     

    Wednesday, November 07, 2007 9:12 PM
    Answerer
  • Hey Geoff,

     

    I simply want to save an exe file that I embedded in my program to the hard drive so I can call it out later on in the program (using visual basic code). Do you know how I can do save the OLEObject to the hard drive? Please advise, a sample code would be great. Thanks so much.

     

    Kienn

     

    Thursday, November 08, 2007 3:59 PM
  •  

    i tried ur solution but it didnt work. the resulting files didnt match the original ones. they have extra information at the end like the path....
    Friday, November 09, 2007 2:05 PM
  • Hi,

     

    You need to be a bit more specific about what you tried, what kind of embedded objects you are working with, and what you mean by "it didn't work" before I can really comment. 

     

    That said, an embedded object is not a document file; it is a persisted representation of the object that is controlled entirely by the object and written to whatever storage the container provides.  Depersistence is handled entirely by the object and may well be through an entirely different mechanism than loading an ordinary document file.  In other words, you can't expect to write persisted bits to a file and then turn around and open the file in the appropriate application.  While in some cases this might work, there is nothing in the OLE specification that requires the persistence format to match the file format.  In addition because it represents object state, the persisted bits will likely include information beyond the contents of the file. 

     

    In all likelihood, what you have is a blob that wraps the actual document file around some additional information relating to the object itself.  If that persistence format happens to be documented, you would just need to do some surgery on the output to extract the actual document file bits.  If it isn't documented, see the next paragraph.

     

    In the link I mentioned above, we access the running embedded object via IDispatch and then use Excel's own Save method to force it to write the bits out to a valid document file.  If that isn't an option, the only way to reconstitute the object would be to load the application, QueryInterface for the appropriate IPersistXXX interface and then call the Load method, passing in the persisted blob.

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

     

    Friday, November 09, 2007 4:38 PM
    Answerer
  • Hi Kienn,

     

    There is a sample that saves an Excel file embedded in a Word document which is provided in the link I referenced in one of my previous posts to this thread.  If your embedded object supports IDispatch and exposes a Save mechanism, the easiest solution would be to activate the embedded object and just call that.  Otherwise you will have to use one if the IPersist interfaces to write the object out to the corresponding storage that you provide.  As I mentioned to the last poster, there is no requirement that this persistence format match the document file format.  If it doesn't, the only way to load it back into the application will be to launch the application and then call IPersistXXX.Load--passing the persistence data back in.  I don't have a sample on how to do that at the moment, but maybe if I get some time next week I'll blog about it.

     

    BTW if you are embedding the exe as a package, there is no easy solution for extracting it.  It can be done, but you are in for a tremendous amount of work.  See the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2295536&SiteID=1

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

     

    Friday, November 09, 2007 4:43 PM
    Answerer
  • Thanks Geoff,

     

    I figured out a different way to do what I needed to do using the shell function.

    Monday, November 19, 2007 9:56 PM
  • Hi Kienn,

    Can you tell me how did you do that using the shell function. I am struggling to do it for a very long time.
    I have a number of OLE objects on my excel sheet which I need to to save or get underlying data. The IOleObject:Surprisebject property is not available until  I activate the object which I cannot do because that takes a lot of time in  my case.

    thanks
    Abhimanyu

    Thursday, November 29, 2007 12:45 PM
  • If anyone still wants to know how to extract the original file out of a OLEObject created by the "Packager" application, I can tell you the format. I just analysed it by hand, it's very simple.
    Drop me a line at mailscanner@ecs.soton.ac.uk if you're interested.
    Monday, April 07, 2008 9:03 PM
  • Nice work Anton,

    What type of project did you create a VS? Command line? Excel Add-in?

    When I attempt to compile your code, I get 'InternalWorksheet does not exist in the current context'.

    I'd really like to get this working, and I haven't found any other solution with potential (I've looked very hard).

    Thanks,
    Ken

     

     

    Wednesday, April 06, 2011 6:15 AM
  • I reverse engineered the format. See my post at http://d3dal3.blogspot.fr/2013/05/saving-oleobject-content-to-file.html.
    ded'
    Friday, May 10, 2013 4:18 PM