help with string length limitation workaround

[kristopher.dawsey]

Hello,

I have a lot of Excel carts which contain absolute references to other
workbooks. I am trying to write a VBA macro that makes these
references relative, so that I can move the workbooks to other folders
and the links will update appropriately. My problem is that I cannot
pass a string greater than 255 characters as the new formula for the
series. Below is the code I'm using, which returns error 1004 from
Excel. Any help greatly appreciated.

Kris

Private Sub Workbook_Open()
Dim oChart As Chart
Dim sSeries As Series
Dim sFormula As String
Dim sPath As String

sPath = Replace(ThisWorkbook.Path, "\Formatting", "")
For Each oChart In Charts
For Each sSeries In oChart.SeriesCollection
sSeries.Formula = Replace
sSeries.Formula, "F:\USERS\ENERANLS\GROUP\Forecasting Program", "")
Next sSeries
Next oChart
End Sub

 

[Tom Ogilvy]

Look at the linksources method in Excel VBE help.

 

[kristopher.dawsey]

I checked out linksources as you suggested, but I'm not sure that I
understand how that's helpful. Can you explain further?

 

[Tom Ogilvy]

Manually, you can go into Edit=>Links select your link, and change the
source. This changes all references in your workbook.

So linksources is the VBA equivalent. that is the way you should be
changing links when a workbook is moved.