ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- 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.
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