0

The issue I am having is I have a .xlsm workbook with two worksheets. One of the worksheets using a VLOOKUP macro function in a cell, that looks up a value in the second sheet in the workbook. I just need the date value that it defines from the VLOOKUP.

What I have tried:

-I used Openpyxl to open the existing workbook, using data_only=True, vba_values=True and the value keeps giving me '#N/A'

-I have tried using win32com to open the workbook, refresh the workbook and grab
the cell value, but I get this giant negative int -217589383

I am not sure if this is possible in openpyxl or if I am not using the library correctly. The macro in the cell looks like this '=VLOOKUP(A34,SSU!$1:$65536,2,FALSE)', the second sheet is called SSU.

I don't care which Python library I use in order to get the value that this macro calls, so long as I can get it. When I have a file that is .xls I am able to get that value easily using xlrd, but unfortunately, xlrd doesn't work with files that aren't .xls. Below is my code sample.

    elif check_file_type(site_list_name, ['.xlsx', '.xlsm', '.xltx', '.xltm']) and get_file_name(
        site_list_name) != prefix_suffix_file_name:

    workbook = load_workbook(site_list_name_path, keep_vba=True, data_only=True)
    worksheet = workbook.active
    print(worksheet['B4'].value)

the print value is #N/A

Any suggestions would be greatly appreciated!!

hcam93
  • 11
  • 2
  • Does this answer your question? [Read Excel cell value and not the formula computing it -openpyxl](https://stackoverflow.com/questions/28517508/read-excel-cell-value-and-not-the-formula-computing-it-openpyxl) – itprorh66 Jun 13 '21 at 20:49
  • Hey thanks for reaching out! The solution I had to do was convert the workbook file to an XLS using win32com, then copy each cell to a new file using xlrd then put it in a pandas dataframe then write it to an xlsx. I don't know why the dates don't convert with openpyxl but maybe it's a bug. Hopefully, I can get that figured out in the future. This bandaid works for now – hcam93 Jun 14 '21 at 00:13

0 Answers0