0

Previously, I was getting a win32 error 'open method of excel workbooks failed'. But now, I'm getting an error in the excel macro I'm trying to run via python, 'subscript out of range'.

Screenshot of error

When I debug it, it outlines this line of code:

Sheets("2 RawData").Select

I know that the error is supposed to mean that there isn't a sheet with that name, but when I checked, it was there:

All sheets in excel file

And this is a screenshot of the errors from python:

Python errors

This is the code I'm trying to run to parse an excel document via macro:

    if os.path.exists(self.excel_parser_location):
        # print "Opening Telematics_Messages_Parser.xlsm in Excel"
        xl = client.Dispatch("Excel.Application")
        xl.Application.visible = False
        wb = xl.Workbooks.Open(os.path.abspath(self.excel_parser_location), ReadOnly=True)
        xl.Application.Run('DoThisFirst')
        xl.DisplayAlerts = False
        wb.DoNotPromptForConvert = True
        wb.CheckCompatibility = False
        # xl.Application.Run('SheetKiller')
        xl.Application.Run('CleanUp')
        xl.DisplayAlerts = False
        wb.DoNotPromptForConvert = True
        wb.CheckCompatibility = False
        # If the file already exists
        if os.path.exists(save_path):
            # Remove the older save
            os.remove(save_path)
        wb.SaveAs(save_path)
        # print "Saving Parsed_Messages.xlsm"
        wb.Close(True)
        del xl

Edit: I called this method before the method to run the macros:

if os.path.exists(csv_path):
    #data = pd.read_csv(csv_path, error_bad_lines=False)
    data = pd.read_csv(csv_path, on_bad_lines='skip')
    book = openpyxl.load_workbook(self.excel_parser_location,keep_vba=True)
    writer = pd.ExcelWriter(self.excel_parser_location)
    writer.book = book
    data.to_excel(writer, sheet_name='2 RawData', index=False)
    # print 'Writing new data'
    book.remove(book['2 RawData'])
    # print 'Removing blank sheet'
    book_sheet = book['2 RawData1']
    book_sheet.title = '2 RawData'
    # print 'Renaming sheet'
    writer.save()
    writer.close()
    # print 'Saving Telematics_Messages_Parser.xlsm'
    self.run_parsing_macro(sn)
    os.remove(csv_path)
    return True

It's supposed to copy data to the excel sheet from a downloaded csv file.

CakeIsALie
  • 21
  • 3
  • 3
    Check for trailing spaces. Also you really should qualify the `Workbook` before `Sheets`. And [avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Feb 15 '22 at 17:52
  • I'm using an excel file with macros somebody else wrote. I'm not really supposed to change those macros. I'm hoping there's something I can do with the python code to fix this error. – CakeIsALie Feb 15 '22 at 19:53
  • 1
    ... why would you try to fix the Python, when the error is in the VBA? That doesn't really make sense. Also, the VBA is badly written, so it *should* be changed. – BigBen Feb 15 '22 at 19:54
  • 2
    Either the book containing that sheet is not active when that code runs, or the sheet name is not what you think (leading or trailing space?) You _can_ work around the former with python, but I agree with Ben: crap code is crap code. Call it out and get it fixed – chris neilsen Feb 15 '22 at 20:00
  • I think it is a problem with the python code because when I open the excel parser file manually, copy the data from the downloaded csv, and run the macro, everything seems to be working. I just can't get it to work via python. – CakeIsALie Feb 16 '22 at 15:31
  • Also, if I run older versions of this excel parser, I get an error occurring on the line before the macro saying that the 'open method of the workbook failed'. it's weird because this newer version can make it to the next line and fails when the macro is run. One of the differences between the old parsers and the new one is that there is an extra sheet. – CakeIsALie Feb 16 '22 at 15:37

0 Answers0