Need Help!!!!

[AcTiVation]

I'm in need of VBA code that will collect data from a range in different excel files that are located in different directories with different file names, and each with their own password. Then paste the data ranges from all the files in a central file which will then be used for statistical purposes.

Thanks in advanced.

L.

 

[donalde]

Hi.
You could try method where location of files are on excel sheet, on column A. Column B has file name, and column C passwd.

Important! file location in sheet must be in format \networkdrive_path, not in networkdrive letter.

sub open_all
a=1
while cells(a,1).value <>""
file_location=cells(a,1).value
file_name= cells(a,2).value
file_passwd=cells(a,2).value
workbooks.open file_location & file_name password=file_passwd
call copy_data
wend
end sub

function copy_data
I think you can personaly code this, this would be easy, activate workbook, then worksheet, select range, copy, close activewindow, paste to central function, into first blank row
end function

HTH
donalde