1

I have an Excel source file in a source folder (*.xlsm) and another file (also *.xlsm) that contain some data. I have to create a third file, that has to be a *.xls file, that is basically the Excel source file that contains some data of the second file. In order to do that I have written this code:

from openpyxl import load_workbook

file1 = "C:\\Users\Desktop\file1.xlsm"
file2 = "C:\\Users\Desktop\file2.xlsm"
file3 = "C:\\Users\Desktop\file3.xls"

wb1 = load_workbook(file1)
sheet1 = wb1["Sheet1"]

wb2 = load_workbook(file2)
sheet2 = wb2["Sheet1"]

sheet1["A1"].value = sheet2["A1"].value

wb1.save(file3)

The code seems to be OK and doesn't return any error, but the I cannot open the created file3.

image

I don't understand why, I tried to change the extension of the third file but both *.xlsx and *.xlsm show this problem. I also tried to delete the line part

sheet1["A1"].value = sheet2["A1"].value

To understand if the problem was linked to the writing of the sheet, but the problem remains.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Newbiecoder
  • 31
  • 4
  • You open file1 and file2 - where do you open file3 - unless you open that or do you want to save the activesheet to file3, if so you need saveas... – Solar Mike Mar 05 '19 at 08:16
  • I want to save the file1 as a new file 3. So is the line "wb2.save(file3)" not correct? – Newbiecoder Mar 05 '19 at 08:21
  • From what I read, you are just saving file3 but it has not got a file3... you could save file1 or do a saveas file3... – Solar Mike Mar 05 '19 at 08:23
  • ok, so I have to save file1 as file3. Could you show me the exact syntax of the code or indicate me where I can find it? "wb1.saveas(file3)" does not work. – Newbiecoder Mar 05 '19 at 08:31
  • I don't use openpyxl, but I was just querying your logic in the hope it would help you to follow what you are trying to do. I based my comments on how I would approach it (and have ) in vba when i deal with creating 200 files for feedback. – Solar Mike Mar 05 '19 at 08:47
  • openpyxl cannot be used to create `.XLS` files only `.XLSX` – Charlie Clark Mar 05 '19 at 09:52
  • Possible duplicate of [How to save XLSM file with Macro, using openpyxl](https://stackoverflow.com/questions/17675780/how-to-save-xlsm-file-with-macro-using-openpyxl) – roschach Mar 05 '19 at 13:24

2 Answers2

0

First of all please not that your code is not creating any new file but just resaving an existing one.

Also is not clear what you want: do you want to create file3? With what information? Your code is not doing anything of that.

However I tried to run a short version of your code and I got the error:

openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support .xlsm' file format, please check you can open it with Excel first. Supported formats are: .xlsx,.xlsm,.xltx,.xltm

Most likely your file format is unsupported. Try to resave your files in the format xlsx. I think the problem are macros: if you don't have any of them in your files then changing the format should not be any issue. If you have I am not sure openpyxl will work in that way (without any workaround at least).

This answer might help. It propose to extract the xlms files (they are zip files), work on the ones that represent the format of your sheet (not the macro) and then put everything together again.

roschach
  • 8,390
  • 14
  • 74
  • 124
0

One error might be that the filepath variables require unicode escape's for the \

Thus: the correct version would be

file1 = "C:\\Users\\Desktop\\file1.xlsm" file2 = "C:\\Users\\Desktop\\file2.xlsm" file3 = "C:\\Users\\Desktop\\file3.xls"

PDXCoder
  • 1
  • 3