Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Macro for setting hyperlinks (https://www.askmehelpdesk.com/showthread.php?t=235040)

  • Jul 8, 2008, 12:55 AM
    contactamer
    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
  • Jul 8, 2008, 05:47 AM
    ScottGem
    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.
  • Jul 8, 2008, 08:35 PM
    contactamer
    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.
  • Jul 8, 2008, 10:32 PM
    JBeaucaire
    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
  • Jul 9, 2008, 05:58 AM
    ScottGem
    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.
  • Jul 9, 2008, 05:56 PM
    contactamer
    Thanks all, will follow up accordingly

  • All times are GMT -7. The time now is 09:22 AM.