Hyperlink

alring · Dec 12th 2005

Hi there

I ahve a hyperlink that opens an another documnet. I use this hyperlink in different documents. But most of the name for the document that i want to open is always the same. It's ony´ly the last part that change.

So in one document the hyperlink opens 6666-DOC-402-03.pdf. But in an another document the hyperlink opens 7777-DOC-402-04.pdf. Is it possible to write the hyperlink so it automatic take the highest number after the last -. The number in front of the name e.i."6666" I change manualy.

Alring

rmullen · Dec 12th 2005

Re: Hyperlink

you could put the hyperlink in a string

Code

Sub createHyperlink(ByRef theLink as String)


theLink = theLink & "-Doc-402-00.pdf"


End Sub

then you would just call it with ...

Code

theLink = 'whatever the user says it should be


createHyperlink(theLink)
'value is passed back into theLink string


msgbox theLink & " is your hyperlink."

hth

alring · Dec 12th 2005

Re: Hyperlink

Maybe I didn't explain myself good enogh. The last part of the filename before the dot is version-number of the document.

I have the following:
6666-DOC-402-XX.pdf
and
7777-DOC-402-YY.pdf

In different cells I write the hole filename and then I put them together in the a hyperlink.

E.I
Cell A1=6666
Cell A2=-402-
Cell A3=XX
cell A4=.pdf
In another document is it the same but cell A1 will be 7777

But when I make the hyperlink then I have to write E.I. 02 in cell A3. But if change that document to version 03. Then the name will also be 03.

So what I want it the hyperlink automatic takes the highest version number.

Be the way. I don't wnat to use VBA for this. I mean I hope that I can do it without. If not then I have to use VBA.

Regards

Goppi · Dec 12th 2005

Re: Hyperlink

to be able to automated the version number - From where whould Excel know which version is the current one?

Quote

But if change that document to version 03

Do you mean by that that the Excel workbook has 03 somwhere in the filename or something like that?

Goppi

alring · Dec 12th 2005

Re: Hyperlink

It's not the excel workbook the change number in the filename. It's the PDF file that change version-number in the filename if there is an update to the PDF-document. If there is one of these changes in version-number e.i. from 02 to 03 then 02 will 02 disapear from the filesystem. So it will aways be the highest number. Meaning where I put the XX and YY before.

I don't know if we can call it wildcard for the last part of the filename.

Regards

**norie** · Dec 12th 2005

Re: Hyperlink

Why not use the HYPERLINK worksheet function?

=HYPERLINK(A1 & A2 & A3 & A4)

alring · Dec 12th 2005

Re: Hyperlink

I want that A3 should be a wildcard so the hyperlink automatic takes the highest version-number in filename.

Regards

Goppi · Dec 12th 2005

Re: Hyperlink

I understand now - basically you are not asking for a VBA solution in a VBA forum, but for a way to define a hyperlink in Excel in a way to automatically pick up a file with a dynamic filename.

I'm afraid, I don't know a solution for this without using VBA. Basically wildcards in hyperlinks aren't valid, and if Excel doesn't know which version is the latest one you can't put a formular together. VBA could search for a file starting with the fixed part and could extract the version number.

Goppi

alring · Dec 12th 2005

Re: Hyperlink

Hi Goppi

It don't sounds good in my ears right now. But if Excel can't do it in that way then I have to change my Excel document.

Now we just come to a nother problem. I have never tried to write a macro like that in VBA before. So can you please help me to make a wildcard in VBA that can solve my problem that way.

Regards

Goppi · Dec 12th 2005

Re: Hyperlink

are the pdf's in the same directory and does only one version of each pdf exist?

alring · Dec 12th 2005

Re: Hyperlink

The pdf files is in another directory and there is only one version of each pdf file.
regards

Goppi · Dec 12th 2005

Re: Hyperlink

Copy that under 'ThisWorkbook' and change the directory to wherever your pdfs exists (you could also link the directory to a cell...). this part than runs everytime you open the workbook and changes the version number. Alternatively you could also create a button and assign the code below to the button.

Code

Const sdir = "C:\pdfs"


Private Sub Workbook_Open()
  Dim fs, f, f1, fc, filename, filesearch, version
    
  filesearch = Worksheets(1).Cells(1, 1) & _
    "-DOC" & _
    Worksheets(1).Cells(2, 1)
  Set fs = CreateObject("Scripting.FileSystemObject")
  Set f = fs.GetFolder(sdir)
  Set fc = f.Files
  For Each filename In fc
    If Left(filename.Name, Len(filesearch)) = filesearch Then
      Worksheets(1).Cells(3, 1).Value = Mid(filename.Name, Len(filesearch) + 1, 2)
      Exit For
    End If
  Next
