Simplifying my previous question ...

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. 


0
7/5/2006 10:08:25 PM
excel 39879 articles. 2 followers. Follow

2 Replies
538 Views

Similar Articles

[PageSpeed] 41

Sure record a macro when you unprotect the sheet,  do some things,
and then protect the sheet again.     You will be able to extract
at the very least the code for unprotecting (which you already have)
and the code for protecting again.     (yes you include the password in the
code) --  An alternative is to protect the sheet without the password.
Sometimes the password is entered solely to prevent others from accidentally
supplying a password in reprotecting the sheet.

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

<doctorjones_md@yahoo.com> wrote in message news:%23qeyK%23HoGHA.1248@TK2MSFTNGP05.phx.gbl...
> 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.
>
>


0
7/6/2006 1:13:31 AM
Thanks David -- I'm ALMOST there ...

Do you see anything that I may be missing?  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?



      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

======================================================

"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message 
news:O1w1DmJoGHA.3440@TK2MSFTNGP04.phx.gbl...
> Sure record a macro when you unprotect the sheet,  do some things,
> and then protect the sheet again.     You will be able to extract
> at the very least the code for unprotecting (which you already have)
> and the code for protecting again.     (yes you include the password in 
> the
> code) --  An alternative is to protect the sheet without the password.
> Sometimes the password is entered solely to prevent others from 
> accidentally
> supplying a password in reprotecting the sheet.
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
> <doctorjones_md@yahoo.com> wrote in message 
> news:%23qeyK%23HoGHA.1248@TK2MSFTNGP05.phx.gbl...
>> 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.
>>
>>
>
> 


0
7/6/2006 1:44:19 PM
Reply:

Similar Artilces:

shippingmethodcode and other questions
ok I can't belive I can't find an answer to this - either no one has ever changed it or it's so obvious I should just hang up my CRM boots now. On various pages eg invoice, there is a drop down that has shipping methods (DHL etc) Where do I change the values in that list? I have the same problem with Payment Terms and Freight Terms but I am hopping that once I can edit the shipping method drop down I will understand how the others work AND Why does an order have to have a name? the order number is more than enough for me (I am assuming I can make it an order number) but...

Backup program question
My critical files are on my XP computer. For many years I have used 3rd party backup programs to backup files to an external drive. I also keep a portable drive in our safe deposit box. With all of the 3rd party backup utilities available on the market today, I'm concerned that if I ever had to perform a disaster recovery I might have trouble obtaining a copy of the software I use. My question is - what is wrong with using the universal XP backup program? I guess I really don't know why I opted against it in the first place. It's readily available and appears ...

OWA Question #7
Ok I am switching from OWA on Exchange 5.5 to OWA Exchange 2003. I got OWA to work correctly. I just have one question. Some of my Users have there Bookmarks pointing to https://servername/exchange/logon.asp But they get a 404 error pages doesn't exist. Where does Exchange 2003 look for this file so I can put a page redirect so they go to the correct address. Any help would be great. Thank you, Kyle Kyle D, In exchange 2003 with forms based authentication it would be Https://Server/Exchweb/bin/auth/owalogon.asp this points to (in a default install) C:\program files\ex...

Is my bank online? Another question
My bank states that it is online (CoastalFCU) via the money web site, so I upgraded to 2005 deluxe from 2004 deluxe. I can get all the way to the point where I am promted inside of money for my banks username and password. So I contact my bank about this and they state they currently do not support the OFX file transfer. I can download qif files from another site for my bank, but I bought 2005 since it was stated that they were online. Where do I go from here? Thanks, Barry "Barry" <no_one@home.net> wrote in message news:%23J%234$8yyEHA.1192@tk2msftngp13.phx.gbl... >...

Quick PF Usage Question
If someone with a similar configuration could check and let me know some information, I'd appreciate it greatly. We are running Exchange 2003 Enterprise on Windows 2003 Enterprise on a Quad Xeon 700MHz box with 4GB's of RAM. We do have the /3GB /USERENV=xxxx switch in the boot.ini file per MS recommendation. We still have the ADC running as we have not migrated our users yet, just the replicas of our public folders. 14GB's in the public store, and 200MB's in the private store (2 IS dept mailboxes are over). We also have Sophos' MailMonitor Anti-Virus package running re...

Compacting question
I have WinXP, OE6. My computer man made a file folder by size of my OE personal folders, so I would know which ones most badly need compacting (before I do an external backup). I prefer to compact folders individually, rather than "compact all'. Here is my question: The 4 or 5 largest ones, in addition to showing "folder name.dbx" also have a "folder name.bak". When it gets almost to the end of the compacting, I get this message: "The folder is currently in use by Outlook Express or by another application" and it quits the compacting....

