File location refence disappearing

[rrenis]

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\

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

Many Thanks,
rrenis

 

[hatman]

Why not do this instead?

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]

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?

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

cheers,
rrenis

 

[hatman]

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:

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]

Thanks hatman

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

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

cheers,
rrenis

 

[rrenis]

Hi hatman 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

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]

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]

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

cheers,
rrenis

 

[hatman]

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.

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:

                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]

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

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.

cheers,
rrenis