-
run macro for an entire folder contents
Hi
i have a folder which contain 20 excel files, each file has the same format (different data).
i created another excel file which i use as a template and i open each on the files (one by one) in the folder and run a macro that changes cells according to what in written in the template file.
i want to know if there is an option to create a macro that will run for the entire folder - it will open each of the excel files and will update then according to the macro file automatically.
1.as i understand i need only the macro for opening the each file in the folder the macro for the update is already in my personal, it it correct ?
2. can the file be schedule by the windows scheduled tasks ?
thank a lot.
-
[vba]
Dim oFSO
Sub LoopFolders()
Set oFSO = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
selectFiles .SelectedItems(1)
End With
Set oFSO = Nothing
End Sub
'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr
Set Folder = oFSO.GetFolder(sPath)
For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr
For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
End If
Next file
End Sub
[/vba]
opens all workbooks in a folder.
You could create an Excel workbook with a Workbook_OPen routine that runs your macro, and then schedule this file to be opened by the scheduler, or even create a VBS script that does the same job.
-
macro calls to another macro
first of all thank for the help
1. i don't know where is the place and how to integrate my macro to the code that is written.
2. are there any specific commands that i should write on Workbook_OPen routine in order to run macros automatically as the file open.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules