Unprotect another Workbook via Macro
Unprotect another Workbook via Macro
(OP)
Hello:
I am writing a macro to insert data from one workbook (source) into another workbook comprised of one sheet (target)that is protected.
In code snippet below, I activate my source worksheet, define my copy range (1st With/End With block) and copy it.
Then I open my protected target workbook, activate it, try to unprotect it. Then the 2nd With/End With block inserts and pastes the lines of data into the target. Then reprotect and save and close the target workbook.
Data does not get pasted to target, just rows of blank cells.
The code, as shown with the Protect and Unprotect lines commented out, works perfectly if the taget workbook is unptotected.
Any ideas?
Thanks
-Pete
' Compile a range of output for valid entries, then write data to archive
' Need at least 1 valid entry so that ranges below are valid thus check if b at least zed
If b >= 0 Then
Workbooks("TubingCost.xls").Activate
With Worksheets("Summary")
.Range(.Cells(a, 1), .Cells(a + b, c)).Select
Selection.Copy
End With
Workbooks.Open DatabasePath
Workbooks("TubingHistory.xls").Activate
' Worksheets("TubingHist").Unprotect BUG- ONLY WRITES LINES OF BLANK DATA
With Worksheets("TubingHist")
.Range(.Cells(a1, 1), .Cells(a1 + b, c)).Select
Selection.Insert Shift:=xlDown
End With
' Worksheets("TubingHist").Protect BUG- ONLY WRITES BLANK DATA
Workbooks("TubingHistory.xls").Close SaveChanges:=True
Workbooks("TubingCost.xls").Activate
Worksheets("Summary").Activate
End If
I am writing a macro to insert data from one workbook (source) into another workbook comprised of one sheet (target)that is protected.
In code snippet below, I activate my source worksheet, define my copy range (1st With/End With block) and copy it.
Then I open my protected target workbook, activate it, try to unprotect it. Then the 2nd With/End With block inserts and pastes the lines of data into the target. Then reprotect and save and close the target workbook.
Data does not get pasted to target, just rows of blank cells.
The code, as shown with the Protect and Unprotect lines commented out, works perfectly if the taget workbook is unptotected.
Any ideas?
Thanks
-Pete
' Compile a range of output for valid entries, then write data to archive
' Need at least 1 valid entry so that ranges below are valid thus check if b at least zed
If b >= 0 Then
Workbooks("TubingCost.xls").Activate
With Worksheets("Summary")
.Range(.Cells(a, 1), .Cells(a + b, c)).Select
Selection.Copy
End With
Workbooks.Open DatabasePath
Workbooks("TubingHistory.xls").Activate
' Worksheets("TubingHist").Unprotect BUG- ONLY WRITES LINES OF BLANK DATA
With Worksheets("TubingHist")
.Range(.Cells(a1, 1), .Cells(a1 + b, c)).Select
Selection.Insert Shift:=xlDown
End With
' Worksheets("TubingHist").Protect BUG- ONLY WRITES BLANK DATA
Workbooks("TubingHistory.xls").Close SaveChanges:=True
Workbooks("TubingCost.xls").Activate
Worksheets("Summary").Activate
End If





RE: Unprotect another Workbook via Macro
Workbooks.Open DatabasePath
Worksheets("TubingHist").Unprotect
Workbooks("TubingCost.xls").Activate
With Worksheets("Summary")
.Range(.Cells(a, 1), .Cells(a + b, c)).Copy
End With
Workbooks("TubingHistory.xls").Activate
With Worksheets("TubingHist")
.Range(.Cells(a1, 1), .Cells(a1 + b, c)).Insert Shift:=xlDown
End With
Worksheets("TubingHist").Protect
Workbooks("TubingHistory.xls").Close SaveChanges:=True
Workbooks("TubingCost.xls").Activate
Worksheets("Summary").Activate
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Unprotect another Workbook via Macro
Thanks for the tip. Yes, I have the code working now, odd that Excel forgets, it was a behaviour I did not expect.
Thanks
-Pete
RE: Unprotect another Workbook via Macro
Thanks,
Jeff
RE: Unprotect another Workbook via Macro
Jeff:
Funny you should ask. In my same macro, I later chose to alter a worksheet that was protected, and then reprotect after I was done with the modifications. But you must know the password that the sheet was originally protected with (ie MyPassword below)
Worksheets("Summary").Unprotect Password:="MyPassword"
' Do operations needed here
Worksheets("Summary").Protect Password:="MyPassword"
Hope this helps, from one rookie to another.
-Pete
RE: Unprotect another Workbook via Macro
Thanks. This looks like it will solve my problem! This forum is incredibly useful, don't you think!
Thanks again,
Jeff
RE: Unprotect another Workbook via Macro
The statement workbook.protect password:="whatever" reveals the protection password to whomever cares to look in the VBA code. Unless you don't care, but then why use a password at all
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.