Ask Experts Questions for FREE Help !
Ask
    contactamer's Avatar
    contactamer Posts: 3, Reputation: 1
    New Member
     
    #1

    Jul 8, 2008, 12:55 AM
    Macro for setting hyperlinks
    Hi

    I have a column of numeric values. (e.g. 100, 120,132 etc.)

    I want each cell in the column to link to a corresponding file on the file system.

    The filenames begin with the numeric value in the cell (e.g. '100 info', '120 data', '132 report' etc.)

    All the files are in the same folder location.

    I would like to create a macro that creates a hyperlink for each cell in the column such that the cell with value '100' links to the filename that begins with the same value i.e. '100 info' from the above examples.

    I don't know what code I need for this although I have written the psuedocode for it below:

    ' Get list of filenames at htmlbase location.
    ' For each filename
    ' if cell value is same as beginning of filename
    ' then set hyperlink for cell to that filename
    ' exit
    ' else do nothing

    Could someone help me out with this?
    thanks
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Jul 8, 2008, 05:47 AM
    You can write a directory listing to a text file with the command line:

    DIR *.* > C:\foldername\directory.txt

    You can then import that file into Excel and create a table of filenames. You can then Copy the first 3 characters of the filename into the column to the left and then use that table with a Vlookup to pick up the filename.
    contactamer's Avatar
    contactamer Posts: 3, Reputation: 1
    New Member
     
    #3

    Jul 8, 2008, 08:35 PM
    Is there a way to do this entirely in a VB module wihtin the excel sheet?
    Would you be able to provide the code to do this? I don't know VB at all.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Jul 8, 2008, 10:32 PM
    Your project is beyond my meager Excel programming experience. But I know what you're considering is possible.

    I hate to send you elsewhere, but these guys have helped me many times:
    Excel Programming - Excel Help Forum
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #5

    Jul 9, 2008, 05:58 AM
    While I'm an Access VBA specialist, Excel VBA is another matter. You can set up a batch file to write the directlry listing to a text file. Then read that in. So it would seem its possible to do. JB's link is a good place for Excel VBA help.
    contactamer's Avatar
    contactamer Posts: 3, Reputation: 1
    New Member
     
    #6

    Jul 9, 2008, 05:56 PM
    Thanks all, will follow up accordingly

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel hyperlinks don't open in PDF [ 6 Answers ]

I have created some hyperlinks in an Excel preedsheet, when I convert the worksheet to a PDF, the hyperlinks are not active. What do I need to do to activate them in the PDF or what is it that I need to do in Excel before converting to a PDF? What am I doing wrong?

I cannot execute any hyperlinks on the Internet [ 2 Answers ]

I am running WinXP Home Ed. with IE 7 Just recently this has been happening. When I am at a website and I click on a hyperlink to take me to another site.............I am stuck where I was and I see a red circle with the prohibited line through it. Could this be a bug or spywear of some sort?...

About hyperlinks in Word Doc. [ 4 Answers ]

I save Word Docs as html for website use. These docs often have links that by default save to open in the same window. How can I save a Word Doc as html so that the links are all _blank? Thanks!


View more questions Search