Python Forum

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

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

OTH

OTH