Excel hyperlinks - how to batch process? 4

[dydavid]

If I have a bunch of file names with directories

D:\Scans\Dec 28-2003\08-07-2003.tif
D:\Scans\Dec 28-2003\09-08-2003.tif
D:\Scans\Dec 28-2003\10-08-2003.tif

E:\Scans\Oct 28-2003\01-07-2003.tif
E:\Scans\Oct 28-2003\02-08-2003.tif
E:\Scans\Oct 28-2003\03-08-2003.tif

& want to convert them to hyperlinks is there a macro or other method that would convert them as a batch rather than one at a time?

I have used properties function & right click on these file names. It is too tedious & cumbersome.

Thanks for any feedback, Dave

 

[SkipVought]

dydavid,

Try turning on the Macro Recorder and recording inserting a single hyperlink.

Then, if you do not know what to do to make your code work in a loop, post what you have, and we'll help you take it to the next level.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dydavid]

Skip - I just get the macro I named "Hyperlink" that during ALT+F8 makes all hyperlinks the same link. Is there a way I can post this macro for review?

Thanks. Dave

 

[jksmi]

Hi, try

[blue]Public Sub[/blue] AddHyperlinks[purple]()[/purple]
[blue]Dim[/blue] n [blue]As Long[/blue]

n [purple]= 1[/purple]
[blue]While[/blue] Len[purple]([/purple]ActiveSheet[purple].[/purple]Cells[purple]([/purple]n[purple], 1)) > 0[/purple]
    ActiveSheet[purple].[/purple]Hyperlinks.Add _
    ActiveSheet[purple].[/purple]Cells[purple]([/purple]n[purple], 1), _[/purple]
    ActiveSheet[purple].[/purple]Cells[purple]([/purple]n[purple], 1)[/purple]
    n [purple]=[/purple] n [purple]+ 1[/purple]
[blue]Wend
End Sub[/blue]

this will loop through each cell in column A adding the text in the cell as a hyperlink (assumes that cells are consecutive and in column A)
Hope this helps, Jamie

 

[Harlequin007]

Alt:

You can set the macro to run in "Relative" cells. That may help also.

----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------

 

[dydavid]

jksmi - I pasted the above into Excel but now what do I do?
Does this macro have a name? How do you use it? Sorry, but this is a complete mystery to me. Is there a reference you could direct me to use?

===========

Harlequin007
"You can set the macro to run in "Relative" cells. That may help also. "

I am researching this topic as follows, but your tip is not clear to me yet. How do I set a macro to do this?

http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=Relative+cells+macros+excel

 

[dydavid]

I got this far, when I displayed edit macro:

Sub Hyperlink_creator()
'
' Hyperlink_creator Macro
' Macro recorded 4/19/2004 by ______________
'
' Keyboard Shortcut: Ctrl+Shift+L
'
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"D:\Scan_b.tif" _
, TextToDisplay:= _
"D:\Scan_b.tif"
Selection.Font.Bold = True
End Sub

but this doesn't tell me where to make it "Relative". Help anybody? Thanks, Dave

 

[dydavid]

jksmi - I pasted the above into an Excel macro that I created, put some links into A1 & A2 then ran the macro "AddHyperlinks" and got this error:

Microsoft Visual Basic
Run-time error '424'
Object required

------

then received errors in debugger:

    ActiveSheet.Hyperlinks.Add _
    ActiveSheet.Cells(n, 1), _
    ActiveSheet.Cells(n, 1)

-------
Help - I'm stuck. Sorry. Dave.

 

[jksmi]

Hi,

what I did was to paste the code from my previous post into a new module. Back in Excel I went to Tools>Macro>Macros. I highlighted AddHyperlinks, selected options and made the shortcut key CTRL+h. I entered a few file addresses into cells, A1, A2, & A3 (one in each) and hit CTRL+h and it worked fine?
Some Q's...
Are you trying to do anything different from this?
I was also assuming that you are using Excel 2k or greater?
When you get errors in the debugger what is the value of n?
What is the value of the cell on the active worksheet at row n[/] column A?

