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
|
|||
|
|||
Opening Protected EXCEL worksheets to update linked data
I have the following code that should open all EXCEL workbooks in a
specified path, and unprotoect any password-protected worksheets to allow for Link Updates, then close the workbook after password protecting it. For some reason, I can't get this code to work -- any ideas? ================================ I put the following code in a general module of a sheet1 of a workbook sub UpdateAllLinks() Dim vLinkSources Dim iLinkSource As Integer Dim AnySheet As Worksheet sPath = " C:\Documents and Settings\Shane\My Documents\Harcourt Assessments\password" sName = Dir(sPath & "*.xls") do while sName "" set bk = Workbook.Open(sPath & sName) 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 bk.Close Savechanges:=True sName = Dir() Loop End Sub Then I put a command button on worksheet1 in that workbook, and added the following code. Private Sub CommandButton1_click() UpdateAllLinks End sub Thanks in advance for any assistance |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update query on more than one table | jwr | Running & Setting Up Queries | 9 | March 22nd, 2006 05:18 AM |
cross office program data sharing | m_jurrens | General Discussion | 0 | December 16th, 2005 03:36 PM |
Need to disable a prompt in Excel when trying to programatically update links in a PPT Presentation that is linked to Excel | shysue25 | Powerpoint | 0 | November 3rd, 2005 09:08 PM |
PST file has reached maximum size | Jeff C | General Discussion | 2 | October 6th, 2005 01:35 PM |
auto format 5 worksheets out of 6 when opening excel spreadsheet. | mike | Worksheet Functions | 1 | December 26th, 2003 08:37 PM |