Unprotected and reprotect a worksheet

[Joe9238]

Hey,
I have a macro that does things to xls files that are found in a long list of hyperlinks. Some of these hyperlinks cannot be opened because they are protected. Is there a way to unprotect and reprotect any protected files?

This is the relevant part of the code:

CodeNames = Range("Z2:Z" & Cells(Rows.Count, "Z").End(xlUp).Row)   'the list of hyperlinksApplication.ScreenUpdating = False
For i = 1 To UBound(CodeNames, 1)                              'for each link in the list
    If InStr(1, CodeNames(i, 1), ".xls") > 0 Then             'if it is an xls file
    On Error GoTo linemarker3                                       'on error will not work on skipping unprotected sheets
        If Not WorkbookOpen(CStr(Split(CodeNames(i, 1), "\")(UBound(Split(CodeNames(i, 1), "\"))))) Then
            Set wbTarget = Workbooks.Open(CodeNames(i, 1)) 'this part gets highlighted when debugging

Function WorkbookOpen(WorkBookName As String) As Boolean
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[MUKESHY12390]

first thing why your are using hyperlink? just take out path and filename details and loop through.


I'm assuming you declared "CodeNames" as range (CodeNames = Range("Z2:Z" & Cells(Rows.Count, "Z").End(xlUp).Row))

and here looks like you treat as CELL Workbooks.Open(CodeNames(i, 1))
to open workbook is simple

workbooks.open("pathdeails")