How to Copy Single Value From One Excel Sheet to Another - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: How to Copy Single Value From One Excel Sheet to Another (/thread-31694.html) |
How to Copy Single Value From One Excel Sheet to Another - SunWers - Dec-28-2020 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". # 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: 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 ExcelThanks, Veronica RE: How to Copy Single Value From One Excel Sheet to Another - buran - Dec-28-2020 ws.cell(row=4,column=3).value=ws1.cell(row=4,column=6).value RE: How to Copy Single Value From One Excel Sheet to Another - SunWers - Dec-28-2020 (Dec-28-2020, 06:42 AM)buran Wrote: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 RE: How to Copy Single Value From One Excel Sheet to Another - Pedroski55 - Dec-29-2020 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. 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') RE: How to Copy Single Value From One Excel Sheet to Another - SunWers - Dec-29-2020 Thank You! I will check this out. (Dec-29-2020, 09:28 AM)Pedroski55 Wrote: Not too sure exactly what you want. |