Automate creation of a table of hyperlinks?

I would like to create a simple spreadsheet consisting of a single
column, where each entry is a hyperlink to a related image file (located
in the same folder as the spreadsheet).

It's easy, but laborious, to do the cells one at a time - either with
the Hyperlink function or Insert Hyperlink.

I can easily generate the list of text entries (just the image file
names, to start) with a DOS DIR command to create an importable text
file.  But haven't been able to figure out how to convert these into
Hyperlinks in one fell swoop! 

    - Herb
0
Herb1 (2)
3/4/2004 4:37:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
269 Views

Similar Articles

[PageSpeed] 30

Herb

You could try this macro from David McRitchie

Sub MakeHyperlinks()
'David McRitchie
  Dim cell As Range
  For Each cell In Intersect(Selection, _
            Selection.SpecialCells(xlConstants, xlTextValues))
     With Worksheets(1)
       .Hyperlinks.Add Anchor:=cell, _
        Address:=cell.Value, _
        ScreenTip:=cell.Value, _
        TextToDisplay:=cell.Value
      End With
  Next cell
End Sub

Gord Dibben Excel MVP

On Thu, 04 Mar 2004 08:37:31 -0800, Herb <Herb@the.nitpickers> wrote:

>I would like to create a simple spreadsheet consisting of a single
>column, where each entry is a hyperlink to a related image file (located
>in the same folder as the spreadsheet).
>
>It's easy, but laborious, to do the cells one at a time - either with
>the Hyperlink function or Insert Hyperlink.
>
>I can easily generate the list of text entries (just the image file
>names, to start) with a DOS DIR command to create an importable text
>file.  But haven't been able to figure out how to convert these into
>Hyperlinks in one fell swoop! 
>
>    - Herb

0
Gord
3/4/2004 4:56:54 PM
Thank you Gord! That looks good - I'll give it a try!

 - Herb


Gord Dibben wrote:
> 
> Herb
> 
> You could try this macro from David McRitchie
> 
> Sub MakeHyperlinks()
> 'David McRitchie
>   Dim cell As Range
>   For Each cell In Intersect(Selection, _
>             Selection.SpecialCells(xlConstants, xlTextValues))
>      With Worksheets(1)
>        .Hyperlinks.Add Anchor:=cell, _
>         Address:=cell.Value, _
>         ScreenTip:=cell.Value, _
>         TextToDisplay:=cell.Value
>       End With
>   Next cell
> End Sub
> 
> Gord Dibben Excel MVP
> 
> On Thu, 04 Mar 2004 08:37:31 -0800, Herb <Herb@the.nitpickers> wrote:
> 
> >I would like to create a simple spreadsheet consisting of a single
> >column, where each entry is a hyperlink to a related image file (located
> >in the same folder as the spreadsheet).
> >
> >It's easy, but laborious, to do the cells one at a time - either with
> >the Hyperlink function or Insert Hyperlink.
> >
> >I can easily generate the list of text entries (just the image file
> >names, to start) with a DOS DIR command to create an importable text
> >file.  But haven't been able to figure out how to convert these into
> >Hyperlinks in one fell swoop!
> >
> >    - Herb
0
Herb1 (2)
3/4/2004 5:46:33 PM
Well, that looked very promising, but I'm not sure I know what I'm doing
with it. I install the macro successfully, enter a fully qualified file
name into one or more cells, then execute the macro. All I get is a
Visual Basic error message, 
"Run time error '1004': 
Application-defined or object-defined error"

Any additional help or pointers would be welcome!

 - Herb


