Python Forum
How to Copy Single Value From One Excel Sheet to Another
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to Copy Single Value From One Excel Sheet to Another
#1
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
Reply
#2
ws.cell(row=4,column=3).value=ws1.cell(row=4,column=6).value
SunWers and nman52 like this post
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
(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
Reply
#4
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 likes this post
Reply
#5
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')
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy Paste excel files based on the first letters of the file name Viento 2 349 Feb-07-2024, 12:24 PM
Last Post: Viento
  How to copy work sheet data one workbook to other? sayyedkamran 2 646 Nov-03-2023, 09:10 AM
Last Post: Larz60+
  Python and pandas: Aggregate lines form Excel sheet Glyxbringer 12 1,697 Oct-31-2023, 10:21 AM
Last Post: Pedroski55
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,926 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
  how to read txt file, and write into excel with multiply sheet jacklee26 14 9,525 Jan-21-2023, 06:57 AM
Last Post: jacklee26
  is it possible to copy image from email and place into excel file? cubangt 3 1,212 Nov-30-2022, 05:11 PM
Last Post: snippsat
  Reading Excel file and use a wildcard in file name and sheet name randolphoralph 6 6,864 Jan-13-2022, 10:20 PM
Last Post: randolphoralph
  Python script for excel sheet Nabil 4 3,225 Jun-01-2021, 05:09 AM
Last Post: Pedroski55
  Copy column from one existing excel file to another file mkujawsk 0 5,489 Apr-14-2021, 06:33 PM
Last Post: mkujawsk
  Add a new column when I extract each sheet in an Excel workbook as a new csv file shantanu97 0 2,192 Mar-24-2021, 04:56 AM
Last Post: shantanu97

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020