Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem add hyperlink to another sheet after save as pdf

0

hello  

I try  finding  way  to   add  hyperlink  in sheet  data  after  save  the  file  as  pdf   what  i  want   after   save  file   then  create  hyperlink  in  column a   in sheet  data  based on cells  value  I1   in sheet "brand"   

so  far   this  is   what  I  got 

Sub createhyperlink()
    Application.ScreenUpdating = False

    Dim wb As Workbook, srcSh As Worksheet, dstSh As Worksheet
    Dim src As Range, dst As Range, pth$, docNm$

    Set wb = ThisWorkbook
    Set srcSh = wb.Worksheets("brand")
    Set src = srcSh.[I1]
    Set dstSh = wb.Worksheets("data")
    Set dst = dstSh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

    docNm = src & " - " & src
    pth = Environ("userprofile") & "\Documents\" & docNm & ".pdf"

    srcSh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pth

    dst(1) = src: dstSh.Hyperlinks.Add dst(2), pth, , src
    Application.ScreenUpdating = True
End Sub
 
Answer
Discuss

Answers

0
Selected Answer

I have modified your code to work. Here it is.

Sub CreateHyperlink()
    ' 176

    Dim Wb          As Workbook             ' Active workbook
    Dim srcSh       As Worksheet            ' "Brand"
    Dim dstSh       As Worksheet            ' "Data"
    Dim Pth         As String               ' folder address (end on "\")
    Dim Fn          As String               ' file name
    Dim Ffn         As String               ' full file name
    Dim Src         As Range                ' hyperlink address
    Dim Dst         As Range                ' location of the hyperlink (Anchor)

    Set Wb = ThisWorkbook
    Set srcSh = Wb.Worksheets("brand")
    Set dstSh = Wb.Worksheets("data")
    Set Src = srcSh.Cells(1, "I")           ' must contain a valid URL
    Set Dst = dstSh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Pth = Environ("userprofile") & "\Documents\"
    Fn = "My File Name" & ".pdf"
    Ffn = Pth & Fn

    Application.ScreenUpdating = False
    srcSh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Ffn
    dstSh.Hyperlinks.Add Anchor:=Dst, _
                         Address:=Src.Value, _
                         TextToDisplay:="My Brand"
    Application.ScreenUpdating = True
End Sub

There is some confusion about what might be in Cell I1. It should be an Internet address which makes it not very suitable as a file name, as your code would have it.  The hyperlink also provides for a difference between the linked address and the text displayed in the cell with the link. I have invented a file name and a display name and generally continued your practise of  building the code very methodically. With the further assistance of my comments you should be able to make the final adjustments.

Discuss

Discussion

@Variatus   many  thanks  for  this  code  ,  but   I  would   when  save  based on  i1   it  supposes  invoice  number    every  time   i  save   it  changes  the  number  so   it will be   asd123 ,asd124,asd125 .... and  so  on     so  it  supposes   hyperlinking  based  on  I1   I  try   modifing   some  lines but  it  gives  me  error
   Set Src = srcSh.Cells(1, 9)           ' must contain a valid URL

TextToDisplay:=SRC
speed (rep: 40) Feb 18, '21 at 4:23 am
Speed

You declared  src As Range (but need a string for the hyperlink text) so I think you need to change your code to read:
TextToDisplay:=SRC.Value
John_Ru (rep: 6102) Feb 18, '21 at 4:44 am
@john    thanks   now  it 's  fixed  , but   there  shows  a problem  when  i  open  the  file  after hyperlinked  in sheet  "data"  it  doesn't  open  the  file  because  doesn't  save  in  directory   and  gives  me  message  you  can't  open  this  file   , do  you  have  any  idea , please ?
speed (rep: 40) Feb 18, '21 at 5:07 am
@Variatus    i  note  your  code  doesn't    save  the  file  to  \documents\    just   save  and  hyperlink  in  sheet  data   and   it  doesn't  open  the  file  because  it  doesn't  save  to  directory      ,may  you  fix  it  ,please ?
speed (rep: 40) Feb 18, '21 at 5:12 am
@Speed

If you want to open the pdf file from the new hyperlink in the "data" sheet, change the hyperlink to the file path/name (in bold below):
dstSh.Hyperlinks.Add Anchor:=Dst, _
                         Address:=Ffn, _
                         TextToDisplay:=Src.Value
John_Ru (rep: 6102) Feb 18, '21 at 5:59 am
@ john well don  !!  for  you  mod  the  code    thank  you  and  of course  @variatus  for  this  a great  code
speed (rep: 40) Feb 18, '21 at 6:50 am
Please always state which kind of error and on which line of code it occurred. I think you are right in assigning TextToDisplay:= Src.Value. So, what is the Address then? I think it's Ffn. If you assign Ffn to the Address property the hyperlink will open the file.
Variatus (rep: 4889) Feb 18, '21 at 8:41 pm
@Variatus- agreed; that's what I suggested above. 
John_Ru (rep: 6102) Feb 19, '21 at 1:33 am
@Variatus, apologies.
John_Ru (rep: 6102) Feb 19, '21 at 3:50 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login