Hyperlink Columns = Excel Macro

Question

I have many pdfs and an excel sheet in one folder. The naming sequence is consistent.

Sheet will be named Apple. Pdfs will be named Apple_1, Apple_2

I want an excel macro to work Get the active sheet name. Hyperlink the cells in G column. When I click on text in cell 1, it should open Apple_1.pdf When I click on cell 2, it should open Apple_2.pdf. This should continue until text filled cells in that column.

I have a Word macro for the same, but I don't know how to make it work in excel. Below is the word macro.

Sub macro3()
Dim tbl As Table
Dim coll As Column
Dim path As String
Dim pdf As String
Dim path1 As String
pdfname = ActiveDocument.Name
pdfname = Left(pdfname, Len(pdfname) - 4)
pdfname = Replace(pdfname, " ", "_")
Set tbl = ActiveDocument.Tables(1)
Set coll = tbl.Columns(7)
Set colpdf = tbl.Columns(7)
i = 0
For Each c In coll.Cells
If (i <> 0 And InStr(c, ".pdf") > 0) Then
path1 = pdfname & "_" & i & ".pdf"
ActiveDocument.Hyperlinks.Add Anchor:=c.Range, Address:=path1
End If
i = i + 1
Next
End Sub

durbo · Accepted Answer · 2015-02-06 05:21:23Z

1

You are missing the directory path to the documents when setting path1. When you click the hyperlink to open its looking in a directory for "Apple1.pdf" which isn't a valid file path. You just need to add the directory path to the start of the path should look like "C:\MyPath\Apple1.pdf". Your code:

pdfname = ActiveDocument.Name
pdfname = Left(pdfname, Len(pdfname) - 4)
pdfname = Replace(pdfname, " ", "_")
path1 = pdfname & "_" & i & ".pdf"

Solution1: Assuming the documents are in the same folder as activedocument.

Dim MyPath as string
MyPath = ActiveDocument.Path
path1 = MyPath & "\" & pdfname & "_" & i & ".pdf"

Solution2: Files are in another location you can add another string address.

Dim MyPath as string
MyPath = "C:\MyOtherLocation"
path1 = MyPath & "\" & pdfname & "_" & i & ".pdf"

edited Feb 6, 2015 at 5:21

answered Feb 6, 2015 at 4:57

durbo

5664 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hyperlink Columns = Excel Macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related