Macro loop opening files in folder successivly

  • Thread starter Thread starter Snoopy
  • Start date Start date
S

Snoopy

Hey guys
Her is my little challenge:

Filename (example):
c:\Aplications\Report\ReportA.xls
- containing a "main macro" opening the files (below) one by one, copy
data from them and paste into the ReportA.xls.

c:\Aplications\Report\user\aaa.xls
c:\Aplications\Report\user\bbb.xls
c:\Aplications\Report\user\ccc.xls
c:\Aplications\Report\user\ddd.xls
etc

Main Macro:

Sub Main ()
' Preparing
Start
'Copying from users
aaa
bbb
ccc
'Calculating and formatting general report
Stop
End sub

My sub usermacros - placed in the main file - open the user files
respectively - named aaa, bbb, ccc, etc

Sub macro:
Sub aaa ()
Application.StatusBar = "Collecting data from aaa"
Dim i As Integer
Dim MyFiles(1)
Dim MyPasswords(1)

MyFiles(1) = "c:\Aplications\Report\ReportA.xls"
MyPasswords(1) = ""
Workbooks.Open Filename:="c:\Aplications\Report\user\aaa.xls",
ReadOnly:=True, Password:=MyPasswords(1), UpdateLinks:=0

Sheets("Register").Visible = True
Sheets("Register").Select
Range("A2").Select

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
Selection.Copy
Range("A2").Select

Windows("ReportA.xls").Activate

Sheets("Sample").Select

Selection.Offset(0, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.End(xlDown).Select
Selection.Offset(1, 0).Range("A1").Select
Windows("ReportA.xls").Activate
Application.CutCopyMode = False ' prevent message about clipboard
ActiveWorkbook.Close savechanges:=False
End Sub


So far I have hardcoded the macro to open each file by filename, but
find it difficult to update over time.
I hope to make my macro automaticly run through all files in the Report
\user-folder and do the stuff on each file in a smother way by using
lopps - but I'm stucked.

Anyone that feel the comfort in helping me?


Thankfully regards
Snoopy
 
Hi Snoopy

Have a look at the code below. It's not fully tested, but the basics seem to
work. It finds the first xls file in the specified directory, then call you
(modified) code with the result. It then loops through the same directory,
repeating the process, until no other xls files are found.

Sub test()

Dim MyFile

' Returns filename with specified extension. If more than one *.xls
' file exists, the first file found is returned.
MyFile = Dir("c:\Aplications\Report\user\*.xls")
If MyFile = "" Then End ' End subroutine if no *.xls files found

Call aaa(MyFile) ' call your macro

Do While MyFile <> ""
' Call Dir again without arguments to return the next *.xls file in the
' same directory.
MyFile = Dir

If MyFile = "" Then End ' End subroutine if no further *.xls files found
Call aaa(MyFile) ' call your macro

Loop


End Sub
Sub aaa(MyFile)
Application.StatusBar = "Collecting data from " & MyFile
Dim i As Integer
Dim MyFiles(1)
Dim MyPasswords(1)

MyFiles(1) = "c:\Aplications\Report\ReportA.xls"
MyPasswords(1) = ""
Workbooks.Open Filename:="c:\Aplications\Report\user\" & MyFile, _
ReadOnly:=True, Password:=MyPasswords(1), UpdateLinks:=0
'the rest of your macro goes here
 
process each file using Dir within a loop

Filename = Dir("c:\Aplications\Report\user\*.xls")
Do While Filename <> ""

'process file
Filename = Dir
Loop
 
process each file using Dir within a loop

    Filename = Dir("c:\Aplications\Report\user\*.xls")
    Do While Filename <> ""

        'process file
        Filename = Dir
    Loop

--
__________________________________
HTH

Bob



















– Vis sitert tekst –
Click to expand...

Thanks a lot both of you
My macro is working perfectly now - nice! :)))

Regards
Snoopy
 
Ian - or whome it might interest :)
I try to make the macro a bit more flexible to adopt dynamic folders
by using a formula in my workbook; UserList.xls
Range A1.value contains this dynamic filpath as expression - exmpl: c:
\Aplications\Report\ - which more easily can be used to
direct the macro to search in various folders - as C:\variable\variable
\
I cant find out how to change the hardcoded expression:"c:\Aplications
\Report\" with formula-based expression:
"C:\variable\variable\"

Best Regards
Snoopy

Input from Ian
Sub test()
Dim MyFile
' Returns filename with specified extension. If more than one *.xls
' file exists, the first file found is returned.
MyFile = Dir("c:\Aplications\Report\user\*.xls")
If MyFile = "" Then End ' End subroutine if no *.xls files found
Call aaa(MyFile) ' call your macro
Do While MyFile <> ""
' Call Dir again without arguments to return the next *.xls file in
the
' same directory.
MyFile = Dir
If MyFile = "" Then End ' End subroutine if no further *.xls files
found
Call aaa(MyFile) ' call your macro
Loop
End Sub

Sub aaa(MyFile)
Application.StatusBar = "Collecting data from " & MyFile
Dim i As Integer
Dim MyFiles(1)
Dim MyPasswords(1)
MyFiles(1) = "c:\Aplications\Report\ReportA.xls"
MyPasswords(1) = ""
Workbooks.Open Filename:="c:\Aplications\Report\user\" & MyFile, _
ReadOnly:=True, Password:=MyPasswords(1), UpdateLinks:=0
'the rest of your macro goes here
End sub
 
Back
Top