Python Forum

Hello,

Let me start off by saying I'm brand new at using Python. This script is far from what I want it to do, but I'm just getting a feel for it. This is what I would like this code to do:

* Copy all the source data which it does by using the max row and column code below, but I want it to paste the data to the destination file (template)range B13:O (O because the data always varies in length so theres no end number). Right now it just pastes the data to A1 of the template file which overwrites important formulas that I have in the first 12 rows.

* I also want the destination file to save as something else besides "template" because I will be using this template multiple times.

import openpyxl as xl; 
  
# opening the source excel file 
filename ="C:/data.xlsx"
wb1 = xl.load_workbook(filename) 
ws1 = wb1.worksheets[1] 
  
# opening the destination excel file  
filename1 ="C:/Template.xlsx"
wb2 = xl.load_workbook(filename1) 
ws2 = wb2.worksheets[2] 
  
# calculate total number of rows and  
# columns in source excel file 
mr = ws1.max_row 
mc = ws1.max_column 
  
# copying the cell values from source  
# excel file to destination excel file 
for i in range (1, mr + 1): 
    for j in range (1, mc + 1): 
        # reading cell value from source excel file 
        c = ws1.cell(row = i, column = j) 

        ws2.cell(row = i, column = j).value = c.value 
  
# saving the destination excel file 
wb2.save(str(filename1))

You could put a shift amount into this line

ws2.cell(row = i+12, column = j+1).value = c.value

to save as something else just change the save name

wb2.save('C:/newfile.xlsx')

(Jun-15-2020, 08:14 PM)Yoriz Wrote: [ -> ]You could put a shift amount into this line

ws2.cell(row = i+12, column = j+1).value = c.value

to save as something else just change the save name

wb2.save('C:/newfile.xlsx')

This worked great! Thank you.

Now, what if I have 100 source files data, data1, data2, data3... etc, and I want to loop it through all 100 files, and use the destination template to paste each data file into, and save as data, data1, data2, data3 etc.

you would create a loop that alters the number of the save file

for index in range(1, 11):
    #rest of code
    wb2.save(f'C:/data{index}.xlsx')