CODEWITHSUNDEEP
vbaexcel
user7004
user7004

Reputation: 187

Change the source of a link

I have a sheet where the last column is always linked to a link. I need to write a code that change the source of this link, where the address is the name of the file ("Caixa das empresas") + the date of yesterday. I tried to write this code, but for some reason its not working.

Sub delete_formulas()

Dim LastCol As Integer
Dim DATstr As String
With Worksheets("Calculo")
    LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
Columns(LastCol - 1).Copy
Columns(LastCol - 1).PasteSpecial Paste:=xlPasteValues
End With

ActiveSheet.Calculate
DATstr = Format((Date - 1), "mm-dd-yy")
Columns(LastCol).Select
ChDir "T:\Gerencial\Mapa\Tesouraria\Histórico - Caixa das Empresas"


 ActiveWorkbook.ChangeLink Name:= _
    "T:\Gerencial\Mapa\Tesouraria\Histórico - Caixa das Empresas\Caixa das empresas.xlsx & DATstr"

End Sub

Upvotes: 0

Views: 548

Answers (3)

skkakkar
skkakkar

Reputation: 2828

I suggest you to suitably adopt to your needs one program version which works as per your requirements. I could get time to just make raw code to meet your requirements.The following code iterates through the Excel links in a workbook and changes links from the test2.xls file to test4 - 09-14-15.xlsx. Snapshot of the sample file is given below.

changelink sample file

I have also uploaded sample file test.xlsm with sample data. You can refer here

Programming approach is different. Try this code :

Sub Chnlink()
    Dim link, linkSources, newLink As String
    Dim DATstr As String
    DATstr = Format((Date - 1), "mm-dd-yy")
    newLink = ThisWorkbook.Path & "\test4 - " & DATstr & ".xlsx"
    linkSources = ThisWorkbook.linkSources(xlLinkTypeExcelLinks)
    If IsArray(linkSources) Then
      For Each link In linkSources
         If InStr(link, "test2.xls") Then _
          ThisWorkbook.ChangeLink link, newLink, xlLinkTypeExcelLinks
      Next
    End If
End Sub

Upvotes: 0

Amine Teffal
Amine Teffal

Reputation: 20

The changeLink function needs two arguments : Name and NewName.

Upvotes: 0

user4039065
user4039065

Reputation:

You are including the DATstr inside the quoted string. It needs to be appended to the right end or stuffed into the middle.

ActiveWorkbook.ChangeLink Name:= _
    "T:\Gerencial\Mapa\Tesouraria\Histórico - Caixa das Empresas\Caixa das empresas" & DATstr & ".xlsx"

Upvotes: 2

Related Questions