Quarantine workbooks

[Chacky]

is it possible to do a global quarantine of a selection of workbooks by
adding the word 'QUARANTINE' to the same cell in each workbook, without
having to open each workbook one at a time. Hopefully I would be able to
specifiy the size and colour of font also.

i.e. selecting a number of workbooks in a folder then running a macro to do
an update to a particular cell. Or is there a system already built into Excel
to allow this? Ihaven't found one.

 

[Tim879]

I'm not sure of the code to open the workbooks but if you open all of
the workbooks first, then this macro will work.


sub quarantine()

for x =1 to application.workbooks.count
application.workbooks(x).select
sheets(1).select
range(a1).value = "Quarantine"
next
end sub

 

[Mike H]

Hi,

Right click a sheet tab of an open workbook, view code and paste this in and
run it. You will need to set the pasth to what you want. Currently the root
of C

Sub quarantimeME()
Dim fn As String
TargetFolder = "C:\"
FileFilter = "*.xls"
Application.ScreenUpdating = False
fn = Dir(TargetFolder & FileFilter)
While Len(fn) > 0
If fn <> ThisWorkbook.Name Then
Workbooks.Open TargetFolder & fn
ActiveWorkbook.Sheets("sheet1").Range("A1").Value = "Quarrantine"
ActiveWorkbook.Close True
End If
fn = Dir
Wend
End Sub

Mike

 

[Chacky]

Tim879

Thanks for the help. This is the sort of thing I am looking for but when I
run the macro I get the message

RUN TIME ERROR '438'
OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD