Hi Dave,
It turns out I Ultimately had a typo. I was able to figure it out and get
it all to work.
Thanks for your help!!
"Dave Peterson" wrote:
> I would have guessed that changing the extension to .doc would be sufficient,
> too.
>
> I'm betting that there's a difference in the name of the file (small typo??)
> that breaks it.
>
> But that's just a guess. You didn't actually say that what broke--adding the
> hyperlink or clicking on it????
>
> And if I'm guessing wrong, what's the name of the .doc file? And what are the
> values in those cells when you run the code?
>
> A line like:
> msgbox "***" & myfilename & "***"
> may be useful to help find the problem.
>
> Tommy Boy wrote:
> >
> > Hi Dave,
> >
> > Thanks! I was able to get the macro below to work. I don't understand how,
> > or why it works, (wish I did) but it works. What's interesting though is
> > that I can't get it to find other file types by changing the file extension
> > in the code. I thought I could just change ".pdf" to ".doc" and hyperlink to
> > a doc file in the same directory. Is there a simple reason this doesn't work
> > this way?
> >
> > Anyway, thanks again!!
> >
> > Sub TestHyperlink()
> >
> > ActiveCell.Select
> > Dim TestStr As String
> > Dim myFileName As String
> > myFileName = "C:\Folders\Crit Scans\" & ActiveCell.Offset(0, 2).Value _
> > & " - " & ActiveCell.Value & ".pdf"
> > TestStr = ""
> > On Error Resume Next
> > TestStr = Dir(myFileName)
> > On Error GoTo 0
> > If TestStr = "" Then
> > MsgBox "No Matching File To Link"
> > Else
> > ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
> > "C:\Folders\Crit Scans\" & ActiveCell.Offset(0, 2).Value _
> > & " - " & ActiveCell.Value & ".pdf", TextToDisplay:= _
> > ActiveCell.Value
> > ActiveCell.Select
> > With Selection.Font
> > .Name = "Arial"
> > .Size = 12
> > End With
> > ActiveCell.Offset(1, 0).Range("A1").Select
> > End If
> > End Sub
> >
> > "Dave Peterson" wrote:
> >
> > > I'd include the path in the test and hyperlink.
> > >
> > > Dim TestStr as string
> > > dim myFileName as string
> > >
> > > myfilename = "C:\somepath\crit scans\" & activecell.offset(0,2).value _
> > > & " - " & activcell.value & ".pdf"
> > >
> > > teststr = ""
> > > on error resume next
> > > teststr = dir(myfilename)
> > > on error goto 0
> > >
> > > if teststr = "" then
> > > msgbox "not a valid filename"
> > > else
> > > 'do the hyperlink
> > > end if
> > >
> > > This won't help if the file is renamed/deleted after the hyperlink has been
> > > created, though.
> > >
> > > Tommy Boy wrote:
> > > >
> > > > I have the following simple code to create a hyperlink in the existing cell
> > > > that links to a PDF file on my drive. The file name it uses is based on the
> > > > text shown in 2 different cells with " - " in between the two text strings.
> > > >
> > > > ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
> > > > "Crit%20Scans/" & ActiveCell.Offset(0, 2).Value & " - " &
> > > > ActiveCell.Value & ".pdf", TextToDisplay:= _
> > > > ActiveCell.Value
> > > >
> > > > The problem is, if there is a typo in my cells or a file with that name
> > > > doesn't exist on the drive, the hyperlink is still created, but the link is
> > > > ultimately invalid. I have no way of knowing that it is invalid without
> > > > going back to check each link.
> > > >
> > > > Is there a way that I can check to see if the pdf file exists before
> > > > creating a hyperlink. If it's not valid, it can simply move to the next cell
> > > > without creating a link; it doesn't have to be anything fancy.
> > > >
> > > > I've seen "if - then" type suggestions here for various questions, however,
> > > > I'm relatively new to using VBA in Excel, so I'm not exactly sure if this
> > > > would work or how to properly insert them into a macro.
> > > >
> > > > Thanks for any help!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>
|
Similar Threads
