Hello. Sorry to bring this thread up again but it turns out that the workbook unlock function is super important. Essentially we are running into an issue where we unlock the workbook and run the macro but the workbook is already locked again and we still manually type those passwords. IF we mistype one of the passwords then the macro doesnt finish and causes issues.
In an attempt to remedy that I tried to combine the code that you made up to unlock workbooks, the code that unlocks sheets and the code that inserts data into the workbooks. I got the sheet unlock and the insert portion to work but when trying to combine the workbook unlock function it simply doesnt respond now.
Here is what I came up with:
Sub protect()
'
'
' this must repeat for 11 different worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim vFile As Variant
Dim strPath As String
Dim counter As Long
Dim password As String
Const sCOLUMN__FILENAME As String = "A"
Const sCOLUMN__PASSWORD As String = "B"
Const sPASSWORD_SHEET As String = "Passwords"
Const iFIRST_ROW_NO As Integer = 1
Const sFILE_PATH As String = "C:\User\Folder1"
Dim wksPasswords As Worksheet
Dim iLastRowNo As Integer
Dim sPassword As String
Dim sFullName As String
Dim sFileName As String
Dim iRowNo As Integer
Dim wbk As Workbook
'set workbook unlock
Set wksPasswords = ThisWorkbook.Worksheets(sPASSWORD_SHEET)
strPath = ThisWorkbook.Path & "\"
Application.ScreenUpdating = False
'start wb unlock
With wksPasswords
iLastRowNo = .Range(sCOLUMN__FILENAME & .Rows.Count).End(xlUp).Row
For iRowNo = iFIRST_ROW_NO To iLastRowNo
sFileName = .Range(sCOLUMN__FILENAME & iRowNo).Value
sFullName = sFILE_PATH & "\" & sFileName
If Dir$(sFullName) = sFileName Then
sPassword = .Range(sCOLUMN__PASSWORD & iRowNo).Value
Workbooks.Open Filename:=sFullName, password:=sPassword
End If
'end wb unlock
'start sheet unlock & insert block
For Each vFile In Array("Sheet1.xlsm", "Sheet2.xlsm", "Sheet3.xlsm", "Sheet4.xlsm", "Sheet5.xlsm", "Sheet6.xlsm", "Sheet7.xlsm", "Sheet8.xlsm", "Sheet9.xlsm")
Set wb = Workbooks.Open(strPath & vFile)
For Each ws In wb.Worksheets ' loop through all sheets
password = "Pass1"
'Check if sheet is protected and unprotect
If ws.ProtectContents Then
ws.Unprotect password
End If
'Insert, copy, paste
ws.Range("L5:W5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Range("L5:W5").Value = ws.Range("L4:W4").Value
ws.protect password
Next ws
wb.Close SaveChanges:=True
counter = counter + 1
'search and unlock sheets and workbook from start
Next iRowNo
Next vFile
End With
Exit Sub
'error handling block
err:
MsgBox err.Description, "an error occured"
'if the sheet isn't protected then protect it again.
If ws.ProtectContents = False Then
ws.protect password
End If
Application.ScreenUpdating = True
MsgBox counter & "Processed.", vbInformation, "Done Adding"
End Sub
That was a long one and kind of messy due to my quick alterations trying to get it working. Sorry about that.
So this code should 1. unlock the workbook 2. unlock the sheets 3. insert data in all sheets in a range on row 5 from pivot table 4. repeat until all workbooks/sheets have the data inserted 5. lock the sheets 6. lock the workbook 7. if sheets or workbooks not found or something errored then respond with error 8. let user know its completed and how many
Let me know if I should create a new post but any help would be appreciated. Once I can get these combined and working in conjunction- my companies entire problem will be fixed.
Thank you.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks