Copying data from a secured workbook

[indyman]

I have learned a lot from this forum and nearing the end of my project. I want to be able to distribute updates to the distributed workbook. A few details:
1. The WB is protected.
2. All sheets are protected with UserInterfaceOnly= True. (All user interaction is via userforms and viewing of reports.)
3. The WB and sheets are protected via a PW that is set a a constant. The PW is the same on the old and new version of the WB.

What I need to do is copy a partial set of the sheets from the wbUserCopy to the wbNewVersion, at which point the user will start using the new version. I have that all working - but only if the wbUserCopy is unprotected and the affected sheets are visible. Here is some pared down version of the Update sub.

Sub Start_Update()
    sUserFileName = getFName(OpenFile)
    'After FileOpen user selection
    Set wbUserCopy = Workbooks(sUserFileName)
    Set wbNewVersion = ThisWorkbook
    'create backup
    'displays modal userform to show processing
   
    iRow = 1
    Set rSheetList = wbNewVersion.Sheets("MySheet").Range("B61:B70")
   
    'Need to unprotect the user copy here but this next command does not work
    'Application.Run ("'" & wbUserCopy & "'!Unpr_WB")
    For Each rCell In rSheetList
        sSheet = rCell.Text
        If sheetExist(sSheet, wbUserCopy) > 0 Then
            sAfter = rCell.Offset(0, 1)
            ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible
            If sheetExist(sSheet, wbNewVersion) > 0 Then
                wbNewVersion.Sheets(sSheet).Visible = xlSheetVisible
                wbNewVersion.Sheets(sSheet).Delete
                VBA.DoEvents
                'This IF statement does not work unless the WB is unprotected
                If wbUserCopy.Sheets(sSheet).Visible = xlSheetVeryHidden Then
                    wbUserCopy.Sheets(sSheet).Visible = xlSheetVisible
                End If
                wbUserCopy.Sheets(sSheet).Copy Before:=wbNewVersion.Sheets("Sheet1")
                wbUserCopy.Sheets(sSheet).Visible = xlSheetVeryHidden
               
                'Check for successful copy of the sheet and logs success/failure
            Else
                'Same as the True without the sheet delete
            End If
        Else
        'sets some variables
        End If
        iRow = iRow + 1
    Next
    'then runs some closing commands
End Sub

My questions are around how to unprotect the wbUserCopy.
1. Can I do it without it being opened?
2. Can I execute just the command in the Unpr_WB sub from the wbNewVersion, rather than call the sub?

ThisWorkbook.Protect Password:=PW, Structure:=True"

3. Ho can I go about this? Any suggestions or help appreciated.

Thanks

 

[GWteB]

You don't have to either unprotect your source workbook or unprotect your source worksheet nor make that sheet visible in order to copy some data from it to another workbook. The source workbook has to be open though.

 

[indyman]

Hmmm. I was not getting success with the sheets hidden (xlSheetVeryHidden). On the following line of code:

wbUserCopy.Sheets(sSheet).Copy Before:=wbNewVersion.Sheets("Sheet1")

I get a Runtime 1004 "Copy method of worksheet class failed." But if I unhide the sheet, the Copy works. And to run the command to make the sheet xlSheetVisible, I also have to unprotect the WB. (which from a data security point, makes me happy. (else anyone who knew a sheet name could extract data. I know Excel is not a security fortress, but we can only do what we can.)

If you want to set up your own test, here is a bare bones code. Just set up a UserCopy with a "Sheet X" and a "Sheet2" and make the "Sheet2" xlVeryHidden. Then create a NewVersion WB with a "Sheet1" and add this to a module.

Option Explicit

Sub Update_It()

    Dim sUserFileName As String, sSheet As String, sPath As String, _
            sAfter As String, OpenFile As Variant
    Dim wbUserCopy As Workbook 'user's existing workbook with data
    Dim wbNewVersion As Workbook 'new workbook with updates

        Application.DefaultFilePath = ThisWorkbook.Path
        sPath = ThisWorkbook.Path
        OpenFile = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", _
                                            Title:="Select the File", _
                                            MultiSelect:=False)

        Workbooks.Open OpenFile
        Application.EnableEvents = True
        sUserFileName = getFName(OpenFile)
        Set wbUserCopy = Workbooks(sUserFileName)
        Set wbNewVersion = ThisWorkbook

        Application.DisplayAlerts = False

        sSheet = "Sheet2"
        sAfter = "Sheet1"
        wbUserCopy.Sheets(sSheet).Copy Before:=wbNewVersion.Sheets("Sheet1")

End Sub

Function getFName(pf) As String: getFName = Mid(pf, InStrRev(pf, "\") + 1)
    'parses file name from the path
End Function

Unless I am missing something, I believe I still need to issue an WB Unprotect command and an xlSheetVisible visible command.

 

[GWteB]

Unless I am missing something, I believe I still need to issue an WB Unprotect command and an xlSheetVisible visible command.

You're right. I overlooked that you're copying an entire sheet.
Perhaps changing the code below will work for you.

    'Need to unprotect the user copy here but this next command does not work
    'Application.Run ("'" & wbUserCopy & "'!Unpr_WB")

Application.Run ("'" & wbUserCopy.FullName & "'!Unpr_WB")

 

[indyman]

You're right. I overlooked that you're copying an entire sheet.
Perhaps changing the code below will work for you.

    'Need to unprotect the user copy here but this next command does not work
    'Application.Run ("'" & wbUserCopy & "'!Unpr_WB")

Application.Run ("'" & wbUserCopy.FullName & "'!Unpr_WB")

Yes indeed. That resolved it. I should have been able to catch that. Thanks.

 

[GWteB]

You are welcome and thanks for the follow-up.