Copying data from a secured workbook

indyman

New Member
Joined
Apr 14, 2021
Messages
47
Office Version
  1. 2019
Platform
  1. Windows
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.
VBA Code:
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?
VBA Code:
ThisWorkbook.Protect Password:=PW, Structure:=True"
3. Ho can I go about this? Any suggestions or help appreciated.

Thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Apr 14, 2021
Messages
47
Office Version
  1. 2019
Platform
  1. Windows
Hmmm. I was not getting success with the sheets hidden (xlSheetVeryHidden). On the following line of code:
VBA 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.

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
    'Need to unprotect the user copy here but this next command does not work
    'Application.Run ("'" & wbUserCopy & "'!Unpr_WB")

VBA Code:
Application.Run ("'" & wbUserCopy.FullName & "'!Unpr_WB")
 
Solution

indyman

New Member
Joined
Apr 14, 2021
Messages
47
Office Version
  1. 2019
Platform
  1. Windows
You're right. I overlooked that you're copying an entire sheet.
Perhaps changing the code below will work for you.
VBA Code:
    'Need to unprotect the user copy here but this next command does not work
    'Application.Run ("'" & wbUserCopy & "'!Unpr_WB")

VBA Code:
Application.Run ("'" & wbUserCopy.FullName & "'!Unpr_WB")
Yes indeed. That resolved it. I should have been able to catch that. Thanks.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the follow-up.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,130
Messages
5,835,569
Members
430,367
Latest member
glastonbury

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top