PDA

View Full Version : Solved: Only do if not read only.



khalid79m
11-28-2008, 08:31 AM
:dunno
Private Sub SITE()
Application.EnableEvents = False

Dim i As Integer, wb As Workbook

With Application.FileSearch
.NewSearch
.LookIn = "\\Uk3\traner\jj\UK\Edirgh\ (file://\\Uk3\traner\jj\UK\Edinburgh\)"
.SearchSubFolders = True
.Filename = "*.xls"
.Application.DisplayAlerts = False
.Execute
For i = 1 To .FoundFiles.Count

ActiveWorkbook.Name

Set wb = Workbooks.Open(Filename:=.FoundFiles(i), ReadOnly:=False)

Sheets("CallData").Select
ActiveSheet.Unprotect Password:="54YY4F"
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("B1").FormulaR1C1 = "12/3/2008 12:00"

wb.Close savechanges:=True

Next i
End With
Application.EnableEvents = True


End Sub

I have the above script, I need it only run if the workbook is not in readonly and save the file , if it is readonly then simply close the file without save...

rbrhodes
11-28-2008, 12:58 PM
khalid,

Seems to work:


'Get file attributes (1 = readonly)
Ronly = GetAttr(.FoundFiles(i)) Mod 2


ReadOnly attribute = 1 and is the only odd number. So Mod 2 leaves 1 if readonly, 0 if not. See below:


Option Explicit
Private Sub SITE()

Dim i As Long
Dim Ronly As Long
Dim WB As Workbook

'No recursion/Warnings
With Application
.EnableEvents = False
.DisplayAlerts = False
End With

'Find file(s)
With Application.FileSearch
.NewSearch
.LookIn = "c:\" '"\\Uk3\traner\jj\UK\Edirgh\ (file://\\Uk3\traner\jj\UK\Edirgh\)"
.SearchSubFolders = False 'True
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count

'//???
'ActiveWorkbook.Name

'Get file attributes (1 = readonly)
Ronly = GetAttr(.FoundFiles(i)) Mod 2

' If Not User = "adamant" & " " & "hater" of the dreaded GoTo Then
' Echo "<G>"
' Else
' change it to an 'IF'
' End If

'Is read only, skip it
If Ronly = 1 Then GoTo NextFile

'Open file
Set WB = Workbooks.Open(Filename:=.FoundFiles(i), ReadOnly:=False)
'Set active sheet
Sheets("CallData").Select
With ActiveSheet
'Do stuff
.Unprotect Password:="54YY4F"
If .AutoFilterMode = True Then
.AutoFilterMode = False
Range("B1").FormulaR1C1 = "12/3/2008 12:00"
'//Reprotect sheet?
.Protect Password:="54YY4F"
'Close with changes saved
WB.Close savechanges:=True
Else
'//Reprotect sheet?
.Protect Password:="54YY4F"
'//Just close
With WB
.Saved = True
.Close
End With
End If
End With
'Skip Read only file
NextFile:
Next i
End With

'Reset BOTH
With Application
.EnableEvents = True
.DisplayAlerts = True
End With

'Destroy object
Set WB = Nothing

End Sub

khalid79m
12-01-2008, 08:19 AM
Thanks I havent had chance to impliment this properly but it seems to work :) thankyou