CODEWITHSUNDEEP
vbaexcel
JK V
JK V

Reputation: 21

Unprotect sheet/Workbook

I managed to get this working but some of my client files are protected.

Sub VBA_Read_External_Workbook()

    ' Get customer workbook...
    Dim customerBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim customerFilename As String
    Dim customerWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sheet As String
    
    ' make weak assumption that active workbook is the target
    Set targetWorkbook = Application.ActiveWorkbook
    
    ' get the customer workbook
    filter = "Text files (*.xlsb),*.xlsb"
    caption = "Please Select an input file "
    customerFilename = Application.GetOpenFilename(filter, , caption)
    
    Set customerWorkbook = Application.Workbooks.Open(customerFilename)
    sheet.Unprotect ("CADDRP")
    
    ' assume range is A1 - C10 in sheet1
    ' copy data from customer to target workbook
    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets(1)
    Dim sourceSheet As Worksheet
    Set sourceSheet = customerWorkbook.Worksheets(3)
    
    targetSheet.Range("A1", "C10").Value = sourceSheet.Range("D85", "D95").Value
    
    ' Close customer workbook
    customerWorkbook.Close

End Sub

I squeezed sheet.unprotect inside.

It gives me an error

"Object Required" Run time error '424'.

I'm guessing I missed some variable declaration in the process?

Upvotes: 0

Views: 2573

Answers (1)

CLR
CLR

Reputation: 12254

Assuming you need to Unprotect the Sheet, not the Workbook.. Remove the sheet.Unprotect line where you have it currently and put it back in after setting the SourceSheet:

Set sourceSheet = customerWorkbook.Worksheets(3)
sourceSheet.Unprotect ("CADDRP")

Upvotes: 2

Related Questions