1

Right now I am doing the following.

import xlrd

resp = requests.get(url, auth=auth).content
output = open(r'temp.xlsx', 'wb')
output.write(resp)
output.close()
xl = xlrd.open_workbook(r'temp.xlsx')
sh = 1
try:
    for sheet in xl.sheets():
    xls.append(sheet.name)
except:
    xls = ['']

It's extracting the sheets but I don't know how to read the file or if saving the file as an .xlsx is actually working for macros. All I know is that the code is not working right now and I need to be able to catch the data that is being generated in a macro. Please help! Thanks.

Ravaal
  • 3,233
  • 6
  • 39
  • 66
  • I'm confused as to what you are trying to do. Also, are you saying you have macros inside of an .xlsx file? – Matt M May 20 '19 at 19:23
  • It's a macro enabled .xlsm file that I was saving as an xlsx but that takes away the ability to get the data from a macro. I'm trying to download the file. Save it in a temp file where the contents are stored, and then process the data and put it into a DataFrame sheet by sheet. – Ravaal May 20 '19 at 19:43
  • Once you have downloaded the file see this https://stackoverflow.com/a/61267822/6117565 – bikram Apr 17 '20 at 09:06

1 Answers1

2

I highly recommend using xlwings if you want to open, modify, and save .xlsm files without corrupting them. I have tried a ton of different methods (using other modules like openpyxl) and the macros always end up being corrupted.

import xlwings as xw
app = xw.App(visible=False) # IF YOU WANT EXCEL TO RUN IN BACKGROUND

xlwb = xw.Book('PATH\\TO\\FILE.xlsm')
xlws = {}
xlws['ws1'] = xlwb.sheets['Your Worksheet']

print(xlws['ws1'].range('B1').value)   # get value
xlws['ws1'].range('B1').value =  'New Value'   # change value

yourMacro = xlwb.macro('YourExcelMacro')
yourMacro()

xlwb.save()
xlwb.close()

Edit - I added an option to keep Excel invisible at users request

Matt M
  • 691
  • 2
  • 6
  • 17
  • I need to download the file first through python. How do I do that too? – Ravaal May 21 '19 at 14:25
  • What are you downloading the file from? An FTP server? An external hard drive? Etc? – Matt M May 21 '19 at 14:27
  • Check out [this answer](https://stackoverflow.com/questions/53671547/python-download-files-from-sharepoint-site) and [this one](https://stackoverflow.com/questions/44779512/download-sharepoint-excel-file-in-python) too. Should be pretty strait forward – Matt M May 21 '19 at 15:21
  • That's not what I'm looking for. I want Python to do all the work. I don't want to file to open itself. Python needs to open the file, use the macro, and extract the data. I will not know the sheet names or the data in there until Python extracts all of it. – Ravaal May 22 '19 at 22:36
  • You need to access Excel in some way to run a Macro. you can either create a VBS script and run a subprocess or just turn the visibility off in `xlwings` (same thing really). I edited my answer above to include this. Then run the macro in `xlwings` and extract the data like shown above – Matt M May 23 '19 at 12:53