VBA Help

[AcTiVation]

I'm in need of help. I have several excel files with different names and passwords. However I need help in developing a script that will be able to go into each file and extract a range of data and paste it into a central file, where the data will be calculated (statistically). I'm not that good at VBA so step by step instructions will be help ful.

Thanks in advanced,
Lauren

[smozgur]

This macro can be written in lots of way. It is also possible to use files without open then directly in excel. This is just one of the ways what you are asking, I hope it helps.


Private Sub myMacro()
Dim wrkOpened As Workbook
'I used an array to files and properties
'First parameter stands for file, 1 to 4 for 4 files
'Second paramater:
'1: file name
'2: password for that file
'3: worksheet name in file which data will be picked up
'4: range in the sheet where data located : for this code just one column can be used
Dim mF(4, 4) As String
Dim i As Integer
Dim tmpRows As Long


'Storing file names and file properties described above: assumed all files in same directory with the main file
mF(1, 1) = "A1.xls": mF(1, 2) = "passA1": mF(1, 3) = "SheetA1": mF(1, 4) = "A1:A4"
mF(2, 1) = "A2.xls": mF(2, 2) = "passA2": mF(2, 3) = "SheetA2": mF(2, 4) = "A1:A7"
mF(3, 1) = "A3.xls": mF(3, 2) = "passA3": mF(3, 3) = "SheetA3": mF(3, 4) = "A1:A3"
mF(4, 1) = "A4.xls": mF(4, 2) = "passA4": mF(4, 3) = "SheetA4": mF(4, 4) = "A1:A6"

'We will open each excel file in this loop
For i = 1 To 4
'Open the password protected excel file
Set wrkOpened = Application.Workbooks.Open(mF(i, 1), , , , mF(i, 2))
'Activate the main workbook : not necessary if range.select isnot used, cells property can be used easily instead
ThisWorkbook.Activate
'tmprows stores the last cell row when data picked up and written into the main document
'again : assume just one column is used for data transferring
ThisWorkbook.Sheets(1).Range("A" & (tmpRows + 1)).Select
'given range from source is being written into the main xls (using A column to store data)
ThisWorkbook.Sheets(1).Range("A" & (tmpRows + 1) & ":A" & (tmpRows + wrkOpened.Sheets(mF(i, 3)).Range(mF(i, 4)).Rows.Count)).Value = wrkOpened.Sheets(mF(i, 3)).Range(mF(i, 4)).Value
'increment the tmpRows to locate cell for next file's data
tmpRows = tmpRows + wrkOpened.Sheets(mF(i, 3)).Range(mF(i, 4)).Rows.Count
'Close opened file without saving
wrkOpened.Close False
Next i
'Locate the cell as A1 in main xls
ThisWorkbook.Sheets(1).Range("A1").Select
End Sub

To be able to use this macro you need:
A1.xls with password passA1 and sheet name SheetA1 in it and also A1:A4 has data in this sheet.
A2.xls with password passA2 and sheet name SheetA2 in it and also A1:A7 has data in this sheet.
...
...
you can see other two files' properties in mF array above (mF stands for the MyFile name)

Then paste this code into the main xls file module then call it or press F5 when you are in VBA designer and also in code.

I hope it is not confused
regards
Oz
(You can visit http://www.TheWordExpert.com for VBA help and also other office applications)