Linking formula problem

[TomF]

Hi everyone,

I just discovered all my macro's are useless because I have a linking formula problem. Let me explain the situation.

As you see I start with an xls file called Receipt Note I - MRN BP.xls. This file has a hyperlink to CMR BULK.xls.

If you look at cell F2 in Receipt Note I - MRN BP.xls I filled in the word test. In CMR BULK.xls this word is linked by using the following function:

='[Receipt Note I - MRN BP.xls]Data Invoer '!$F$2

So far so good

Now I have a Save button and when I press this the Receipt Note I - MRN BP.xls is saved as 123456.xls

Now when I click the hyperlink to CMR BULK.xls from the 123456.xls document you can see the word Test is not there. This is because it still refers to:

='[Receipt Note I - MRN BP.xls]Data Invoer '!$F$2

Does anyone know a solution for this that this code automatically adjusts to:

='[123456.xls]Data Invoer '!$F$2

Thanks in advance

 

[shippey121]

when you press the save button is the CMR bulk file open or closed, ?? you could program the macro used to save the document to open the CMR file and change the formula

 

[shippey121]

the code used to change the link information is

ActiveWorkbook.ChangeLink Name:="Book1", NewName:= _
        "C:\Documents and Settings\CSHIPPEY\My Documents\Book1.xls", Type:= _
        xlExcelLinks
    ActiveWorkbook.UpdateLink Name:= _
        "C:\Documents and Settings\CSHIPPEY\My Documents\Book1.xls", Type:= _
        xlExcelLinks

if you post you SAVE code maybe we can incorporate the above code into it, we need file locations as well,

to clarify you only have 1 CMR file but the master recippt remains unchanged and get a new file name

 

[TomF]

Shippey,

When I press the Save button the CMR BULK file is closed.

Here you have some more details:


The Receipt Note I - MRN BP.xls and the maps, Marine File and Shipments are located on the desktop.

In the Marine files map there are 10 documents. These are the documents that will open when you follow the hyperlinks in the Receipt Note I - MRN BP.xls . The example I used in my first post is CMR BULK.xls . You can see that file in this map.

Here is the picture again I posted in my first post. Notice under and next to the CMR BULK hyperlink you see the other hyperlinks which files are located in the Marine File map.

In the map Shipments the files which are saved by using the save button macro are located. You can see that the 123456.xls is listed here.

Here you have the save macro:

Sub Save()
'
' Save Macro
' Macro recorded 7/07/2006 by BPPassPort User
'
' Keyboard Shortcut: Ctrl+t
'
Dim s As String, s1 As String
s = ActiveSheet.Range("F10").Text
s1 = "C:\Documents and Settings\" & _
"fackt0\Desktop\Shipments\"
ActiveWorkbook.SaveCopyAs _
Filename:=s1 & s & ".xls"
End Sub

 

[shippey121]

as far as i can see the solution to altering the CMR bulk file link, would be to insert some code before the current code you have which would open the CMR file alter the link useing the variable S which you have then save and close the CMR file and then save you sheet,

i see that you change the name of your Receipt file every time but you only have i CMR bulk file, is this correct,

is there any more files which will need the links changing aswell or just this one ??

 

[shippey121]

the code below will open a new file change the link from one file to another the close it, you need to change the file names and directorys

try

Workbooks.Open Filename:= _
        "C:\Documents and Settings\CSHIPPEY\My Documents\test.xls", UpdateLinks:=0
    ActiveWorkbook.ChangeLink Name:= _
        "C:\Documents and Settings\CSHIPPEY\My Documents\importlabels.xls", NewName:= _
        "C:\Documents and Settings\CSHIPPEY\My Documents\stockadjust test.xls", Type _
        :=xlExcelLinks
        Workbooks("test.xls").Close

HTH

 

[TomF]

i see that you change the name of your Receipt file every time but you only have i CMR bulk file, is this correct,

is there any more files which will need the links changing aswell or just this one ??

I have only one CMR BULK file.

There are more files which will need the links changing. All those files are located in this map which is on the desktop.

I tried using the macro you posted but it doesn't seem to do the trick. Then again I ain't so good at using macro's