Previous Record
How do I access a field from the previous record in a form To programmatically read the value from the previous record, regardless of how the form is sorted or filtered, use the RecordsetClone of the form. This kind of thing: Function GetPreviousValue(frm As Form, strField As String) As Variant On Error GoTo Err_Handler 'Purpose: Return the value from the previous row of the form. 'Keywords: PriorRow PreviousValue Dim rs As DAO.Recordset Set rs = frm.RecordsetClone rs.Bookmark = frm.Bookmark rs.MovePrevious GetPreviousValue = rs(strField) Set rs...

conditional custom formating question
Hi, I need to be able to type in "B" or "S" or "BC" or "SS" into a cel and have the same cell return "Buy" or "Sell" or "Buy to Cover" o "Short Sell" respectively. Is there any way to do this? Thanks -- Message posted from http://www.ExcelForum.com Hi this would require VBA (using an event procedure) Would this be a feasible way for you? -- Regards Frank Kabel Frankfurt, Germany "doublet83 >" <<doublet83.16m2gy@excelforum-nospam.com> schrieb im Newsbeitrag news:doublet83.16m2gy@excelforu...

Exchange 2007 Clustering Questions
Several questions on the new Exchange clustering technologies. For the most part the CCR Cluster Q. I've been reading some design articles and the recommended approach to the "File Witness share". The recommended location is placing the share on the Hub Transport server. A. Wouldn't this be a single point of failure? ...

basic format question...I think !!
I have multiple pages on one sheet running left to right. Is there a way to adjust the height of one row on one page with out effecting the row across the whole sheets on either side of it. I know I can merge or insert rows but I would like to just click and drag the selected cells until I get the correct height. Thanks -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- JG, The entire row must have the same height. Avoid merging cells -- it cause...

DeleteDC() and DeleteObject() Question
Hi guys, Will DeleteDC() and/or DeleteObject() fail if neither have been Created() ? Is there a way I can test it, like this: CDC memDC; // we didn't create it yet if(// was it created ?) memDC.DeleteDC(); Same for a CBitmap except DeleteObject() is used to delete. Thanks, Robert A. "Robert A." <death@invalid.org> wrote in message news:eDl2JuwTEHA.1472@TK2MSFTNGP12.phx.gbl... > Hi guys, > > Will DeleteDC() and/or DeleteObject() fail if neither have been Created() ? > > Is there a way I can test it, like this: > > CDC memDC; > // we didn'...

database size question #2
Hi all - Trying to get a little perspective. Currently running 5.5 sp4 - my priv.edb is 46GB - is this considered big? The storage is big and fast - if 46GB is not considered big, what is - what have some of the larger 5.5 priv.edb been? When I move to 2003 by the end of year, I plan on throwing a large NAS box and using iscsi for the exchange database(s) - and finally giving my users 'unlimited' email storage - what is considered big for a 2003 private store? Many thanks. Peter On Tue, 24 Apr 2007 12:44:59 -0700, "PFG" <petergump@gumps.org> wrote: >H...

MS CRM Certification question
Hi all, I have a question regarding the CRM certification? What, if any, certifications are there available with MS CRM? Is there anything like CRM Professional? Have I earned any certification if I passed the Applications Professional exam? T.I.A -- Kuba Skalbania [MCAD] ...

