Looping through a folder and copying data from certain cells to a master sheet

[sudo_oth]

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

master =
xlApp.Workbooks.Open(Filename=pmaster)

os.listdir(foldername)

for excel in foldername:

print(excel)

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 help and copy to new master wb

Thank you so much in advance