Interesting problem?
Posted by AcTiVision on December 12, 2001 8:14 AM
I have a 6 folder (all named differently); within those folders I have files (workbooks) that are also name differently; within each workbook there are 3 sheets, all name generically sheet1, sheet2...to sheet3. Now each workbook is password protected with a unique password. Here's the question. Is it possible to have excel grab a range of data from each workbook (from either sheet1, sheet2 or sheet3) and display them in a specified workbook? If so how would that be done, considering that each workbook is password protected with a different password for each workbook.
Thanks in advance,
AcTiVision
Posted by Damon Ostrander on December 12, 2001 10:06 AM
Hi AcTiVision,
I haven't tested this code, but I believe it should at least provide a template for what you want to do. If individual sheets are also password protected this could would require modification. Hopefully the code is self-explanatory with the help of the comments. The arrays can of course be increased in size to handle a large number of workbooks.
'Opens a set of files and copies certain source ranges to destination
'ranges in this workbook. It is assumed that the default (working)
'directory contains the folders named march, april, and may. To set the
'working directory, simply use the File -> Open menu to browse to that
'directory, and cancel the open when there.
SourceRanges = Array("Sheet1!a5:b12", "Sheet3!b4", "Sheet2!m1:m9")
SourcePaths = Array("march/data1.xls", "april/data2.xls", "may/data3.xls")
SourcePasswds = Array("sneezy", "grumpy", "dopey")
DestRanges = Array("Sheet1!a1:b7", "Sheet2!a1", "Sheet3!a1:a9")
'open all workbooks and past from source ranges in SourcePaths workbooks to
'destination ranges in this workbook
For i = 0 To UBound(SourcePaths)
Workbooks.Open Filename:=SourcePaths(i), password:=SourcePasswds(i)
'workbook just opened is now the active workbook
With ActiveWorkbook
.Range(SourceRanges(i)).Copy Destination:=ThisWorkbook.Range(DestRanges(i))
.Close
End With
Next i
Happy computing.
Damon
Posted by Jacob on December 12, 2001 11:33 AM
Also make sure you add application.enablecancelkey=false before you start opening the workbooks otherwise someone could stop the macro with esc or ctrl + alt + break and would have access to the workbooks that were just opened.
Jacob
SourceRanges = Array("Sheet1!a5:b12", "Sheet3!b4", "Sheet2!m1:m9") SourcePaths = Array("march/data1.xls", "april/data2.xls", "may/data3.xls") SourcePasswds = Array("sneezy", "grumpy", "dopey") DestRanges = Array("Sheet1!a1:b7", "Sheet2!a1", "Sheet3!a1:a9") 'open all workbooks and past from source ranges in SourcePaths workbooks to 'destination ranges in this workbook For i = 0 To UBound(SourcePaths) Workbooks.Open Filename:=SourcePaths(i), password:=SourcePasswds(i) 'workbook just opened is now the active workbook With ActiveWorkbook .Range(SourceRanges(i)).Copy Destination:=ThisWorkbook.Range(DestRanges(i)) .Close End With Next i
