Python: Access embedded OLE from Office/Excel docu

Question

I want to add and extract files from an Office/Excel document using Python. So far adding things is easy but for extracting I haven't found a clean solution.

To make clear what I've got and what not I've written the small example test.py below and explain further.

test.py

import win32com.client as win32
import os 
from tkinter import messagebox
import win32clipboard

# (0) Setup
dir_path = os.path.dirname(os.path.realpath(__file__))
print(dir_path)
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(dir_path + "\\" + "test_excel.xlsx")
ws = wb.Worksheets.Item(1)
objs = ws.OLEObjects()

# (1) Embed file
f = dir_path + "\\" + "test_txt.txt"
name = "test_txt_ole.txt"
objs.Add( Filename=f, IconLabel=name )

# (2) Access embedded file
obj = objs.Item(1) # Get single OLE from OLE list
obj.Copy()
win32clipboard.OpenClipboard()
data = win32clipboard.GetClipboardData(0xC004) # Binary access
win32clipboard.EmptyClipboard()
win32clipboard.CloseClipboard()
messagebox.showinfo(title="test_txt_ole.txt", message=str(data))

# (3) Press don't save here to keep 
# wb.Close() # Will close excel document and leave excel opened.
excel.Application.Quit() # Will close excel with all opened documents

For preparation (step 0) it opens a given excel document with one worksheet that was create before by using new document button in excel.

In step (1) it uses API to embed a given text file to the excel document. The text file was created before with content "TEST123" using a text editor.

Afterwards in step (2) it tries to read back content from embedded OLE using clipboard and opens a message box that shows the content from OLE in clipboard.

Finally (3) the program closes the opened document. To keep an unchanged setup press no here.

The big disadvantage of this solution is the use of clipboard which smashes any user content in clipboard which is bad style in a productive environment. Further it uses an undocumented option for clipboard.

A better solution would be to safe OLE or OLE embedded file to a python data container or to a file of my choice. In my example I've used a TXT file to easily identify file data. Finally I'll use ZIP for an all-in-one solution but a TXT file solution would be sufficient for base64 data.

Source of 0xC004 = 49156: https://danny.fyi/embedding-and-accessing-a-file-in-excel-with-vba-and-ole-objects-4d4e7863cfff

This VBA example look interesting but I have no clue about VBA: Saving embedded OLE Object (Excel workbook) to file in Excel 2010

Answer 1

Well, I find Parfait's solution a bit hackish (in the bad sense) because

