VBA problem with using relative cell offset value in change link function

[nymark]

I am trying to write a simple macro that will change the file link path/name from the active cell to the value in the cell directly to the right of it....

I tried this:
Dim new_ss As String

ActiveSheet.Calculate

new_ss = ActiveCell.Offset(0, 1).Value

ActiveWorkbook.ChangeLink Range(ActiveCell()), _
Range("new_ss"), xlExcelLinks

But its not working. I know the problem is in the change link to file name variable....

The macro works when I use the names of the two cells.... but I make it realtive to which ever cell lin the SS I am on....

I also tried without the new_ss viable and just using the activecell.offset function in the changelink command...

What am I missing?

Thanks

 

[jasonb75]

Try Range(new_ss) (entering it without the double quotes).

Or possibly Range(new_ss).Value

 

[nymark]

Try Range(new_ss) (entering it without the double quotes).

Or possibly Range(new_ss).Value

Nope.... Thanks for trying.

 

[nymark]

I dont get why this doesnt work

ActiveWorkbook.ChangeLink Range(ActiveCell()), _
Range(ActiveCell.Offset(0, 1)), xlExcelLinks

Seems so basic... there's something I'm missing with regards to the offset reference

 

[GWteB]

Are you looking for something like this ...

Public Sub Example()
    With ActiveCell
        .Hyperlinks(1).Address = .Offset(0, 1).Hyperlinks(1).Address
    End With
End Sub

 

[nymark]

Are you looking for something like this ...

Public Sub Example()
    With ActiveCell
        .Hyperlinks(1).Address = .Offset(0, 1).Hyperlinks(1).Address
    End With
End Sub

Thanks... But I just get a subscript out of range error

 

[GWteB]

B2 contains a link to the file F:\Outgoing\BookingConfirmation .pdf
C2 contains a string value
Are you trying to change the path of the link in B2 to the value (also a path) from C2?

Book1
	B	C
2	BookingConfirmation .pdf	C:\Users\GWteB\Documents\AnotherBookingConfirmation.docx
Sheet1

 

[nymark]

B2 contains a link to the file F:\Outgoing\BookingConfirmation .pdf
C2 contains a string value
Are you trying to change the path of the link in B2 to the value (also a path) from C2?

Book1
	B	C
2	BookingConfirmation .pdf	C:\Users\GWteB\Documents\AnotherBookingConfirmation.docx
Sheet1

B2 contains the text F:\Outgoing\BookingConfirmation .pdf
Which is also a link in the workbook.

C2 contains the text F:\Outgoing\BookingConfirmationNEW .pdf

while I am on B2, I want to change the link from
F:\Outgoing\BookingConfirmation .pdf
to
F:\Outgoing\BookingConfirmationNEW .pdf

I have multiple links.... Current links will be in column B, new links in C.... So when I want to update, I just want to go to a cell in column B, run the macro and have it replace it with the path/file name in the adjacent column. Thank you.

 

[GWteB]

This should work

Public Sub Example()
    With Selection
        .Hyperlinks(1).Address = .Offset(0, 1).Value
    End With
End Sub

 

[nymark]

This should work

Public Sub Example()
    With Selection
        .Hyperlinks(1).Address = .Offset(0, 1).Value
    End With
End Sub

Thanks.... but it doesnt. I'm not sure... but I believe I am not using a hyperlink.... it's just an old fashion link to pull in data from another spreadsheet