Open file with file picker, get data and close file

[wpryan]

Hi All, I have a situation I would like to solve... I want to run a macro where I want to open a file, get some data and copy it, and then close the workbook without saving. The workbook to select is password protected and I am getting a "run-time error 9: subscript out of range" error on the line to unlock the worksheet (selectedWorkbook.Worksheets("shInstructions").Unprotect Password:=SelWBPW)
Code is below:

Sub AutoPowerMeasurement()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Dim pw As String: pw = shRelease.Range("A14").Value

    shLaserPower.Unprotect Password:=pw
    
    Dim selectedWorkbook As Workbook
    Dim selectedFilePath As Variant
    
    ' Open the file picker dialog and get the selected file path
    selectedFilePath = Application.GetOpenFilename("Excel Files (*.xlsx;*.xlsm),*.xlsx;*.xlsm")
    
    If VarType(selectedFilePath) = vbBoolean And selectedFilePath = False Then ' Check if the user cancelled the file picker
        Exit Sub
    End If
        
    ' Open the selected workbook
    Set selectedWorkbook = Workbooks.Open(selectedFilePath)
    Dim SelWBPW As String: SelWBPW = "21.05.2021"
    selectedWorkbook.Worksheets("shInstructions").Unprotect Password:=SelWBPW
    
    ' Copy the value from shInstructions, cell C9, to shLaserPower, cell B33
    ThisWorkbook.Sheets("shLaserPower").Range("B33").Value = selectedWorkbook.Worksheets("shInstructions").Range("C9").Value 'Park Max
    ThisWorkbook.Sheets("shLaserPower").Range("C33").Value = selectedWorkbook.Worksheets("shInstructions").Range("C10").Value 'Park Min
    ThisWorkbook.Sheets("shLaserPower").Range("B34").Value = selectedWorkbook.Worksheets("shInstructions").Range("D9").Value 'OS Max
    ThisWorkbook.Sheets("shLaserPower").Range("C34").Value = selectedWorkbook.Worksheets("shInstructions").Range("D9").Value 'OS Min
    ThisWorkbook.Sheets("shLaserPower").Range("B35").Value = selectedWorkbook.Worksheets("shInstructions").Range("E9").Value 'OD Max
    ThisWorkbook.Sheets("shLaserPower").Range("C35").Value = selectedWorkbook.Worksheets("shInstructions").Range("E9").Value 'OD Min
    ThisWorkbook.Sheets("shLaserPower").Range("B36").Value = selectedWorkbook.Worksheets("shInstructions").Range("F9").Value 'OS_Other Max
    ThisWorkbook.Sheets("shLaserPower").Range("C36").Value = selectedWorkbook.Worksheets("shInstructions").Range("F9").Value 'OS_Other Min
    ThisWorkbook.Sheets("shLaserPower").Range("B37").Value = selectedWorkbook.Worksheets("shInstructions").Range("G9").Value 'OD_Other Max
    ThisWorkbook.Sheets("shLaserPower").Range("C37").Value = selectedWorkbook.Worksheets("shInstructions").Range("G9").Value 'OD_Other Min
    ' Close the selected workbook without saving changes
    selectedWorkbook.Worksheets("shInstructions").Protect Password:=SelWBPW
    selectedWorkbook.Close False
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    shLaserPower.Protect Password:=pw
    
End Sub

 

[Anthony47]

It seems that the worksheet you are trying to unprotect doesn't exist...
When you get the error, enter the Debug mode, then check that a workbook named exactely "shInstructions" exists in the just-opened workbook

But I seem that you only READ info from that worksheet, so you shouldn't even need to unprotect it to proceed.
As well you don't need to re-protect the sheet, since you'll close the workbook without saving it

 

[wpryan]

It seems that the worksheet you are trying to unprotect doesn't exist...
When you get the error, enter the Debug mode, then check that a workbook named exactely "shInstructions" exists in the just-opened workbook

But I seem that you only READ info from that worksheet, so you shouldn't even need to unprotect it to proceed.
As well you don't need to re-protect the sheet, since you'll close the workbook without saving it

Thanks for your response. When I step through the code, the selected workbook opens and I can see the worksheet:

...I still get the error...

 

[Anthony47]

Well, "shInstructions" is the Codename, whereas the Name is "Instructions"; thus you need to use selectedWorkbook.Sheets("Instructions") or selectedWorkbook.shIntructions

 

[Fluff]

You would need to use

selectedWorkbook.Worksheets("Instructions").Range("C9").Value

You can only directly refer to the codename of sheets in the workbook with the code.