hellonwheels
06-16-2014, 10:52 AM
I have a 40 GB Directory full of subfolders and .xls & .xlsx workbooks. All of this data was manually entered and no stored in a database anywhere (archaic I know but that's the set-up I got :( ) I need to iterate all folders & subfolders in one main directory and generate a list of all workbooks that have a value (now this column varies from book to book but is sorted as money meaign has the $ sign etc) > 1K on any of the worksheets within the workbook. Then once the list has been made, cycle back through the list just made and replace the values > 1K with the text 'over one thousand'
I have seen several ways to open the workbook and then perform the check, but I am just thinking speed wise what is the fastest way (which is why I thought not open the workbook) -- and the worksheets will need to be visible the next time the workbook is opened. Oh yes, last but not least a few of these workbooks may be password protected so the coding will need to ignore any prompt for a password -- I saw on one site where you can do an if statement something like the below
if err.number = 1004 then it has a password and just use the On Error Resume Next - but I have seen some people say to avoid Resume Next like the black plague!
Any and all assistance is greatly appreciated.
I have seen several ways to open the workbook and then perform the check, but I am just thinking speed wise what is the fastest way (which is why I thought not open the workbook) -- and the worksheets will need to be visible the next time the workbook is opened. Oh yes, last but not least a few of these workbooks may be password protected so the coding will need to ignore any prompt for a password -- I saw on one site where you can do an if statement something like the below
if err.number = 1004 then it has a password and just use the On Error Resume Next - but I have seen some people say to avoid Resume Next like the black plague!
Any and all assistance is greatly appreciated.