Feb-15-2021, 08:28 PM
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:
Thank you so much in advance
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