Python Forum

Hello all,

I am working to automate the creation of network diagrams using python/openpyxl. The script I have is working for the most part, but one of the key components that I am trying to get working is o have the new file save using the value of specific cells. This is because the template I will be working off of is the same, but the data that needs to be entered into the template is different each time. Below is the script, and below that is the error that I receive

from openpyxl import Workbook, load_workbook
# Loading Source file
wb1 = load_workbook ('C:\\Users\\fbianchi\\Documents\\Source Information.xlsx')
ws1 = wb1.active
# Loading Destination file
wb2 = load_workbook ('C:\\Users\\fbianchi\\Documents\\Network Design Excel Template.xlsx')
ws2 = wb2.active

mr = ws1.max_row
mc = ws1.max_column

# Define Range in source file
for i in range (1,5):
    for j in range (1,5):
        # Select Cells in source file
        TID = ws1.cell(row = 2, column = 1)
        VLAN_A = ws1.cell(row = 2, column = 2)
        VLAN_B = ws1.cell(row = 2, column = 3)
        CID = ws1.cell(row = 2, column = 4)
        # Paste Cells from source file to specified cells in destination file
        ws2.cell(row = 15, column = 4).value = TID.value
        ws2.cell(row = 17, column = 4).value = VLAN_A.value
        ws2.cell(row = 5, column = 13).value = VLAN_A.value
        ws2.cell(row = 17, column = 6).value = VLAN_B.value
        ws2.cell(row = 30, column = 13).value = VLAN_B.value
        ws2.cell(row = 14, column = 4).value = CID.value



wb2.save('C:\\Users\\fbianchi\\Documents\\(f"{ws2} {TID.}.xlsx")')

#ws1.delete_rows(2)

*END SCRIPT TRACEBACK BELOW*

Error:Traceback (most recent call last):
  File "C:\Users\fbianchi\Documents\Network Design Script.py", line 30, in <module>
    wb2.save('C:\\Users\\fbianchi\\Documents\\(f"{ws2} {TID.}.xlsx")')
  File "C:\Users\fbianchi\AppData\Roaming\Python\Python310\site-packages\openpyxl\workbook\workbook.py", line 407, in save
    save_workbook(self, filename)
  File "C:\Users\fbianchi\AppData\Roaming\Python\Python310\site-packages\openpyxl\writer\excel.py", line 291, in save_workbook
    archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
  File "C:\Program Files\Python310\lib\zipfile.py", line 1249, in __init__
    self.fp = io.open(file, filemode)
OSError: [Errno 22] Invalid argument: 'C:\\Users\\fbianchi\\Documents\\(f"{ws2} {TID.}.xlsx")'
[Finished in 809ms]

I have tried various combinations of syntax to get this to save properly, however I continue to get an invalid argument error. I believe this is an issue with the syntax that I am using, but I am unsure.

(I am newer to python, so I apologize if my terminology is off)

wb2.save(f'C:\\Users\\fbianchi\\Documents\\{ws2} {TID}.xlsx')

Thank you, I will give this a try.

Hello, Unless I missed something, this errored out again

wb2.save(f'C:\\Users\\fbianchi\\Documents\\{ws2} {TID}.xlsx')

Error:Traceback (most recent call last):
  File "C:\Users\fbianchi\Documents\Network Design Script.py", line 29, in <module>
    wb2.save(f'C:\\Users\\fbianchi\\Documents\\{ws2}{TID}.xlsx')
  File "C:\Users\fbianchi\AppData\Roaming\Python\Python310\site-packages\openpyxl\workbook\workbook.py", line 407, in save
    save_workbook(self, filename)
  File "C:\Users\fbianchi\AppData\Roaming\Python\Python310\site-packages\openpyxl\writer\excel.py", line 291, in save_workbook
    archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
  File "C:\Program Files\Python310\lib\zipfile.py", line 1249, in __init__
    self.fp = io.open(file, filemode)
OSError: [Errno 22] Invalid argument: 'C:\\Users\\fbianchi\\Documents\\<Worksheet "Sheet1"><Cell \'Sheet1\'.A2>.xlsx'
[Finished in 607ms]

I did also try playing around with the locations of the quotations to see if that has any effect on the output, as well as adding .value after {TID}. Neither of these proved fruitful.

You need the name of the active worksheet. Looks like you can get that using "ws2.title" instead of "ws2". TID is a cell. You probably want to use the value of the cell. use "TID.value" instead of "TID"

wb2.save(f'C:\\Users\\fbianchi\\Documents\\{ws2.title} {TID.value}.xlsx')

Hello

Thank you for the information, it helped me figure out what needed to happen. Ultimately this is what I found that worked

wb2.save(f'C:\\Users\\fbianchi\\Documents\\ {ws2.cell(row = 14, column = 4).value} {ws2.cell(row = 15, column = 4).value}  {ws2.cell(row = 17, column = 4).value} {ws2.cell(row = 17, column = 6).value}.xlsx')

fjbianchi93

deanhystad

fjbianchi93

deanhystad

fjbianchi93