Python Forum

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 Excel

Thanks,
Veronica

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

(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

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')

Thank You! I will check this out.

(Dec-29-2020, 09:28 AM)Pedroski55 Wrote: [ -> ]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')

SunWers

buran

SunWers

Pedroski55

SunWers