Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

what should be an easy one

  Asked By: Pedro    Date: Dec 03    Category: MS Office    Views: 583
  

Three excel files One a dashboard file that links to a summary file
which links to a raw data file...

When a user runs a macro I wan the link in the dahboard file to be
correct to do that I have the follwoing macro:

ChDir "p:\PLP"
Workbooks.Open Filename:="p:\PLP\Ongoing Summary.xls",
UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.Close

This refreshes the summary worksheet (it has links set to
automatically update)

But what it is not doing is refresshing the dashboard... which
automatically updated when it opened with the old summary data...

I tried to add the line

ActiveWorkbook.UpdateLink

but then it gave me error messages regarding protection.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Burk Martin     Answered On: Dec 03

If you get protection  errors it may be that your code is trying to alter
protected parts of the workbook. The way to deal with this is, in your code
to turn protection off, make the changes, then turn protection back on.

 
Answer #2    Answered By: Hubert Taylor     Answered On: Dec 03

So I changed the code to this....

ChDir "\\tangent1\PLP"
Workbooks.Open Filename:="\\tangent1\PLP\Ongoing Summary.xls",
UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWorkbook.Unprotect
ActiveWorkbook.UpdateLink
ActiveWorkbook.Protect

Sitll doesn't work... I get the same error  message Run time error
1004 you cannot run thios comman on a protected sheet.

Any other ideas???

 
Answer #3    Answered By: Lurlene Fischer     Answered On: Dec 03

Have you tried doing this manually while recording a macro?

 
Answer #4    Answered By: Helene Stewart     Answered On: Dec 03

Instead of:

ActiveWorkbook.UpdateLink

Try:

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

 
Answer #5    Answered By: Feodora Bonkob     Answered On: Dec 03


Yes I did in a test workbook environement... adn came up with this

ChDir "\\tangent1\PLP"
Workbooks.Open Filename:="\\tangent1\PLP\Ongoing Summary.xls",
UpdateLinks:=3
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWorkbook.Unprotect
ActiveWorkbook.UpdateLink Name:= _
"\\tangent1\PLP\Ongoing Summary.xls", Type:= _
xlExcelLinks
ActiveWorkbook.Protect

Which worked in my test workbook environment but fails in my real
environement with

 
Answer #6    Answered By: Della Simpson     Answered On: Dec 03

I m pretty sure it s the UpdateLink command. Do you know how to do a
step-by-step debug of the macro?

 
Didn't find what you were looking for? Find more on what should be an easy one Or get search suggestion and latest updates.




Tagged: