Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with string length limitation workaround
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with string length limitation workaround
Look at the linksources method in Excel VBE help.
-- Regards, Tom Ogilvy " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with string length limitation workaround
I checked out linksources as you suggested, but I'm not sure that I
understand how that's helpful. Can you explain further? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with string length limitation workaround
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. -- Regards, Tom Ogilvy wrote in message oups.com... I checked out linksources as you suggested, but I'm not sure that I understand how that's helpful. Can you explain further? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workaround for HYPERLINK argument length limit | Excel Worksheet Functions | |||
Formula Req'd - Autofilter limitation workaround | Excel Worksheet Functions | |||
Length limitation of cell formulas | Excel Programming | |||
Length limitation of cell formulas | Excel Programming | |||
Need Workaround for Cell Display Limitation in Excel 2000 | Excel Discussion (Misc queries) |