If you still cant get it to work let me know...HTH, Jamie

 

[dydavid]

Thanks jamie!
"what I did was to paste the code from my previous post into a new module. Back in Excel I went to "Tools>Macro>Macros. I highlighted AddHyperlinks, selected options and made the shortcut key CTRL+h. I "entered a few file addresses into cells, A1, A2, & A3 (one in each) and hit CTRL+h and it worked fine?
>sorry, still stuck.
"Some Q's..."Are you trying to do anything different from this?
>not that I can tell.
"I was also assuming that you are using Excel 2k or greater?
>Yup - Excel in Office XP 2002 SP-1
"When you get errors in the debugger what is the value of n?
>n=n+1 as displayed in (General)

"What is the value of the cell on the active worksheet at row n[/] column A?
>I used [cells A1, A2, A3, A4] with good known working links previously tested manually as hyperlinks.
>The values are A1 is [d:\file1.xls], A2 is [d:file2.xls], etc.
--------
> This is copied directly from the Microsoft Visual Basic (General) area
Public Sub AddHyperlinks()
Dim n As Long

n = 1
While Len(ActiveSheet.Cells(n, 1)) > 0
    ActiveSheet.Hyperlinks.Add _
    ActiveSheet.Cells(n, 1), _
    ActiveSheet.Cells(n, 1)
    n = n + 1
Wend
End Sub

>WHERE THESE *LINES ARE YELLOW HIGHLIGHTED
*    ActiveSheet.Hyperlinks.Add _
*    ActiveSheet.Cells(n, 1), _
*    ActiveSheet.Cells(n, 1)
--------
>This was the error I received & its Help:
Object required (Error 424)
References to properties and methods often require an explicit object qualifier. This error has the following causes and solutions:
*You referred to an object property or method, but didn't provide a valid object qualifier.
Specify an object qualifier if you didn't provide one. For example, although you can omit an object qualifier when referencing a form property from within the form's own module, you must explicitly specify the qualifier when referencing the property from a standard module.

*You supplied an object qualifier, but it isn't recognized as an object.
Check the spelling of the object qualifier and make sure the object is visible in the part of the program in which you are referencing it. In the case of Collection objects, check any occurrences of the Add method to be sure the syntax and spelling of all the elements are correct.

*You supplied a valid object qualifier, but some other portion of the call contained an error.
An incorrect path as an argument to a host application's File Open command could cause the error. Check arguments.

*You didn't use the Set statement in assigning an object reference.
If you assign the return value of a CreateObject call to a Variant variable, an error doesn't necessarily occur if the Set statement is omitted. In the following code example, an implicit instance of Microsoft Excel is created, and its default property (the string "Microsoft Excel") is returned and assigned to the Variant RetVal. A subsequent attempt to use RetVal as an object reference causes this error:
Dim RetVal ' Implicitly a Variant.
' Default property is assigned to Type 8 Variant RetVal.
RetVal = CreateObject("Excel.Application")
RetVal.Visible = True ' Error occurs here.
Use the Set statement when assigning an object reference.

*In rare cases, this error occurs when you have a valid object but are attempting to perform an invalid action on the object. For example, you may receive this error if you try to assign a value to a read-only property.

Check the object's documentation and make sure the action you are trying to perform is valid.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

 

[jksmi]

Hi,

I cant seem to replicate your error!? couple of suggestions though... switch the code its breaking on to

    ActiveSheet.Hyperlinks.Add _
    ActiveSheet.Range(ActiveSheet.Cells(n, 1).Address), _
    ActiveSheet.Cells(n, 1).Value

if its still not working try

Public Sub AddHyperlinks_v2()
Dim n As Long

On Error GoTo err_handler

n = 1
Debug.Print ActiveSheet.Name & " | " & ActiveWorkbook.Name

