Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to open every file in a folder (Excel 2002)

    Hi,
    I would like my macro below to have a loop, having it open every file in the folder one at a time, then copy some of that opened file's data into the summary file, then close the file and open the next file and copy some of its' data to the summary file, etc. I'm unsure on how to make the individual file names variable... all the files will start with "ProWellness Walking Campaign-" and then it will have each person's name attached to the end of the file name.. I'm not sure that that is even important as I want every file in the folder to be opened (regardless of it's file name), however they need to be opened one at a time, thus the looping thingy. As always, your help is very much appreciated!
    Thanks!!
    Lana

    Sub Consol()
    'Open the first file in the folder and retreive the goal & actual data
    ChDir "K:GroupsWellness"
    Workbooks.Open Filename:= _
    "K:GroupsWellnessProWellness Walking Campaign-Lana.xls"

    'Copy & Paste the Goals worksheet to the Summary file
    Windows("ProWellness Walking Campaign-Lana.xls").Activate
    Sheets("Goal").Select
    Range("A7:K16").Select
    Selection.Copy

    Windows("ProWellness Walking Campaign-Summary.xls").Activate
    Sheet2.Select
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

    'Copy & Paste the Actual worksheet to the Summary file
    Windows("ProWellness Walking Campaign-Lana.xls").Activate
    Sheets("Actual").Select
    Range("A15:K24").Select
    Application.CutCopyMode = False
    Selection.Copy

    Windows("ProWellness Walking Campaign-Summary.xls").Activate
    Sheet2.Select
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
    Windows("ProWellness Walking Campaign-Lana.xls").Activate
    ActiveWindow.Close

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to open every file in a folder (Excel 2002)

    You can copy the following code into a module in the ProWellness Walking Campaign-Summary.xls workbook:

    Sub Consol()
    Const strPath = "K:GroupsWellness"
    Dim strFile As String
    ' Source
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    ' Target
    Dim wbkT As Workbook
    Dim wshT As Worksheet

    ' No display is more efficient
    Application.ScreenUpdating = False

    ' Target workbook
    Set wbkT = ThisWorkbook
    ' Sheet - supply correct name
    Set wshT = wbkT.Worksheets("Summary")

    ' Name of first source workbook
    strFile = Dir(strPath & "*.xls")
    ' Loop
    Do While Not strFile = ""
    ' Open workbook
    Set wbkS = Workbooks.Open(strPath & strFile)
    ' Goal worksheet
    Set wshS = wbkS.Worksheets("Goal")
    ' Copy range
    wshS.Range("A7:K16").Copy
    ' Paste
    wshT.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ' Actual sheet
    Set wshS = wbkS.Worksheets("Actual")
    ' Copy range
    wshS.Range("A15:K24").Copy
    ' Paste
    wshT.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ' Close source workbook
    wbkS.Close SaveChanges:=True
    ' Name of next workbook
    strFile = Dir
    Loop

    ' Clean up
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub

    Notes:
    - The ProWellness Walking Campaign-Summary.xls workbook itself should NOT be stored in the K:GroupsWellness folder, because then the code would try to open it too.
    - You must substitute the name of the target sheet in the line

    Set wshT = wbkT.Worksheets("Summary")

Posting Permissions

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