Password protected xls data transfer to master - 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: Password protected xls data transfer to master (/thread-32518.html) |
Password protected xls data transfer to master - OTH - Feb-15-2021 Hi, I am quite new to python and currently writing a code to speed up a VBA process which takes 5 to 6 hours to complete and want to speed it up. The code needs to open a password protected excel, extract certain sheet and cell data to a master sheet and if column A is that same number then override so no duplicates: Process: Step 1: Open password protected xls step 2: check for the duplicated number in column A and if the same value exists then override, copy required cells from each sheet to master wb and data sheet as shown below step 3: go back to step one until all xls are done. This is part of the VBA to show the process to a degree: wbThis.Worksheets("Data").Range("A" & Store_Row_no) = NewNumber wbThis.Worksheets("Data").Range("B" & Store_Row_no) = DateNew wbThis.Worksheets("Data").Range("C" & Store_Row_no) = wbNew.Worksheets("Sheet1").Range("F2").Value wbThis.Worksheets("Data").Range("D" & Store_Row_no) = wbNew.Worksheets("Sheet2").Range("H152").Value wbThis.Worksheets("Data").Range("E" & Store_Row_no) = wbNew.Worksheets("Sheet3").Range("D3").Value and this is my current code but can't work out how I open a password protected excel and copy to master sheet and then overide for data column A if it is a duplicate. Python code so far: import xlrd wb = xlrd.open_workbook('C:/Users/') sh1 = wb.sheet_by_name('Sheet1') #sheet name1 sh2 = wb.sheet_by_name('Sheet3') #sheet name2 sh3 = wb.sheet_by_name('Sheet4') #sheet name3 out1 = sh1.cell(1,1).value # sh.cell(row,column).value out2 = sh1.cell(1,3).value out3 = sh1.cell(1,5).value out4 = sh2.cell(151,7).value out5 = sh3.cell(2,2).value print(out1,out2,out3,out4,out5)Any help would be greatly appreciated, I know this is probably quite simple and I am trying my best to learn via google, youtube and online tutorials but nothing I have seen explains what I want. UPDATE: updated the code so can open encrypted excels: import win32com.client import sys password = ' ' xlApp = win32com.client.Dispatch("Excel.Application") filename = ('C.xls') wb = xlApp.Workbooks.Open(filename, False, True, None, password) sh1 = wb.Sheets('sheet1') #sheet name1 sh2 = wb.Sheets('sheet2) #sheet name2 sh3 = wb.Sheets('sheet3') #sheet name2 out1 = sh1.Range("B2").value out2 = sh1.Range("D2").value out3 = sh1.Range("F2").value out4 = sh2.Range("H152").value out5 = sh3.Range("D3").value print(out1,out2,out3,out4,out5)Just need to loop through help and copy to new master wb Thank you so much in advance Looping through a folder and copying data from certain cells to a master sheet - OTH - Feb-15-2021 Hi, I am quite new to python and currently writing a code to speed up a VBA process which takes 5 to 6 hours to complete and want to speed it up. The code needs to open a password protected excel, extract certain sheet and cell data to a master sheet and if column A is that same number then override so no duplicates: Process: Step 1: Open password protected xls step 2: check for the duplicated number in column A and if the same value exists then override, copy required cells from each sheet to master wb and data sheet as shown below step 3: go back to step one until all xls are done. This is part of the VBA to show the process to a degree: wbThis.Worksheets("Data").Range("A" & Store_Row_no) = NewNumber wbThis.Worksheets("Data").Range("B" & Store_Row_no) = DateNew wbThis.Worksheets("Data").Range("C" & Store_Row_no) = wbNew.Worksheets("Sheet1").Range("F2").Value wbThis.Worksheets("Data").Range("D" & Store_Row_no) =wbNew.Worksheets("Sheet2").Range("H152").Value wbThis.Worksheets("Data").Range("E" & Store_Row_no) = wbNew.Worksheets("Sheet3").Range("D3").Value and this is my current code but cant work out how I open a password protected excel and copy to master sheet and then overide for data column A if it is a duplicate. Python code so far: Any help would be greatly appreciated, I know this is probably quite simple and I am trying my best to learn via google, youtube and online tutorials but nothing I have seen explains what I want. UPDATE: updated the code so can open encrypted excels: import win32com.client import sys import os foldername = ('C:\\Users\\') password = 'ORANGE' pmaster = (r'C:\Users') xlApp = win32com.client.Dispatch("Excel.Application") xlApp.Visible = False wb = xlApp.Workbooks.Open(foldername, False, True, None, password) sh1 = wb.Sheets('sheet1') #sheet name1 sh2 = wb.Sheets('sheet2') #sheet name2 sh3 = wb.Sheets('sheet3') #sheet name2 out1 = sh1.Range("B2").value out2 = sh1.Range("D2").value out3 = sh1.Range("F2").value out4 = sh2.Range("H152").value out5 = sh3.Range("D3").value print(out1,out2,out3,out4,out5)Just need to loop through the excels and paste data into a master sheet making sure that if the new data matches the row in column A to replace with the new data (so most recent data is used) any help would be great Thank you so much in advance |