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

nymark

New Member
Joined
Apr 19, 2020
Messages
9
Office Version
  1. 2010
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
Try Range(new_ss) (entering it without the double quotes).

Or possibly Range(new_ss).Value
 

nymark

New Member
Joined
Apr 19, 2020
Messages
9
Office Version
  1. 2010
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Are you looking for something like this ...
VBA Code:
Public Sub Example()
    With ActiveCell
        .Hyperlinks(1).Address = .Offset(0, 1).Hyperlinks(1).Address
    End With
End Sub
 

nymark

New Member
Joined
Apr 19, 2020
Messages
9
Office Version
  1. 2010
Are you looking for something like this ...
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
BC
2BookingConfirmation .pdfC:\Users\GWteB\Documents\AnotherBookingConfirmation.docx
Sheet1
 

nymark

New Member
Joined
Apr 19, 2020
Messages
9
Office Version
  1. 2010
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
BC
2BookingConfirmation .pdfC:\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

Well-known Member
Joined
Sep 10, 2010
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows
This should work
VBA Code:
Public Sub Example()
    With Selection
        .Hyperlinks(1).Address = .Offset(0, 1).Value
    End With
End Sub
 

nymark

New Member
Joined
Apr 19, 2020
Messages
9
Office Version
  1. 2010
This should work
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,035
Members
410,721
Latest member
adi772
Top