CODEWITHSUNDEEP
excelvba
HM Production
HM Production

Reputation: 37

Using VBA to save excel file and hyperlink the saved file

Just wanted to ask is it possible to save a file in excel using VBA then generate a hyperlink of that file path to be pasted on a different worksheet?

For example, I have a workbook that have two worksheets, Form and Logs. In the Form worksheet, there is a cell that contain a document number. When I run my VBA code, it will copy the document number to the Logs worksheet.

Private Sub CommandButton1_Click()

 Application.ScreenUpdating = False
 Dim copySheet As Worksheet
 Dim pasteSheet As Worksheet

 Set copySheet = Worksheets("Form")
 Set pasteSheet = Worksheets("Logs")

 copySheet.Range("I3").Copy
 pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 copySheet.ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:="C:\Users\username\Documents\Saved PDF.pdf"

End sub

It will also export the Form worksheet as a pdf file. However, I want a hyperlink of that saved pdf to be placed into the Logs worksheet. Let's say the Doc Number is in Column A, I want the hyperlink to be in column B in the same row as the Doc Number.

Upvotes: 0

Views: 856

Answers (1)

AsUsual
AsUsual

Reputation: 524

Try this..

Sub log()
    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("Form")
    Set src = srcSh.[I3]
    Set dstSh = wb.Worksheets("Logs")
    Set dst = dstSh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 2)

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

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

    dst(1) = src: dstSh.Hyperlinks.Add dst(2), pth, , "Open PDF", "Log"

    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Related Questions