0

Scenario

In an attempt to read cells from an .ods file that is readible and editable without requiring any password, but which has some data validation cells, I am experiencing slight technical difficulties.

I can manually open the .ods with LibreOffice Version: 6.3.2.2 (x64) without having to enter a password, and save it as an .xlxs file. I can then open that .xlxs file in Excel 2016 using vba.

Question

However I was wondering, how I could either:

  • Automatically read the cells from the "password protected*" .ods file from the .vba method of excel.
  • Or run a script from .vba that creates a copy of the .ods file transforming it to .xlxs.

Related error message:

Run time error '1004': Cannot open '.ods': The file has been protected with a password and cannot be opened.

VBA code:

' Initialize variables
planningWorkbookName = "PlanningData-Form-Temp.ods"
currentPath = ThisWorkbook.path
parentPath = getParentFolder2(currentPath) + "/" + planningWorkbookName
MsgBox (parentPath)
Set planWB = Workbooks.Open(parentPath)
0

This answer is incomplete as it requires one to start a python script, instead of a vba script as the question asks. However, it does execute the vba method that should be executed. So the problem is that the starting point is python instead of vba since I was not yet able to run python from vba in a way that works on most platforms.

  • The first function converts the .ods file to a .xlsx file (both located in the parent folder of the convert.py script).
  • The second function executes the vba code (that then reads the content of the newly created .xlxs file.
# prerequisites: requires python 3.x (or higher)
# open cmd
# browse to this directory
# install pyexcel with:
# python -m pip install pyexcel
# python -m pip install pyexcel-ods (outdated)
# python -m pip install pyexcel-ods3 (outdated)
# python -m pip install pyexcel-xlsx
# python -m pip install pyexcel-xlsxw (ignored)
# run this script with:
# python convertOds.py
import pyexcel
import glob
import os


def convert_to_xlxs():
    os.chdir(".")
    for file in glob.glob("../*.ods"):
        #for sheet in file:
         #   print(sheet)
        #sheet = pyexcel.get_sheet(file_name = file)
        sheet = pyexcel.get_sheet(file_name = file,sheet_name = "Course")
        sheet += pyexcel.get_sheet(file_name = file,sheet_name = "Lectures")
        sheet += pyexcel.get_sheet(file_name = file,sheet_name = "Assignments")
        sheet += pyexcel.get_sheet(file_name = file,sheet_name = "OldExams")
        sheet += pyexcel.get_sheet(file_name = file,sheet_name = "Exercises")
        sheet += pyexcel.get_sheet(file_name = file,sheet_name = "StudyMaterial")
        sheet += pyexcel.get_sheet(file_name = file,sheet_name = "Exam")

        #print(sheet)
        sheet.save_as(file + '.xlsx')

def run_excel_module_from_python():
    import os, os.path
    import win32com.client

    if os.path.exists("GenerateTwCommandsAndLatexTemplates.xlsm"):
        xl=win32com.client.Dispatch("Excel.Application")
        xl.Workbooks.Open(os.path.abspath("GenerateTwCommandsAndLatexTemplates.xlsm"), ReadOnly=1)
        xl.Application.Run("GenerateTwCommandsAndLatexTemplates.xlsm!Module1.main")
        ##    xl.Application.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
        xl.Application.Quit() # Comment this out if your excel script closes
        del xl

convert_to_xlxs()
print("Converted .ods to .xlxs in parentfolder.")
run_excel_module_from_python()
print("Completed evaluation of excel subroutine")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.