How to Copy Single Value From One Excel Sheet to Another

[SunWers]

Hi Everyone,

I'm trying to copy one value from a worksheet to another, but I'm doing something wrong. Can someone help me understand how to do this.

I'm trying to copy a value from workbook(Weekly Data.xlsx) worksheet(DateData)Column H row 4 to workbook(Weekly-temp.xlsx) worksheet (Cutter A - Marker Report) Column C row 4.

Value in Workbook(Weekly Data.xlsx) is in a date format "MM/DD/YY".

1 2 3 4 5 6 7 8 9 10 11 12

# Import 'load_workbok' module from 'openpyxl' from openpyxl import load_workbook   # Load existing excel file into a workbook object wb=load_workbook('Weekly-temp.xlsx') wb1=load_workbook('Weekly Data.xlsx') ws=wb['Cutter A - Marker Report'] ws1=wb1['DateData'] ws.cell(row=4,column=3).value=ws1.cell(row=4,column=6)   # Saving the excel file using "wb.save" method wb.save("C:/Users/vhubb/Documents/Python Scripts/Cutter Reports/2021/01 - January/Week 01.xlsx")

When I run this project I get this error:

1 2 3 4 5 6 7 8 9 10 11 12 13

runfile('C:/Users/vhubb/Documents/Python Scripts/Cutter Reports/CutterReportSetup3.py', wdir='C:/Users/vhubb/Documents/Python Scripts/Cutter Reports') Traceback (most recent call last):     File "C:\Users\vhubb\Documents\Python Scripts\Cutter Reports\CutterReportSetup3.py", line 22, in <module>     ws.cell(row=4,column=3).value=ws1.cell(row=4,column=6)     File "C:\Users\vhubb\anaconda3\lib\site-packages\openpyxl\cell\cell.py", line 216, in value     self._bind_value(value)     File "C:\Users\vhubb\anaconda3\lib\site-packages\openpyxl\cell\cell.py", line 199, in _bind_value     raise ValueError("Cannot convert {0!r} to Excel".format(value))   ValueError: Cannot convert <Cell 'DateData'.F4> to Excel

Thanks,
Veronica

[buran]

1	ws.cell(row=4,column=3).value=ws1.cell(row=4,column=6).value

[SunWers]

1	ws.cell(row=4,column=3).value=ws1.cell(row=4,column=6).value

Hi buran,

Thank you! I think I've been looking at the computer screen way too long and need to take a break, lol. This was a simple fix and I didn't realize I had left off the ".value" till I read your post.

One more question... once I got this error fixed everything works perfect except for the update value in the Excel spreadsheet. What I have is when I update Cutter A sheet with dates and times, I have Cutter B sheet setup to copy the same cells. So when I input this data in the first sheet, it copies over into the next sheet. When I do this manually, it saves time. Now that I'm trying to automate it with phython, I noticed when I run the script above it doesn't automatically update the values in the second sheet. I can do this by manually going to the Excel worksheet and updating the values to point to the correct workbook... but is there a way to do this in Python automatically? I will be writing more to the script to create a lot more workbooks.

Thanks Again for Your Help!

-Veronica

[Pedroski55]

Not too sure exactly what you want.

I don't have to copy any formulas or charts, so I never tried and I don't know about that.

I have to update my attendance + scores timetable each week.

This is the first step, may be that will help. It copies all cells in each sheet, after column 9, to a target file.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

def copyOldData():           print(f'Opening the file attendanceSummer2020_{clas}latestPyMade.xlsx ....')     pathToExcel = f'/home/pedro/attendanceSummer2020/{clas}/'     attendanceFile = f'attendanceSummer2020_{clas}latestPyMade.xlsx'     target = f'attendanceSummer2020_First9colsOnly{clas}.xlsx'     saveFileName = attendanceFile.split('.')     outputFile = saveFileName[0] + '_Step1.xlsx'       sourceFile = openpyxl.load_workbook(pathToExcel + attendanceFile)     sourceSheetNames = sourceFile.sheetnames       targetFile = openpyxl.load_workbook(pathToExcel + target)     targetSheetNames = targetFile.sheetnames       # this inserts the maximum attendance value in the target file       for sheet in sourceSheetNames:         sourceFileActiveSheet = sourceFile[sheet]         targetFileActiveSheet = targetFile[sheet]         targetFileActiveSheet.cell(row=1, column=5, value='max Attendance')         maxAttendance = sourceFileActiveSheet.cell(row=2, column=5).value # get the value         targetFileActiveSheet.cell(row=2, column=5, value=maxAttendance) # write the value                      # copy all data after column 9 to the target file     # move everything up 3 columns           for sheet in sourceSheetNames:         sourceFileActiveSheet = sourceFile[sheet]         targetFileActiveSheet = targetFile[sheet]         maxRowSourceFile = sourceFileActiveSheet.max_row         maxColSourceFile = sourceFileActiveSheet.max_column         for rowNum in range(1, maxRowSourceFile + 1):             for colNum in range(10, maxColSourceFile + 1): # copy everything after column 9                 value = sourceFileActiveSheet.cell(row=rowNum, column=colNum).value                 targetFileActiveSheet.cell(row=rowNum, column=colNum + 3, value=value) # move everything right 3 columns       print('Saving file as ' + outputFile)     targetFile.save(pathToExcel + outputFile)           print('Step 1 done, old data copied')

[SunWers]

Thank You! I will check this out.

Not too sure exactly what you want.

I don't have to copy any formulas or charts, so I never tried and I don't know about that.

I have to update my attendance + scores timetable each week.

This is the first step, may be that will help. It copies all cells in each sheet, after column 9, to a target file.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

def copyOldData():           print(f'Opening the file attendanceSummer2020_{clas}latestPyMade.xlsx ....')     pathToExcel = f'/home/pedro/attendanceSummer2020/{clas}/'     attendanceFile = f'attendanceSummer2020_{clas}latestPyMade.xlsx'     target = f'attendanceSummer2020_First9colsOnly{clas}.xlsx'     saveFileName = attendanceFile.split('.')     outputFile = saveFileName[0] + '_Step1.xlsx'       sourceFile = openpyxl.load_workbook(pathToExcel + attendanceFile)     sourceSheetNames = sourceFile.sheetnames       targetFile = openpyxl.load_workbook(pathToExcel + target)     targetSheetNames = targetFile.sheetnames       # this inserts the maximum attendance value in the target file       for sheet in sourceSheetNames:         sourceFileActiveSheet = sourceFile[sheet]         targetFileActiveSheet = targetFile[sheet]         targetFileActiveSheet.cell(row=1, column=5, value='max Attendance')         maxAttendance = sourceFileActiveSheet.cell(row=2, column=5).value # get the value         targetFileActiveSheet.cell(row=2, column=5, value=maxAttendance) # write the value                      # copy all data after column 9 to the target file     # move everything up 3 columns           for sheet in sourceSheetNames:         sourceFileActiveSheet = sourceFile[sheet]         targetFileActiveSheet = targetFile[sheet]         maxRowSourceFile = sourceFileActiveSheet.max_row         maxColSourceFile = sourceFileActiveSheet.max_column         for rowNum in range(1, maxRowSourceFile + 1):             for colNum in range(10, maxColSourceFile + 1): # copy everything after column 9                 value = sourceFileActiveSheet.cell(row=rowNum, column=colNum).value                 targetFileActiveSheet.cell(row=rowNum, column=colNum + 3, value=value) # move everything right 3 columns       print('Saving file as ' + outputFile)     targetFile.save(pathToExcel + outputFile)           print('Step 1 done, old data copied')