Open all .xls files in a target folder, run macro then next

[Forgone]

I've got a macro which I use at least 43 times a month and I'm
wondering what would be the best way to automate it.
Currently, I'm opening up each workbook manually, running the macro
which does something, saves the document and closes it. The only
manual part is to open the workbook.

Each month, these workbooks are located in a different folder (eg: for
month) and I'm wondering what the best way to automate the "open
workbook, call existing macro" for each workbook in a target folder.
When the macro runs for the first time it would prompt the user for
the 'target' folder and off it runs.

 

[Bernie Deitrick]

You could select all the files using the fileopen dialog:

Sub OpenMultipleUserSelectedFiles()
Dim FileArray As Variant
FileArray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileArray) Then
For i = LBound(FileArray) To UBound(FileArray)
Workbooks.Open FileArray(i)
'Call your macro here
Application.DisplayAlerts = False
Activeworkbook.Save
Activeworkbook.Close
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[JP Ronse]

Hi Forgone,

Try something like this...
You will reciece a window to select all the files...

Sub OpenFiles()
Dim files As Variant, intCount As Integer

files = Application.GetOpenFilename(MultiSelect:=True)
Stop

For intCount = 1 To UBound(files)
Debug.Print files(intCount)
''' open the files,
''' your macro name here
''' save and close the file
Next intCount
End Sub

 

[Forgone]

You could select all the files using the fileopen dialog:

Sub OpenMultipleUserSelectedFiles()
Dim FileArray As Variant
FileArray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileArray) Then
   For i = LBound(FileArray) To UBound(FileArray)
      Workbooks.Open FileArray(i)
    'Call your macro here
    Application.DisplayAlerts = False
    Activeworkbook.Save
    Activeworkbook.Close
    Next i
Else:
   MsgBox "You clicked cancel"
End If
End Sub

HTH,
Bernie
MS Excel MVP

That's starting to look like an easier option, have the user select
the files rather than trying to "Browse for Folder", I've come across
this page which lists how to "browse for folder"

http://www.cpearson.com/excel/BrowseFolder.aspx

 

[Forgone]

You could select all the files using the fileopen dialog:

Sub OpenMultipleUserSelectedFiles()
Dim FileArray As Variant
FileArray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileArray) Then
   For i = LBound(FileArray) To UBound(FileArray)
      Workbooks.Open FileArray(i)
    'Call your macro here
    Application.DisplayAlerts = False
    Activeworkbook.Save
    Activeworkbook.Close
    Next i
Else:
   MsgBox "You clicked cancel"
End If
End Sub

HTH,
Bernie
MS Excel MVP

I've found another line of code which looks promising...... could this
be implemented?
It appears to allow the user to define the folder.
However, I just tried it and it didn't open the DialogFolder, just
diverted to My Documents.......


With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show <> -1 Then MsgBox "No folder selected! Exiting sub...":
Exit Sub
myDir = .SelectedItems(1)
End With

This would select the folder,

 

[Bernie Deitrick]

Below is the code that I use to browse for a folder, and you would use it like

Sub DoAllFilesInFolde()
Msg = "Select the folder of files."
SelectedDir = GetDirectory(Msg)
MsgBox "The directory is " & SelectedDir
WorkFile = Dir(SelectedDir & "\*.xls")
Do While WorkFile <> ""
Workbooks.Open FileName:=SelectedDir & "\" & WorkFile
MsgBox ActiveWorkbook.Name
'Do other stuff here
ActiveWorkbook.Close False
WorkFile = Dir()
Loop
End Sub



HTH,
Bernie
MS Excel MVP



Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

'works from either here, or from another userform
'Here is example code
Sub DisplayDirectoryDialogBox()
Msg = "Select a folder under which are all your CDF Files."
SelectedDir = GetDirectory(Msg)
MsgBox "The directory is " & SelectedDir
End Sub

Function GetDirectory(Msg) As String
Dim bInfo As BROWSEINFO
Dim path As String
Dim x As Long

'If you are calling it from a userform, you need this part of the code
'This is the Userform that has a control that calls GetDirectory()
' bInfo.hOwner = FindWindow(vbNullString, "YourUserForm.Caption")
bInfo.pidlRoot = 0& ' Root folder = Desktop
bInfo.lpszTitle = Msg
bInfo.ulFlags = &H1 ' Type of directory to return

' Display the dialog
x = SHBrowseForFolder(bInfo)

' Parse the result
path = Space$(512)
If SHGetPathFromIDList(ByVal x, ByVal path) Then
GetDirectory = Left(path, InStr(path, Chr$(0)) - 1)
Else: GetDirectory = ""
End If
End Function

You could select all the files using the fileopen dialog:

Sub OpenMultipleUserSelectedFiles()
Dim FileArray As Variant
FileArray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileArray) Then
For i = LBound(FileArray) To UBound(FileArray)
Workbooks.Open FileArray(i)
'Call your macro here
Application.DisplayAlerts = False
Activeworkbook.Save
Activeworkbook.Close
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub

HTH,
Bernie
MS Excel MVP

I've found another line of code which looks promising...... could this
be implemented?
It appears to allow the user to define the folder.
However, I just tried it and it didn't open the DialogFolder, just
diverted to My Documents.......


With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show <> -1 Then MsgBox "No folder selected! Exiting sub...":
Exit Sub
myDir = .SelectedItems(1)
End With

This would select the folder,