Gord Dibben wrote:
> 
> Herb
> 
> You could try this macro from David McRitchie
> 
> Sub MakeHyperlinks()
> 'David McRitchie
>   Dim cell As Range
>   For Each cell In Intersect(Selection, _
>             Selection.SpecialCells(xlConstants, xlTextValues))
>      With Worksheets(1)
>        .Hyperlinks.Add Anchor:=cell, _
>         Address:=cell.Value, _
>         ScreenTip:=cell.Value, _
>         TextToDisplay:=cell.Value
>       End With
>   Next cell
> End Sub
> 
> Gord Dibben Excel MVP
> 
> On Thu, 04 Mar 2004 08:37:31 -0800, Herb <Herb@the.nitpickers> wrote:
> 
> >I would like to create a simple spreadsheet consisting of a single
> >column, where each entry is a hyperlink to a related image file (located
> >in the same folder as the spreadsheet).
> >
> >It's easy, but laborious, to do the cells one at a time - either with
> >the Hyperlink function or Insert Hyperlink.
> >
> >I can easily generate the list of text entries (just the image file
> >names, to start) with a DOS DIR command to create an importable text
> >file.  But haven't been able to figure out how to convert these into
> >Hyperlinks in one fell swoop!
> >
> >    - Herb
0
Herb2738 (1)
4/4/2004 6:58:26 AM
Reply:

Similar Artilces:

No hyperlink balloon
I am running Outlook 2007 on Windows 7 Pro x64. When I open an e-mail in my inbox and hover over the links, there is no balloon popping up to show where the link is actually going (e.g.: link says Microsoft, balloon shows http://www.microsoft.com). I do see a quick flash of something, as if the balloon was going to appear, but it never does. I had this feature on Windows XP with Outlook 2003, and consider it a security risk to click on links now. Does anyone know how to get this feature working in Outlook 2007? I have already tried running the Microsoft Office 2007 repai...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

Need access advise regarind linked versus imported tables
I have designed an application that uses a linked csv file as the main table. The reason for this is because another user creates this file and jsut puts it into a designated folder on their computer. Here is the issue. The csv file contains approximately 20K records. I cannot create any type of index on a linked file. Am I better off importing the file into access (or reading the linked table and inserting them into another table with an index)? Is there an easy way to perform something like this? Will this increase performance? I am pretty new to access and am looking for any and all...

converting docx to rtf hyperlinks
I’m using a windows XP system with Word 2007. I’m attempting to link small files in a single older together via hyperlinks. I have changed formats to rtf for a reason. The interlocking hyperlinks were changed by find and replace using the alt-F9 function. Did not work. Although the alt-f9 shows the correct rtf, the Insert-hyperlink shows docx. Why did the find and replace not work? Is there a way of converting the docsx hyperlinks to rtf easy? Please have mercy on me! I’m completely confused about using hyperlinks. I have an application that I’ve been working on for mo...

Outlook 2007 won't open hyperlinks...
SP1 automatically installed yesterday for Outlook 2007, and I now get "This operation has been cancelled due to restrictions in effect on this computer" whenever I try to click a hyperlink. I Googled this problem and tried: Resetting IE Resetting IE via Access Defaults Importing new reg key for HKLM/Software.../htmlfile I'm out of tricks. Does anyone know of anything else I can do? It's infuriating that Microsoft released an update that completely screws with the way I work. My inclination is uninstall SP1 - but then who knows what other problems that might cause. Anyo...

Products/Services Table
I am using Access 2007. I have a database for my Products/Services industry. I have a table containing my products/services, as well as a Work Order Form that is linked to the Products/services table from which I can choose via drop down the products that I am selling to that customer. The Product/services table contains 3 fields; Product, Cost & Price. The Work Order Form also contains those three fields, and when I choose a product from the drop down, the corresponding Price field fills in as well. Now, sometimes (after bargaining of course!) I want to change the price for ...

Excel spreadsheet hyperlink
hi, I am using a excel spreadsheet object to display some information in a webpage.I want to insert a hyperlink in one of the cells say http://www.microsoft.com . I am able to write this in the cell. But it does not initially show it as an hyperlink. On going to the cell and cliking within the cell it becomes hyperlink. What should i do so that it initially shows as an hyperlink bye Abhilash Hi Manu For Excel 2000 and above you go to Tools>Auto Corect Options-Autoformat as you type and check "Internet and network path with hyperlinks". ** Posted via: http://www.ozgrid.com ...

Making a list of all persons in a table in a corresponding form.
Hi all, In a database of mine I have a table containing persons' name, address and so on. In the corresponding form I have of course all the fields where the users put in the data. In this form I want to have a list showing all the persons in the table as well, so that the users can click the right person in the list and have his or her data filled into the fields. I have tried to make a table form from the same table and include it as a subform. This, however, was not successful because the subform only showed the person in focus in the form, not all the persons in the table. Also...

Pasting Word table cell with paragraph markers into single Excel c
I have a Microsoft Word document that contains a table, each cell of which may contain paragraph markers. I need to copy and paste this table into Excel. However, whenever a cell from the Word table contains paragraph markers, rather than pasting the contents of the entire cell into a single cell in Excel, the paragraph markers are treated as delimiters, and the data is pasted into multiple Excel cells. If the cell from the table in Word contains one paragraph marker, then when I paste that cell to Excel, it pastes it into two cells. If it contains two paragraph markers, then it pa...

How to get Table Description Field Text to show on Forms? Hovering? Right-Click?
Can I get the fioeld description I enter in the Table Design View to show on Forms or Queries? When the mouse hovers over the field or field name?? When the user right clicks on the field or field name?? At the bottom of the screen when the mouse hovers? How? Thanks for any help. Mel Ken, I've done some programming but not much in Access. How do I (where do I click) to add a function to the "standard module" ??? And, am I correct in understanding that the first function would be called each time the db opened? And the 2nd function would be called each time the form op...

I want to send autom fax using a distribution list
I live in USA. I create a distribution list with 125 fax numbers in Honduras. All the fax numbers are +011 (504) number, but the fax in Windows XP don't recognize that list. What can I do? You can start by not multiposting. -- Russ Valentine [MVP-Outlook] "JLG" <JLG@discussions.microsoft.com> wrote in message news:ED7BFC16-71A5-4800-A36C-000E85163913@microsoft.com... >I live in USA. I create a distribution list with 125 fax numbers in >Honduras. > All the fax numbers are +011 (504) number, but the fax in Windows XP don't > recognize that list. What can...

Hyperlinks in Excel and elsewhere
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I'm just starting to switch from PC to mac. In general fine, except a string of problems relating to hyperlinks. I use hyperlinks extensively in Word, Excel, emails. In generall I mean hyperlinks that are (say) name of website, with complicated hyperlink behind. I have several key spreadsheets that are virtually all hyperlinks to web sites I need to run my business. So I immediately hit a problem when I loaded it into the mac and all the links had gone. Is there a way of bringing these in without losing the hyperlinks? Then I thoug...

How to turn off warning when hyperlinking to other program?
I am hyperlinking cells in Excell to sound files on my hard drive which open up Windows media player and play the files. However, when I click the hyperlinks, office opens up a warning window stating "Some files contain viruses . . . it is important that this file is from a trustworthy source." How do I stop this warning from appearing? ...

pivot table multiple consolidation ranges
When I create a pivot table based on data from one excel worksheet, the pivot table automatically creates one layout field for every column header on the worksheet; but when I choose the multiple consolidation option and add two consolidation ranges to my pivot table data, the layout shows only a field named "Rows" and one named "Columns" - in other words, it doesn't create fields named after the column headers that I can drag into the respective pivot layout areas (page, column, row, data). The column headers in both of the worksheets (consolidation ranges) are...

Item.content field in RMS Items table
I was taking a look at the structure of the item table in RMS 1.3, Headquarters. At the end of the record is a field called "Content". It is an ntext field. I don't see anyplace on the Item Properties form where this field is referenced. Is this an obsolete field, and if so, would it be safe for me to utilize this field for some of my custom information? -- Bill Yater The Worth Collection byater@worthltd.com Bill, There are a few fields in the Item table that either have never been implemented or are no longer in use. It should be safe to use contents. We have used th...

hyperlinks in Outlook Express
Just wondering if there is a way to set Outlook Express to open hyperlinks. They appear active, but when I click on them, nothing happens. I use IE for internet access. Any help would be appreciated. Thanks! this is an outlook newsgroup, not an outlook express newsgroup, try one o them ! N "Lynn" <anonymous@discussions.microsoft.com> wrote in message news:08a101c3cf1b$85e4b210$a001280a@phx.gbl... > Just wondering if there is a way to set Outlook Express > to open hyperlinks. They appear active, but when I click > on them, nothing happens. I use IE for internet acce...

Synchronizing Custom Tables
1) If I created a new customer profile in Store A, I want it to be available in Store B and other stores. Is this possible? How? 2) I want to create a new table to be linked to Customer table. How can I syncronize it to the HQ and other Stores? ...

