If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Simplifying my previous Request for Assistance
I suspect that (since I didn't get any response on my previous request for
assistance) I may need to approach this in stages ... Assuming that Information Rights Management (IRM) won't give me the detailed permissions I need, basically, here's what I need: 1. A Macro that will unprotect a series of Workbooks (approximately 200 of them) so that the Auto Update function in Linked Data can update without user intervention. 2. I copied this Macro from a previous post -- how can I modify the following code to achieve what I need from it: Can I use something like this? I assume that I'll need something like this to unprotect the affected worksheets: ActiveSheet.Unprotect Password:="" '===I assume that I'll enter my worksheet password between the "" Private Sub Workbook_Open() Dim vLinkSources Dim iLinkSource As Integer vLinkSources = ThisWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(vLinkSources) Then For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources) ThisWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks Next End If End Sub I'll need to re-protect the affected worksheets upon closing: ActiveSheet.Protect Password:="" '===I assume that I'll enter my worksheet password between the "" End Sub Any help in setting me straight on this would be greately appreciated -- thanks in advance. |
#2
|
|||
|
|||
Simplifying my previous Request for Assistance
Yes, the .Unprotect and .Protect code you have has the proper syntax. The
password will go between the double-quotes. Now, unless these are single-page workbooks, or you know with absolute certainty that they will open to the proper sheet, you need to be prepared to handle that. If you know the sheet's name, you could add something like Worksheets("NameOfSheetINeed").Activate in the _Open event. If you don't know the sheet's name, then you can set up to just unprotect them all: Dim AnySheet as Worksheet For Each AnySheet in ActiveWorkbook.Worksheets ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect Password:="mypassword" Next and a similar loop to Protect them again later. I kind of doubt that "mypassword" will be the right one, but ... Unprotect before you start trying to update the links (above the If Not IsEmpty() statement) then put them back into protected state below the End If statement. OH - when you open a workbook, it becomes the active workbook, so in the code use ActiveWorkbook. rather than ThisWorkbook. ThisWorkbook refers to the workbook that the code physically resides in. " wrote: I suspect that (since I didn't get any response on my previous request for assistance) I may need to approach this in stages ... Assuming that Information Rights Management (IRM) won't give me the detailed permissions I need, basically, here's what I need: 1. A Macro that will unprotect a series of Workbooks (approximately 200 of them) so that the Auto Update function in Linked Data can update without user intervention. 2. I copied this Macro from a previous post -- how can I modify the following code to achieve what I need from it: Can I use something like this? I assume that I'll need something like this to unprotect the affected worksheets: ActiveSheet.Unprotect Password:="" '===I assume that I'll enter my worksheet password between the "" Private Sub Workbook_Open() Dim vLinkSources Dim iLinkSource As Integer vLinkSources = ThisWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(vLinkSources) Then For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources) ThisWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks Next End If End Sub I'll need to re-protect the affected worksheets upon closing: ActiveSheet.Protect Password:="" '===I assume that I'll enter my worksheet password between the "" End Sub Any help in setting me straight on this would be greately appreciated -- thanks in advance. |
#3
|
|||
|
|||
Simplifying my previous Request for Assistance
("JLatham") ~~ Thank you for your guidance and suggestions -- here's the
code that I have now: Private Sub Workbook_Open() Dim vLinkSources Dim iLinkSource As Integer Dim AnySheet As Worksheet For Each AnySheet In ActiveWorkbook.Worksheets ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect Password:="mypassword" Next vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(vLinkSources) Then For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources) ActiveWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks Next End If For Each AnySheet In ActiveWorkbook.Worksheets ActiveWorkbook.Worksheets(AnySheet.Name).Protect Password:="mypassword" Next End Sub Do you see anything that I may be missing? Now, here's the MOST important thing I need this code to do (assuming there are no further modifications needed) ... This code (Macro) needs to perform this same task on approximately 200 different Workbooks (all residing in the same Network Directory), but ONLY when a Command Button is pressed. I'm assuming that I'll need to create a Form (in ACCESS perhaps) and call this Macro -- correct? How would I do this, and how would I get this Macro to perform this Link Update on all 200 or so Workbooks? "JLatham" wrote in message ... Yes, the .Unprotect and .Protect code you have has the proper syntax. The password will go between the double-quotes. Now, unless these are single-page workbooks, or you know with absolute certainty that they will open to the proper sheet, you need to be prepared to handle that. If you know the sheet's name, you could add something like Worksheets("NameOfSheetINeed").Activate in the _Open event. If you don't know the sheet's name, then you can set up to just unprotect them all: Dim AnySheet as Worksheet For Each AnySheet in ActiveWorkbook.Worksheets ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect Password:="mypassword" Next and a similar loop to Protect them again later. I kind of doubt that "mypassword" will be the right one, but ... Unprotect before you start trying to update the links (above the If Not IsEmpty() statement) then put them back into protected state below the End If statement. OH - when you open a workbook, it becomes the active workbook, so in the code use ActiveWorkbook. rather than ThisWorkbook. ThisWorkbook refers to the workbook that the code physically resides in. " wrote: I suspect that (since I didn't get any response on my previous request for assistance) I may need to approach this in stages ... Assuming that Information Rights Management (IRM) won't give me the detailed permissions I need, basically, here's what I need: 1. A Macro that will unprotect a series of Workbooks (approximately 200 of them) so that the Auto Update function in Linked Data can update without user intervention. 2. I copied this Macro from a previous post -- how can I modify the following code to achieve what I need from it: Can I use something like this? I assume that I'll need something like this to unprotect the affected worksheets: ActiveSheet.Unprotect Password:="" '===I assume that I'll enter my worksheet password between the "" Private Sub Workbook_Open() Dim vLinkSources Dim iLinkSource As Integer vLinkSources = ThisWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(vLinkSources) Then For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources) ThisWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks Next End If End Sub I'll need to re-protect the affected worksheets upon closing: ActiveSheet.Protect Password:="" '===I assume that I'll enter my worksheet password between the "" End Sub Any help in setting me straight on this would be greately appreciated -- thanks in advance. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Meeting Request appearing in calendar | ddt | General Discussion | 0 | August 25th, 2004 07:14 AM |
Outlook 2002 - meeting request forwarding problem | Derek | Calendar | 0 | June 9th, 2004 02:55 PM |
Meeting Request Compatibility: Outlook 2003 vs 2000 | Ian | Calendar | 1 | June 7th, 2004 08:02 PM |
Meeting Request Ocourances | Jay Gustafson | Calendar | 2 | June 6th, 2004 03:58 PM |
FW: iCalendar request performs differently in outlook 2002 than outlook 2003 | Aguila_Eagle | Calendar | 0 | June 6th, 2004 01:43 PM |