utility

  • Thread starter Thread starter hurdler
  • Start date Start date
H

hurdler

This may be way simple, but could use some help starting. I want to be
able to select a number of excel files from a menu list and then perfom
a simple macro in every worksheet in each of those files. These steps

- select files for batch
- Loopt through files
- Loop through worksheets in each file
- run a simple macro sequence (Clear Print Area)

thanks
 
Hi hurdler,

Something like this should work:

Sub ClearPrintAreaForWBs()
Dim vFiles As Variant
Dim fil As Variant
Dim wb As Workbook
Dim ws As Worksheet

vFiles = Application.GetOpenFilename(FileFilter:= _
"Microsoft Excel Files (*.xls), *.xls", _
Title:="Select workbooks for which to clear print areas", _
MultiSelect:=True)

If TypeName(vFiles) <> "Boolean" Then
Application.ScreenUpdating = False
For Each fil In vFiles
Set wb = Workbooks.Open(fil)
For Each ws In wb.Worksheets
ws.PageSetup.PrintArea = ""
Next ws
wb.Close SaveChanges:=True
Next fil
Application.ScreenUpdating = True
End If
End Sub


GetOpenFilename returns an array containing paths of selected workbooks.
You can then loop through that array, opening each workbook. Once a
workbook is opened, you can loop through each of its worksheets and clear
the print area, saving and closing the workbook when you're done.
Application.ScreenUpdating will keep the screen from flailing wildly while
this is running (and speed things up).

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Back
Top