Attach a .PDF file to Excel

**simonwilliams** · 12-15-2009, 08:42 AM

My Excel (Office 2007) has a column that is devoted to job numbers.

I create my job sheets as .PDF files & save them with job numbers.

Is there a way where I can click on the job number in Excel & have this open up the relevent .PDF file?

Any help or pointers appreciated & please bear in mind my knowledge of Excel is limited. Thanks

**davesexcel** · 12-15-2009, 08:55 AM

Hit Ctrl & K to insert a hyperLink
Find the File you want to HyperLink to.

Check this out to remove HyperLink warnings, maybe there are no warnings in xl'07
http://support.microsoft.com/kb/829072

**simonwilliams** · 12-15-2009, 09:56 AM

Thanks for that, yes it did what I wanted.

But is there a way to make it less long winded?

There is a lot of navigating to get to the correct folder, the path is
\\Nasbox\Leaflets\Work Sheets\602.pdf

And the Insert Hyperlink dialogue box doesn't allow for date modified (which would make it quicker to get to relevant file) or a last folder used.

I have all the .PDF files I want linked to in one folder & they are all numbered 601, 602, 603 etc. So is there a way that if I type in 603 in a particular column in Excel it will automatically link to the 603.PDF file?

Bet there isn't!!! ???

**martindwilson** · 12-15-2009, 10:23 AM

