VBA(?) to keep links to charts embeded in word relative after saving on another machine

Hi All

I am a newbie to VBA, learning as i go and still very much a beginner.

I am trying to create template word and excel files, housed in the same folder. The word needs to have charts and tables embedded from the excel. This I can do easily. The problem is the moment the template is copied to another machine or destination, the links (being absolute) break.

Some background info: The word doc is a Report for each client, and therefore there will be numerous "copies" made - one for each client. The excel holds all the statistics for each client. The report is run 4 times a year. The end user needs the process to be as simple as opening the word report and then printing it with all the charts pulled through "automatically". They are not competent to re-establish the links themselves. New clients come on board constantly, hence the need for a template. The folder also needs to be shared, but not housed on the cloud or a server. Hope that makes sense.

Is there a way to enter a wildcard into the links, either in word/excel or writing some VBA? I may, of course, just be missing something obvious.

Thanks

Hoplo

Hi Hoplo,

I did similar kind of project in the past.

I was working on a project where powerpoint presentations need to be prepared for 123 dealers. All the data of the dealer was present in the Excel sheet. We need to automatically prepare presentations for these 123 dealers based on the data present in the Excel sheet.

So I created one Powerpoint template and placed it at one particular location. Then I created a VBA macro which used to prepare chart and separate the data of each dealer which result in creating 123 new Excel workbook from the main workbook, one for each dealer in one particular location.

Once these 123 Workbooks were prepared, then i created another macro which used to open, update and link the Powerpoint template with newly created workbooks, one at a time. So, this macro used to open powerpoint presentation then update the values of the chart and data in the presentation. And, after making the updation, macro used to break the link and save the presentation. So, at the end, macro used to give me 123 presentation for each dealer. This macro used to take 2 hours to complete.

Once these presentations were prepared, we created another macro which used to send automatic mail to different dealers.

I hope you may get some idea from the above. You can share the template and the macro which you have created if you require any further help.


Regards
Ramandeep Singh

Please Click STAR to Add Reputation if my answer helped!

Thanks Ramandeep. I had considered something similar to that. It is possibly beyond my capabilities at the moment to create a macro like that from scratch though, so will see if I can find some source code.

Hi Hoplo,

This forum also provide commerical services, where you can get help from Experts, who can automate this work at some charges. You may go for the commerical services.

Regards
Ramandeep Singh

Please Click STAR to Add Reputation if my answer helped!