While Len(ActiveSheet.Cells(n, 1)) > 0
    
    Debug.Print "n = " & n & " value = " & ActiveSheet.Cells(n, 1)
    
    ActiveSheet.Hyperlinks.Add _
    ActiveSheet.Cells(n, 1), _
    ActiveSheet.Cells(n, 1)
    n = n + 1
Wend

Exit Sub

err_handler:

Debug.Print Err.Number, Err.Description
Resume Next

End Sub

in the VBIDE (ALT+F11) run the code (F5) and have a look in your immediate window (CTRL+g) you should see the worksheet name that you are working on, the work book name you are working on and the values in the cells that we are trying to change to hyperlinks - if any of these are not what you expect then we might be close - if not I'm pretty stuck... HTH, Jamie

 

[dydavid]

Yes!!

I got this in (CTR+g) from second version, Public Sub AddHyperlinks_v2():

Sheet1 | Hyperlinks.xls
n = 1 value = d:\file1.xls
n = 2 value = d:\file2.xls
n = 3 value = d:\file3.xls
Sheet1 | file1.xls

and the macro works making the values in n1, n2, n3 - as hyperlinks.

OUTSTANDING!!

I will do more testing and report back. This is a 'killer' application for creating documentation quickly for reference material, indexes, table of contents, etc.

Thank you Jamie. (PS - what does HTH mean - not familiar with this acronym ?)

Dave

 

[jksmi]

Hi, glad it worked, but that's very weird as all I added was some debug.prints so that that we could see what it was trying to do? One thing you could do to take this a step further is to use the filesearch to get the names of your files into the workbook to save you typing them in - it might look like

Sub CatalogFiles(Optional ByRef strFileType As String = "*", _
    Optional ByRef strLookIn As String = "C:\", _
    Optional ByRef bSearchSubFolders As Boolean = True)
' i.e. CatalogFiles "tif","D:\Scans",True
Dim fs As FileSearch
Dim wb As Workbook
Dim v As Variant
Dim n As Long

Set fs = Application.FileSearch

With fs
    .NewSearch
    .LookIn = strFileType
    .SearchSubFolders = bSearchSubFolders
    .Filename = "*." & strFileType
    .Execute
End With

n = 1
Set wb = Workbooks.Add

For Each v In fs.FoundFiles
    wb.Sheets(1).Cells(n, 1) = v
    n = n + 1
Next

Call AddHyperlinks(wb.Sheets(1))

End Sub

Public Sub AddHyperlinks(Optional ByRef ws As Worksheet)
Dim n As Long

If ws Is Nothing Then Set ws = ActiveSheet

n = 1
While Len(ws.Cells(n, 1)) > 0
    ws.Hyperlinks.Add _
    ws.Cells(n, 1), _
    ws.Cells(n, 1)
    n = n + 1
Wend
End Sub

call it from the immediate window with something like CatalogFiles "tif","D:\Scans",True to catalog all tifs in D:\Scans, including all subfolders or call it from another macro or form...

HTH (Hope this helps), Jamie

 

[dydavid]

Jamie

Your work has been invaluable. I will try the next steps as soon as I finish my accounting. This Hyperlink function is to give to my accountant on CD with the data.

I estimate another two weeks. I'll keep you posted. Thanks again for the hard work.

Dave

 

[tav1035]

dydavid,
Did someone forget to mention HYPERLINK(A1)
Quick and easy->
Assuming that your paths are in Column A, and starting in Row1,
add HYPERLINK(A1) to B1 and pull it down (auto-filling the rest). Then you could hide Column A.
A B
D:\Scans\Dec 28-2003\08-07-2003.tif HYPERLINK(A1)
D:\Scans\Dec 28-2003\09-08-2003.tif HYPERLINK(A2)
D:\Scans\Dec 28-2003\10-08-2003.tif HYPERLINK(A3)

E:\Scans\Oct 28-2003\01-07-2003.tif HYPERLINK(A4)
E:\Scans\Oct 28-2003\02-08-2003.tif HYPERLINK(A5)
E:\Scans\Oct 28-2003\03-08-2003.tif HYPERLINK(A6)

tav