Tables in Publisher
I'd like to set up a table in Publisher with lines on the bottom of every cell - like a list. When I select the table and ask for a line on the bottom, it only gives me a line on the bottom border of the whole table. Is there a quick way to get a line on the bottom of every cell without having to select every single cell and add the line? Thanks. silver wrote: > I'd like to set up a table in Publisher with lines on the bottom of every > cell - like a list. When I select the table and ask for a line on the bottom, > it only gives me a line on the bottom border of the who...

Serial Reference Creation SO
In a HQ Environment - Store Operations for all areas applicable should use as a prefix to reference creation the store ID from dbo.Configuration. This then provides HQ with data that is acurate to each store. I have requested this as I have found that pretty well much no information about stocktakes gets to head office (HQ) - so I am doing this via Tablesync (it has it's own issues) and seems to work to the most part. However I do find that since you can name your own reference number then there 'could' be the possibility of an error in soem form - human or otherwise. If ...

Command Bar Creation
I have search on old posts on this topic and there are a lot of information but maybe I need more basics on this... I have tried several of the options but can not make appear any new command on my excel file command bar... I have Excel 2002.... I copied below code from "Roman" from a post back in 2005 but not working for me.... maybe I am not loading it in the right place... I try a module, I try in Private Sub Workbook_Open(), and try whatever but not luck :( Need help to create a new menu on my command bar that will work just with a specific file... Appreciat...

Automate unprotecting of blank cells
I have a spreadsheet that has several cells with values entered int it. I want others to add some values, but I don't want them to change cell that already have values. I know that I can use goto, special, constants and protect thos cells. Can someone recommend a macro or method to review each cell within specified range and unprotect it if it is blank? Any suggestions would be appreciated. Jim Palme -- Jim Palme ----------------------------------------------------------------------- Jim Palmer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=521 View t...

"is now available for editing" problem with Excel-automated mail merge
I have an excel file template that automated to open from an Access application that will 1) save the template as a new file with the current date/time appended to the file name, 2) pull data from an Access database and format the data into a usable mail merge file, and 3) open the mail merge file in Word. This all works well only I have two problems with which I need some help. The first problem is how can I automate the mail merge file to open with the current Excel file as the datasource for the mail merge? Right now, I'm cutting and pasting the file path into Word myself. The second...

automated data entry to a s/s
i have a daily requirement to 1) collate data on a solaris (unix) system to a datafile 2) import the datafile (ascii) to a excel spreadsheet 3) create a graph in the s/s of the data 1) is automated 3) is built on the fly in the s/s already (ie the necesaary stuff is in place to convert the data to a graph) The bugbear is the importing manually of the data to the s/s. Not a huge task (I have omitted some tedious steps above but they can eventually be automated too...) but I would like to automate the entire proces such that w/out human intervention - by a script that runs automatically at a...

Indexes in tables.
Hi, Can I add index for tables in SQL Manager. For example for SOP30200 index for any field. Will it be a problem with program or not? Any other ways? Thanks, Vitali I believe any future upgrade will blow away your index. -- Charles Allen, MVP "Vitali V" wrote: > Hi, > > Can I add index for tables in SQL Manager. For example for SOP30200 index > for any field. Will it be a problem with program or not? > Any other ways? > > Thanks, > > Vitali > > > Suggest you leave the tables alone. You'll just get into a pickle.. "Char...

Formatting data table
I have a data table shown under a diagram. However it won't display all the decimals. I.e. in the input to the diagram for january 2006 is 1.123, but only 1.1 is shown in the data table. The only solution seems to be to enlarge the diagram but this is not an option in this particular case! As a last resort I could embed a table under a traditional diagram without the data table, but that is second best. Looking forward to any help If you right click on the data table you can change the font size by clicking on 'format data table' and the font tab. You can type in the size ...