Hyperlink invoice to customer at time of printing

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,904
Office Version
  1. 2007
Platform
  1. Windows
Morning,

I have a worksheet called DATABASE where all my customers info is stored.
In the same workbook i have a worksheet called INV where i select the customer from a drop down list & it then gathers all the info to populate the fields.

I then print off an invoice & the copy is saved in a PDF format within a folder on my pc.
I then go to my worksheet called DATABASE & in column P for the customer in question will be the invoice number.
With this cell selected i run the code below which will then hyperlink it to the PDF file.

To save time as it can be long winded if i have mutiple to do i would like the hyperlink piece to happen when i print the invoice.
The code supplied works fine but you manually need to go select the cell etc etc.
So it should work like so,

When i print the invoice look at customers name on INV worksheet.
Look for a match in column A on the DATABASE worksheet.
Hyperlink the value in that cell row at column P with the matched PDF file in the given path.



VBA Code:
Sub hyperlink()

        Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\"
    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
        End If
    Else
        MsgBox "Please Select An Invoice Number.", vbExclamation, "Hyperlinking The Invoice Number"
    End If
End Sub
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,904
Office Version
  1. 2007
Platform
  1. Windows
I believe i have all the info required to do this but need some help putting these 2 codes together.



This is the code which is in use.
The hyperlink part above can run after i click OK to the msgbox

"Remember the hyperlink code above only works IF I select the cell"
"I need the code added which will look at customers names on worksheet INV at cell G13"
"Now look in column A of worksheet DATABASE for a match,once the match is found hyperlink the value in column P with the PDF that was just saved"
"path shown in above code"

Have a nice day


VBA Code:
Private Sub Print_Invoice(n As Long)
  Dim strFileName As String
  If Range("N18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "Payment Type Not Selected"
    Exit Sub
  End If
  ActiveSheet.PrintOut Copies:=n
  Unload InvoicePrintForm
  MsgBox "ONCE PRINTED PLEASE CLICK OK BUTTON" & vbNewLine & vbNewLine & "TO SAVE INVOICE & CLEAR CURRENT INFO", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
    Exit Sub
  End If
  With ActiveSheet
    .PageSetup.PrintArea = "$G$3:$O$61"
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
    Range("G27:N36").ClearContents
    Range("G46:G48").ClearContents
    Range("G47:I51").ClearContents
    Range("N18").ClearContents
    Range("N4").Value = Range("N4").Value + 1
    Range("G13").ClearContents
    Range("G13").Select
    ActiveWorkbook.Save
  End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,961
Messages
5,621,844
Members
415,861
Latest member
Leetor72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top