Macros

  • Thread starter Thread starter mickey
  • Start date Start date
M

mickey

I wrote a macros in a seperate workbook that needs to execute the commands in
another workbook.

2 questions:
How do I make it look for another workbook?
Do the commands need additional coding to execute in the destination workbook?

Bascilly, I want to apply password protection to each sheet in the
destination workbook. But, instead of changing each manually, I can just
change it in the macro with search and replace.
 
Sounds like batch processing. Please supply your code.

Or else, use this batch process code; place your code in the area between
'end of first part' and 'beginning of second part':
Sub Example()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim mybook As Workbook
Dim CalcMode As Long
Dim sh As Worksheet
Dim ErrorYes As Boolean

'Fill in the path\folder where the files are
MyPath = "C: \"


If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If


FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If


Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop


With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
On Error GoTo 0

If Not mybook Is Nothing Then

'****************************************************************
' end of first part of Batch Process code
' your Excel code here
' beginning of second part of Batch Process code
‘****************************************************************


End If

Next Fnum
End If

If ErrorYes = True Then
MsgBox "There are problems in one or more files, possible problem:" _
& vbNewLine & "protected workbook/sheet or a sheet/range that
not exist"
End If
End Sub

Regards,
Ryan---
 
Back
Top