• it assumes that Excel will save the embedding as a temporary file,
• it assumes that the path of this temporary file is always the user's default temp path,
• it assumes that you will have privileges to open files there,
• it assumes that you use a naming convention to identify your objects (e.g. 'test_txt' is always found in the name, you can't insert an object 'account_data'),
• it assumes that this convention is not disturbed by the operating system (e.g. it will not change it to '~test_tx(1)' to save character length),
• it assumes that this convention is known and accepted by all other programs on the computer (no one else will uses names that contain 'test_txt').

So, I wrote an alternative solution. The essence of this is thef following:

1. unzip the .xlsx file (or any other Office file in the new XML-based format, which is not password protected) to a temporary path.

2. iterate through all .bin files inside the '/xxx/embeddings' ('xxx' = 'xl' or 'word' or 'ppt'), and create a dictionary that contains the .bin files' temporary paths as keys and the dictionaries returned from step 3 as values.

3. extract information from the .bin file according to the (not very well documented) Ole Packager format, and return the information as a dictionary. (Retrieves the raw binary data as 'contents', not only from .txt but any file type, e.g. .png)

I'm still learning Python, so this is not perfect (no error checking, no performance optimization) but you can get the idea from it. I tested it on a few examples. Here is my code:

import tempfile
import os
import shutil
import zipfile
import glob
import pythoncom
import win32com.storagecon


def read_zipped_xml_bin_embeddings( path_zipped_xml ):
    temp_dir = tempfile.mkdtemp()

    zip_file = zipfile.ZipFile( path_zipped_xml )
    zip_file.extractall( temp_dir )
    zip_file.close()

    subdir = {
            '.xlsx': 'xl',
            '.xlsm': 'xl',
            '.xltx': 'xl',
            '.xltm': 'xl',
            '.docx': 'word',
            '.dotx': 'word',
            '.docm': 'word',
            '.dotm': 'word',
            '.pptx': 'ppt',
            '.pptm': 'ppt',
            '.potx': 'ppt',
            '.potm': 'ppt',
        }[ os.path.splitext( path_zipped_xml )[ 1 ] ]
    embeddings_dir = temp_dir + '\\' + subdir + '\\embeddings\\*.bin'

    result = {}
    for bin_file in list( glob.glob( embeddings_dir ) ):
        result[ bin_file ] = bin_embedding_to_dictionary( bin_file )

    shutil.rmtree( temp_dir )

    return result


def bin_embedding_to_dictionary( bin_file ):
    storage = pythoncom.StgOpenStorage( bin_file, None, win32com.storagecon.STGM_READ | win32com.storagecon.STGM_SHARE_EXCLUSIVE )
    for stastg in storage.EnumElements():
        if stastg[ 0 ] == '\1Ole10Native':
            stream = storage.OpenStream( stastg[ 0 ], None, win32com.storagecon.STGM_READ | win32com.storagecon.STGM_SHARE_EXCLUSIVE )

            result = {}
            result[ 'original_filename' ] = '' # original filename in ANSI starts at byte 7 and is null terminated
            stream.Seek( 6, 0 )
            while True:
                ch = stream.Read( 1 )
                if ch == '\0':
                    break
                result[ 'original_filename' ] += ch

            result[ 'original_filepath' ] = '' # original filepath in ANSI is next and is null terminated
            while True:
                ch = stream.Read( 1 )
                if ch == '\0':
                    break
                result[ 'original_filepath' ] += ch

            stream.Seek( 4, 1 ) # next 4 bytes is unused

            temporary_filepath_size = 0 # size of the temporary file path in ANSI in little endian
            temporary_filepath_size |= ord( stream.Read( 1 ) ) << 0
            temporary_filepath_size |= ord( stream.Read( 1 ) ) << 8
            temporary_filepath_size |= ord( stream.Read( 1 ) ) << 16
            temporary_filepath_size |= ord( stream.Read( 1 ) ) << 24

            result[ 'temporary_filepath' ] = stream.Read( temporary_filepath_size ) # temporary file path in ANSI

            result[ 'size' ] = 0 # size of the contents in little endian
            result[ 'size' ] |= ord( stream.Read( 1 ) ) << 0
            result[ 'size' ] |= ord( stream.Read( 1 ) ) << 8
            result[ 'size' ] |= ord( stream.Read( 1 ) ) << 16
            result[ 'size' ] |= ord( stream.Read( 1 ) ) << 24

            result[ 'contents' ] = stream.Read( result[ 'size' ] ) # contents

            return result

You can use it like this:

objects = read_zipped_xml_bin_embeddings( dir_path + '\\test_excel.xlsx' )
obj = objects.values()[ 0 ] # Get first element, or iterate somehow, the keys are the temporary paths
print( 'Original filename: ' + obj[ 'original_filename' ] )
print( 'Original filepath: ' + obj[ 'original_filepath' ] )
print( 'Original filepath: ' + obj[ 'temporary_filepath' ] )
print( 'Contents: ' + obj[ 'contents' ] )

Answer 2

Consider using the Windows temp directory that will temporarily store the OLE Object's file source when embedded in workbook. No clipboard is used in this solution but physical files.

With this approach, you will need to retrieve the current user's name and iterate through all files of the temp directory: C:\Documents and Settings\{username}\Local Settings\Temp (standard Excel dump folder for Windows Vista/7/8/10). Also, a conditional like-name search with in is used that contains original file's basename as multiple versions with number suffixes (1), (2), (3),... may exist depending on how many times script runs. Try even a regex search here.

Finally, the below routine uses try...except...finally block to cleanly exist the Excel objects regardless of error but will output any exception message. Do note this is only a Windows solution using a text file.

import win32com.client as win32
import os, shutil
from tkinter import messagebox

# (0) Setup
dir_path = cd = os.path.dirname(os.path.abspath(__file__))
print(dir_path)

try:
    excel = win32.gencache.EnsureDispatch('Excel.Application')    
    wb = excel.Workbooks.Open(os.path.join(dir_path, "test_excel.xlsx"))
    ws = wb.Worksheets(1)
    objs = ws.OLEObjects()

    # (1) Embed file
    f = os.path.join(dir_path, "test_txt.txt")    
    name = "test_txt_ole.txt"
    objs.Add(Filename=f, IconLabel=name).Name = 'Test'

    # (2) Open file from temporary folder
    ole = ws.OLEObjects(1)        
    ole.Activate()

    # (3) Grab the recent like-named file
    user = os.environ.get('USERNAME')
    outfile = os.path.join(dir_path, "test_txt_out.txt")

    tempfolder = r"C:\Documents and Settings\{}\Local Settings\Temp".format(user)

    for subdir, dirs, files in os.walk(tempfolder):
        for file in sorted(files, reverse=True):
            if 'test_txt' in file:                
                tempfile = os.path.join(tempfolder, file)
                break

    shutil.copyfile(tempfile, outfile)

    # (4) Read text content
    with open(outfile, 'r') as f:        
        content = f.readlines()

    # (5) Output message with content
    messagebox.showinfo(title="test_txt_ole.txt", message="".join(content))

except Exception as e:
    print(e)

finally:
    wb.Close(True)      # CLOSES AND SAVES WORKBOOK
    excel.Quit          # QUITS EXCEL APP

    # RELEASES COM RESOURCES
    ws = None; wb = None; objs = None; ole = None; excel = None

Tkinter Messagebox