Solved

Change PowerPoint links to Excel workbooks with VBA

Posted on 2009-06-29
4
741 Views
Last Modified: 2012-05-07
I have a presentation with a number of links to Excel workbooks/sheets. I can get to these through Edit... Links... and change them one at a time. I need to change the location of the workbooks, so I'd like to do them all at once programmatically. The tricky part here is that the filenames contain ranges, not just an Excel workbook name.

I tried replacing just the directory spec using something like this:
   .LinkFormat.SourceFullName = newPath & oldXLFN

I get an error message that says I can't do this because the filename is already open. I'm supposing that when I open the PPT presentation, that the links are then opened at the same time.

Is there some easy way to make this directory change in VBA? Each presentation has about 15 links and we have a number of presentations to update.

Thanks in advance for the help.
Tom
0
Comment
Question by:thomasedooley
4 Comments
 

Accepted Solution

by:
thomasedooley earned 0 total points
ID: 24745088
I think I found the problem. All day yesterday I was trying this, and getting the message that said "you already have a copy of this file open" as I stated above.

When shutting down my machine before going home, I got a spurious "do you want to save the changes made to thisfile.xls" from Excel (which was the linked Excel file I'm using in the PPT).

I tried this again this morning, and lo and behold, the code works (.linkformat.sourcefullname).

We can close this as solved by me, or delete it.

I appreciate the work put in by any experts on this problem, and would happily give points.

thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
PowerPoint is the go-to presentation software for millions of users around the world. Many presentations use basic text features but you can really make special text jump out of your slide by applying this bubble text design process. This article ha…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now