File location refence disappearing

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi - I've got the following in a Sub that opens Summary.xls, copies in a row that contains a reference to template_cost_breakdown and replaces this refence with myFile so that a link to myfile is created. Unfortunately when the Summary.xls sheet has been opened with template_cost_breakdown.xls the E:\Summary\Costs\ part of the refence is lost.

This doesn't happen when running the VBA code as I rename template_cost_breakdown.xls prior to opening the Summary.xls - it's just if they happen to be opened at the same time for any reason. :(

Does anyone know if you can stop excel from altering the link to template_cost_breakdown .xls in the Summary spreadsheet if they are both open? Failing that could anyone help with suggesting a way of using something like If E:\Summary\Costs\ exists then run the following code else run the code excluding the reference to E:\Summary\Costs\ :confused:

Code:
Selection.Replace What:="E:\Summary\Costs\[template_cost_breakdown.xls]", Replacement :=myFile, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Many Thanks,
rrenis :)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Why not do this instead?

Code:
ActiveWorkbook.ChangeLink Name:="E:\Summary\Costs\template_cost_breakdown.xls", NewName:= myfile, Type:= xlExcelLinks

It shouldn't matter whether the file is open or not... BUT the sheet name in the original file must exist in the new file, otherwise you will get a prompt to select which sheet to link to.
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
thanks hatman - but wouldn't that still not change the link if the link has changed from

E:\Summary\Costs\[template_cost_breakdown.xls]
to
[template_cost_breakdown.xls]

because the template_cost_breakdown.xls spreadsheet had been accidentally opened whilst the Summary.xls sheet is open? :confused:

Can't test it as I'm not at work at the moment :p

cheers,
rrenis
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
No.

This method replicates the use of the Edit->Links tool, which ALWAYS displays the full pathname whether the source file is open or not. That is because the .Linksources collection of a Workbook object contains the full pathname for each link, though Excel likes to display only the Filename in the cells when the linked file is actually open.

Besides, I tested this before I stated
It shouldn't matter whether the file is open or not
:wink:

If for some reason I'm wrong, and this behaves differently on your machine, you can always get a bigger rock and do this instead:

Code:
tmp = ""
for each item in thisworkbook.linksources
    if instr(1,item,"template_cost_breakdown.xls") > 0 then
    tmp = item
    exit for
next item

if tmp <> "" then
    ActiveWorkbook.ChangeLink Name: = tmp, NewName:= myfile, Type:= xlExcelLinks
end if

Let me know how you make out
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120

ADVERTISEMENT

Thanks hatman :cool:

I'll change my code tomorrow from what you've said ChangeLink it's just what I'm after! :biggrin:

Thanks for giving up your time - I really appreciate it. I'll post back and let you know how it goes.

cheers,
rrenis :)
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi hatman :biggrin: I've just got chance to try the changelink code and I get 'run-time error 1004: cannot link to file'. The sheet is unprotected too so it's not that :confused:

btw will the changelink command change all references to the template as I need the hidden row (I'm using this to copy a new row and then change the link on the new row to myFile) to keep it's link to the template.

cheers,
rrenis :)
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

Second question first: the Changelink method will change all links to the specified file to link to a different file. Links to OTHER files will remain unchanged, but if you only want to change SOME links to the specified file, this is not the a solution that will be workable.

So before I do any research into the error, let me know one way or the other whether this solution will be viable. If not, hope is not lost, we just need dissect the text strings of the formulas instead... tedious, but do-able.

So let know, and I;ll try to play with this tomorrow.
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi hatman - I did a bit of googling into changelink since my last post as I've never used it before and like you say I don't think this is a viable solution as it will change my 'master link' that I use to copy in a row and update the link to the template in the new row to myFile.

Thanks for your time posting back I apprecaite it :cool:

cheers,
rrenis :)
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
No problem.

Of the top of my head, not sure what caused the "Cannot Link To File Error"... but it doesn't really matter at this point.

This code should fix you up in fine style by performing a strict text replacement on ONLY the fomrulas in the current selection, regardless of whether the file reference has the full path or not.

Code:
Sub link_replace()

    Dim Item As Range
    Dim File_Strt As Long
    Dim Old_File As String
    Dim Strt
    Dim item2
    Dim Strt_Num As Long
    Dim Op_Num As Long
    Dim myfile As String
    Dim Actual_Old_File As String
    Dim bang As Long
    
    Strt = Array("=", "-", "+", "*", "/", "<", ">", "(", ",", "^", "&")
    
    myfile = "c:\temp\[book2.xls]"
    
    Actual_Old_File = ""
    
    Old_File = "[template_cost_breakdown.xls]"
    
    For Each Item In Selection
    
        File_Strt = InStr(1, Item.Formula, Old_File, vbTextCompare)
        
        If File_Strt > 0 Then
        
            If Actual_Old_File = "" Then
        
                Strt_Num = 0
            
                For Each item2 In Strt
                
                    Op_Num = InStrRev(Item.Formula, item2, File_Strt)
                    
                    If Op_Num > Strt_Num Then
                    
                        Strt_Num = Op_Num
                        
                    End If
                
                Next
                
                Actual_Old_File = Mid(Item.Formula, Strt_Num + 1, File_Strt - Strt_Num + Len(Old_File) - 1)
                
                bang = InStr(1, Item.Formula, "!")
                
                If Mid(Item.Formula, bang - 1, 1) = "'" And Left(myfile, 1) <> "'" Then
                
                    myfile = "'" & myfile
                    
                End If
                
            End If
            
            Item.Formula = Replace(Item.Formula, Actual_Old_File, myfile, , , vbTextCompare)
            
        End If
    
    Next Item

End Sub

note this code:
Code:
                If Mid(Item.Formula, bang - 1, 1) = "'" And Left(myfile, 1) <> "'" Then
                
                    myfile = "'" & myfile
                    
                End If

in the above code. Without it, I ended up with a Runtime Error, though it was an Object Error. Reason was that I was trying to map in a formula with an error in it... the Old_File I was testing with had the path contained in single quotes, but myfile did NOT... so it ended up with mismatched quotes. Hence I added in the snippet above to check and see if there is a closing single quote, and add it to the beginning of myfile if it is absent. So it is BEST if you omit an open quote in myfile in all circumstances, and let the code add it if needed... otherwise, if you include it but it is not needed, you will get a runtime error.

Lastly: I think I captured all of the possible operators/characters that could immediately preceed a link... if you come across something that I missed, simply add it to the array.

Let me know how you make out :)
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
:eek: :eek: :eek: :eek:

Thanks hatman :cool: - can't believe you went to so much trouble!! Unfortunately I get an error in the debugger with

Code:
Item.Formula = Replace(Item.Formula, Actual_Old_File, myfile, , , vbTextCompare)

I've placed it in my existing code after I unhide row 6 (the row containing the template link) and copy it down to row 7 - as this is the row I'm wanting to change the refences on. I really appreciate your time on this but I'm happy to live with the restrictions of my original code if you've got no more time to spare. :cool:

cheers,
rrenis :)
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top