End Sub

Display More

HTH

Goppi

alring · Dec 13th 2005

Re: Hyperlink

Hi Goppi

Thank you for the code. It's works almost perfect. I was hoping that the macro also could open the document. But it seems that i can't get it all.

:thanx:

Alring

Goppi · Dec 13th 2005

Re: Hyperlink

What do you mean - how do you want to have it working? Nearly everything can be done

Goppi

alring · Dec 13th 2005

Re: Hyperlink

Hi Goppi

As the macro is now then it's write the number for the document in cell A3. But is it also possible to have the macro to open the document? I know that if it's a word document then the VBA code is:

Code

Word.Apllication

But what is it for a Pdf file?? if I just know that then I can use the macro to find the last version and to open the pdf file.

Alring

Goppi · Dec 13th 2005

Re: Hyperlink

you can run anything out of Excel - or better with VBA in general. But I'm just confused what it is you actually want to achieve. You have got an Excel workbook, that if you open it opens a pdf file? Why using Excel?

goppi

alring · Dec 13th 2005

Re: Hyperlink

Hi Goppi

I am using Excel to control the project. In that excel document I have made some hyperlinks that can open some documents in pdf. These pdf documents sometimes change the name. But it's just the last part of the name that changes. As desribed before. The macro I have now look like this:

Code

Sub Button1_Click()


    Dim fs, f, f1, fc, filename, filesearch, version, sdir
    sdir = Worksheets(1).Cells(1, 1)
    filesearch = Worksheets(1).Cells(2, 1) & _
    "-DOC-" & _
    Worksheets(1).Cells(3, 1)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(sdir)
    Set fc = f.Files
    For Each filename In fc
    If Left(filename.Name, Len(filesearch)) = filesearch Then
    Worksheets(1).Cells(4, 1).Value = Mid(filename.Name, Len(filesearch) + 1, 2)
        Exit For
        End If
    Next
    aNameAndPath = Worksheets(1).Cells(6, 1)
    Set appWD = CreateObject("word.Application")
    appWD.Visible = True
    appWD.documents.Open filename:=aNameAndPath


End Sub

Display More

It's the part after "Next" that I quiestions to now. I know that this part can open a word document. But what do I need to write so it's Adobe Reader that opens.
Cells(6,1) is the path and file name.
Regards

Goppi · Dec 13th 2005

Re: Hyperlink

So every Excel file should open only one PDF file?

If yes, use

Code

Sub Button1_Click() 
     
    Dim fs, f, f1, fc, filename, filesearch, version, sdir 
    sdir = Worksheets(1).Cells(1, 1) 
    filesearch = Worksheets(1).Cells(2, 1) & _ 
    "-DOC-" & _ 
    Worksheets(1).Cells(3, 1) 
    Set fs = CreateObject("Scripting.FileSystemObject") 
    Set f = fs.GetFolder(sdir) 
    Set fc = f.Files 
    For Each filename In fc 
        If Left(filename.Name, Len(filesearch)) = filesearch Then 
            Worksheets(1).Cells(4, 1).Value = Mid(filename.Name, Len(filesearch) + 1, 2) 
            Exit For 
        End If 
    Next 
    aNameAndPath = Worksheets(1).Cells(6, 1) 
    ActiveWorkbook.FollowHyperlink aNameAndPath
End Sub

Display More

You could also leave out the writing back to the cells - where you wouldn't need to save everytime by using this code (which is smoother as well):

Code

Sub Button1_Click() 
     
    Dim fs, f, f1, fc, filename, filesearch, version, sdir 
    sdir = Worksheets(1).Cells(1, 1) 
    filesearch = Worksheets(1).Cells(2, 1) & _ 
    "-DOC-" & _ 
    Worksheets(1).Cells(3, 1) 
    Set fs = CreateObject("Scripting.FileSystemObject") 
    Set f = fs.GetFolder(sdir) 
    Set fc = f.Files 
    For Each filename In fc 
        If Left(filename.Name, Len(filesearch)) = filesearch Then 
            ActiveWorkbook.FollowHyperlink sdir & "/" & filename.Name
            Exit For 
        End If 
    Next 
End Sub

Display More

alring · Dec 14th 2005

Re: Hyperlink

Hi Goppi

THANK YOU :thanx:

This is perfect. Just what I needed. I don't know how I can thank you enough. This was a big help for me. :cheers:

:thanx:

Alring

Goppi · Dec 14th 2005

Re: Hyperlink

NP.You are welcome - was interesting...

Goppi

Hyperlink

Participate now!

Share