A question on __declspec(dllimport) and the Windows APIs.
In chapter 19 of Jeff's classic book "Programming Applications for Microsoft Windows", there is a paragraph as follows: "What Importing Really Means The previous section introduced the _ _declspec(dllimport) modifier. When you import a symbol, you do not have to use the _ _declspec(dllimport) keyword-you can simply use the standard C extern keyword. However, the compiler can produce slightly more efficient code if it knows ahead of time that the symbol you are referencing will be imported from a DLL's .lib file. So I highly recommend that you use the _ _declspec(dllimp...

Question about CRM Server migration
HI, I would like to put my CRM app Server onto a new machine. Actually, the apps are on one machine, SQL is on another one and AD is on a third one. I would like to know if the only thing I have to do is to install CRM server on the new machine and specify to use old data and old AD. Question : - What happens with form customization? Do I need to do a "Deployment manager full deploy"? - What happens with custom report that has been deployed? - Is there any way to make SFOulook client point to the new server? Should I be better removing the old machine from the network and using ...

Outlook 2003 and opening link question
I'm wanting to change the way Outlook 2003 opens links/webpages - from opening them in Explorer to Firefox. Please guide me to where the option to change this is. Thanks in advance! There is no such setting in Outlook; set Firefox as your default browser. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Ben" <bswong@bigpond.net.au> wrote in message news:%xboj.9252$421.7874@news-serv...

Exch. 5.5 GAL questions
Ok, I have created three address book views in Exchange 5.5 (Company A, Company B, and Other) and I would like those to be the only address books that users can see. As of this moment, they can see and traverse Global Address List, Company A, Company B, Other, and Recipients. How do I make the GAL and Recipients disappear from the available address books? I read somewhere that by giving the site account search permissions it will make the GAL and Recipients dissappear out of the available address books, however, I have no idea how to edit permissions on the GAL since I can't ...

Validation Data & Goto question
Hi Everyone, I tried this on my own, but got a big headache and no result. I have an excel spread sheet with 9 columns and about 3000 rows of just text. One of the columns has vendor names (column F) and their products. So, for one vendor, there may be one or 100 products listed in rows until next vendor starts. On top of this column I have a "validation listbox" with the list of vendors. If I click & drop down the list, I can pick any one of the vendor names. Then I wanted to go to a row containing first occurrence of that vendor's name. Almost like one would be using V...

Complex coding question re: recordset
If I have an open form and am positioned on a new record with no bound field having been changed (dirty = false) and I call a public function which opens a recordset on the same table that my form is bound to and then do a series of movenexts , can my form become dirty? I'm getting strange results and am wondering if this could be the cause. What I am trying to do is to programatically retrieve a given record by doing an applyfilter. However, what seems to be happening is that somehow my record has become dirty and therefore the form is wanting to do a save before executing t...

COM QUESTION
CAN SOMEONE GIVE AN EXAMPLE OF CALLING A STORED PROCEDURE USING ADO AND APPENDING TO IT USING DEXTERITY. I GOT IT TO WORK BUT WAS WONDERING IF ANYONE KNOWS HOW TO EXECUTE IT. "AS" wrote: > CAN SOMEONE GIVE AN EXAMPLE OF CALLING A STORED PROCEDURE USING ADO AND > APPENDING TO IT USING DEXTERITY. Where is your sproc stored? If it is in a company database or Dynamics database, why don't you just call it directly using call sproc. Much easier than using COM and ADO. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Micr...

License Question: clarification of "non-commercial use" for Office
Hi, <br><br>I am unsure about the right license for me. My company has given me a windows based computer with office installed. This computer is for my exclusive use and not shared with any other employee. However, I prefer OSX and would write the majority of my work related office documents on my private mac. <br><br>I will be getting Office 2008 through you super suite deal. As I have a secondary mac, the 3 licenses of the home version are appealing to me. I don't use Entourage. <br><br>Would I need to get the full version or would the home &am...

->2003 Mail Preview Panel question<-
Hi, Since Outlook 2003, the preview panel for emails is vertical, to the right of the mail list. I don't like that, I prefer to have horizontally split panels, with the emails on the top panel, and the preview underneath it like it was in Outlook98 and 2000. Is there any way to reconfigure that panel horizonatlly instead of vertically? Thanks for any help. Sincerely, Steve JORDI (Remove the K_I_L_LSPAM from my email address) ------------------------------------------------ 1197 Prangins Email: stevejordiK_I_L_LSPAM@hotmail.com Switzerland WWW: www.sjordi.com --------------------...

Excel question for beginner...
My boss wants me to create a worksheet that will autofill cells a particular color in response to the data entered ie if she types in "run" in A1, A1 will autofill red. I am just learning this program...help please??? Select the cells where this is supposed to happen. Go to Format | Conditional Formatting. Set it to Cell Value Equals Type the Value in the Box Provided Click the Format Button to set the necessary formatting You may want to check out: http://www.contextures.com/xlCondFormat01.html for more information. tj "Gregory" wrote: > My boss wants me to create ...

Exchange Front End Server Question
We currently are running SBS 2003 and Enterprise Exchange. Our SBS box is acting as our front end server. We are moving away from SBS and I will be moving exchange to it own server. Any ideas the amount of power a front end server will require? I would think that since it is only handling http requests, the box does not need to be that powerful We have about 35 users with expected growth over the next 3 years to 80 users. Thanks, Mike 3 years? Who knows, in 3 years you might get a 64bit 16GB RAM 12GHz processor machine for $19.95 to run Exchange <insert new marketing name&...

Updating two two tables based upon the response to questions in a subform
Me again. I'm trying to populate two tables based upon the results to questions on a subform. I have a form and one subform. The subform has a variable number of questions related to the form. Each question is limited to either "Pass", "Fail", or "N/A". If the user should select "Fail" on a question, I want to add a record to a table called "tblCARs" and a record to a table called "tbl CARLineItems". However, for every subsequent "Fail" response I want (need) to only add a record to the table "tblCARLi...