=HYPERLINK("C:\Documents and Settings\Martin Wilson\My Documents\"&B1&".pdf")
where b1 contains doc number

**simonwilliams** · 12-15-2009, 11:57 AM

Thanks for that but can you elaborate as at the mo I'm not really sure what to do with this?

=HYPERLINK("C:\Documents and Settings\Martin Wilson\My Documents\"&B1&".pdf")

I have changed it to my files location
=HYPERLINK("\\Nasbox\Leaflets\Work Sheets\"&600&".pdf")

I right clicked on the cell, selected hyperlink, pasted the above, changed the B1 to 600 but keep getting a cannot open the specified file.

If it was working correctly wouldn't I have to still paste all of that into each cell I wanted the link to work on & then manually adjust each B1 to the number I want?

Confused???

**martindwilson** · 12-15-2009, 06:01 PM

i have no idea of your actual path is
get the properties of doc by right clicking and use that
you do not use this by selecting hyperlink
just put
=HYPERLINK("\\Nasbox\Leaflets\Work Sheets\"&600&".PDF")
in the cell just as if you were putting =b1
i think you are linking to a shared drive so you need to link whatever the drive is
=HYPERLINK("g:\Nasbox\Leaflets\Work Sheets\"&600&".PDF")
yes you would have to change b1 but you could put a function in b1 to change it to what you need

**davesexcel** · 12-15-2009, 08:58 PM

Attached is a sample of getting a list of PDF files and hyperlinking to them.
Enter the Name of the Folder in A2 and hit enter, in your case enter the name Work Sheets, and then hit enter.
There is a Worksheet_Change event that kicks in the code to get the .pdf files from the named folder, and then creates a HyperLink to those files.

Make this simple at first, in you C:\ drive make a new folder and Name it "Work Sheets", throw some .pdf files into that folder.

Open the workbook, enable macros, I believe in xl'07 you need to do something in the tools menu to enable macros in a workbook.

Enter Work Sheets in A2 and hit enter.

The code should then produce a list of Hyperlinks to your .pdf files from that folder.

**simonwilliams** · 12-16-2009, 08:17 AM

Hi Dave

That looked fantastic, unfortunately it didn't work?

I downloaded the GetPDFs.xls & saved it to my trusted folder where my main excel spreadsheet reside, full of macros & all enabled.

On my C; drive I created a folder called "Work Sheets" & put 4 .pdf files in it.

In Cell A2 I tried entering both "Work Sheets" & "C:\Work Sheets"

Upon hitting Enter nothing happened?

Under the Developer Tab - Macro Security - Enable All Macros (was checked)

Macros - Macro Name - ListPics - Run (nothing happened?)

Any idea why it isn't running?

**davesexcel** · 12-16-2009, 08:50 AM

Try changing this .

 .LookIn = "C:\Work Sheets\" 'change this

Run the macro GetPics
Check the spelling of the folder.

**simonwilliams** · 12-16-2009, 09:29 AM

Unfortunately that didn't make any difference. Clicking in or hitting enter over A2 produces nothing?
No error messages, just nowt.

Am running Win 7, x64 by the way.
My Work Sheets folder is "C:\Work Sheets"

If this was working correctly I assume that all the .pdf files would show up in Cell A4 as hyperlinks?

My macro now reads as below. Any other ideas?


Sub ListPics()

    Dim lCount As Long, ws1 As Worksheet, f As Range, rng As Range

    Set ws1 = Worksheets("Find")
    Set f = ws1.Range("A2")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error Resume Next
    Range("A5:A65536").Clear

    With Application.FileSearch

        .NewSearch
        .LookIn = "C:\Work Sheets\" 'change this
        .Filename = ".pdf"

        If .Execute > 0 Then

            For lCount = 1 To .FoundFiles.Count
                If InStr(.FoundFiles(lCount), f) > 0 Then
                    ActiveSheet.Hyperlinks.Add Anchor:=Range("A65536").End(xlUp).Offset(1, 0), Address:=.FoundFiles(lCount), TextToDisplay:=Replace(.FoundFiles(lCount), "M:\CA\jobs\", "")

                End If
            Next lCount

        End If

    End With
 
    rng.Font.Size = 28
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub

**martindwilson** · 12-16-2009, 10:15 AM

i still maintain =hyperlink () works since it will do exactly the same.
with the names of the pdfs 100,101,103,.... listed in col a starting a1 then in b1
=HYPERLINK("g:\Nasbox\Leaflets\Work Sheets\"&a1&".PDF") dragged down will work.
the key is to get the right path. right vclick a pdf doc select proerties and copy the exact path given in location into the formula.ive just done it on my networked drive fine
you dont use the path as you have but the mapped path.

**simonwilliams** · 12-16-2009, 10:19 AM

Ta Martin, I didn't get back to you earlier as I didn't follow what you meant.

Still not that clear on what you mean, could you explain it to an idiot please?

The path to where I store the .pdfs is "\\NASBOX\Leaflets\Work Sheets"

???

**martindwilson** · 12-16-2009, 11:07 AM

that might be the path but you must have mapped it to a drive letter. what does it say in the document properties

**simonwilliams** · 12-16-2009, 01:34 PM

No
"\\NASBOX\Leaflets\Work Sheets"
Is the exact path, its a seperate Server connected via ethernet.

Don't like mapping network drives, they frequantly don 't reconnect.

**simonwilliams** · 12-17-2009, 05:54 AM

Martin, please can you elaborate on your hyperlink method as its not working for me?

I have selected the Cell A85, right clicked & selected Hyperlink.
In the address bar I have pasted
=HYPERLINK("c:\Work Sheets\"&a1&".PDF")
& click OK

The text that then appears as a hyperlink is C:\Work Sheets\Add Highlight.PDF

Now when I click this in the spreadsheet I get a cannot open the specified file?

If I type over the hyperlink, the number of one of the work sheets, say 557 I get the same error message.

More help please???

**simonwilliams** · 12-17-2009, 06:22 AM

I have attached a sample of the worksheet which may help with advice?

Column A is the one where the jobs are numbered & where the hyperlink should refer to the individual .PDF file stored at
\\Nasbox\Leaflets\Work Sheets
which is a server.

Thanks

**martindwilson** · 12-16-2009, 02:41 PM

Don't like mapping network drives, they frequantly don 't reconnect

can't say thats what happens in my experience! but i suspect the syntax for connecting to a network path is different but i am unable to test that

**davesexcel** · 12-16-2009, 08:00 PM

I just read another reply, it appears

Application.Filesearch

Is no longer available in xl'07, check out RoyUk's blog on an alternative.

http://excel2007tips.blogspot.com/20...h-in-2007.html

**simonwilliams** · 12-17-2009, 05:19 AM

Thanks Dave, followed the link & copied the code

--------------------------------------------------------------------------------------' Module : Module1
' DateTime : 09/05/2007 08:43
' Author : Roy Cox (royUK)' Website : www.excel-it.com for more examples and Excel Consulting
' Purpose : Open all worksheets in a specific folder' Disclaimer; This code is offered as is with no guarantees. You may use it in your' projects but please leave this header intact.
'---------------------------------------------------------------------------------------
Option Explicit
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String

sPath = "C:\Work Sheets\"
'location of files
ChDir sPath
sFil = Dir("*.xlsx") 'change or add formats
Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

' do something
oWbk.Close True 'close the workbook, saving changes
sFil = Dir
Loop ' End of LOOP
End Sub

But every time I clicked within Excel it just opened up the Visual Basic Window & then came up with a compile error.
Too be honest not really sure what this bit of code is meant to do, how I run it or how it creates & displays my hyper-linked .pdfs?

Attach a .PDF file to Excel

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Re: Attach a .PDF file to Excel

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions