Activate a macro within a folder

[CAM]

Hello,

I have about 20 workbooks each containing a macro called "ClearBalances"
within a folder called "Schedules" The macro from each workbook clears the
quarter ending balances activated by a command button. What I want to do is
to use the "ClearBalances" macro and clear out the balances without having
to open the workbook individually. Is there a way to activate the
"ClearBlances" command button by using a seprate Excel sheet and press a
command button that will activate the "ClearBalance" macro.without having to
open all the worksbooks individually. Any suggestions or visit a website
will be appreciated. Thank you in advance.

 

[Dave Peterson]

I think you'll have to open the workbooks--but you can do that work in a macro.

Maybe something like this untested, but compiled code:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk As Workbook

'change the folder here
myPath = "C:\my documents\excel\test\Schedules"
If myPath = "" Then Exit Sub
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
Application.Run "'" & TempWkbk.Name & "'!ClearBalances"
TempWkbk.Save
TempWkbk.Close savechanges:=False
Next fCtr
End If

End Sub

 

[CAM]

Thanks Dave,
I will give it a try.

Cheers

I think you'll have to open the workbooks--but you can do that work in a
macro.

Maybe something like this untested, but compiled code:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk As Workbook

'change the folder here
myPath = "C:\my documents\excel\test\Schedules"
If myPath = "" Then Exit Sub
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
Application.Run "'" & TempWkbk.Name & "'!ClearBalances"
TempWkbk.Save
TempWkbk.Close savechanges:=False
Next fCtr
End If

End Sub