VBA Help
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: VBA Help

  1. #1
    New Member
    Join Date
    Mar 2002
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    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,

  2. #2
    Join Date
    Feb 2002
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    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
    '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
    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
    (You can visit http://www.TheWordExpert.com for VBA help and also other office applications)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts