+ Reply to Thread
Results 1 to 10 of 10

macro to convert those filepaths into a ready-to-click hyperlink

  1. #1
    Registered User
    Join Date
    04-04-2008
    Posts
    6

    macro to convert those filepaths into a ready-to-click hyperlink

    Hi All,

    I need a macro to perform a looped operation on a single column of data on an Excel spreadsheet. The column in question contains filepaths to files on the company's network. I need the macro to convert those filepaths into a ready-to-click hyperlink to the corresponding file, and for the hyperlink to read "Click to View."

    The macro has to be looped so that it will perform the operation until it reaches the end of records in the spreadsheet. Further, the macro has to be saved, such that it will be available to the user regardless of what spreadsheet she's working on (I'll duplicate the macro as necessary for all my co-workers who'll be using it).

    The problem is complicated by the fact that a blank cell in the "Filepath" column is not a good indicator of the end of the loop. This is because some rows will simply have records that contain no filepath. The only safe indicator of the end of the loop is where the operation hits the point at which ALL cells in the row are blank. That would truly spell the end of the operation.

    This is a doozy, it seems, especially for me, since I've only written a limited number of macros. But if anyone here can swing this, I'd sure appreciate it. You'd be preserving my retreating hairline : )

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    This procedure will change all of the cells in column A of Sheet1 to a hyperlink, using the cell contents as the address and the display text is "Click to View". You will need to change the column and sheet name based on your needs (red font below).
    Please Login or Register  to view this content.
    HTH

    Jason

  3. #3
    Registered User
    Join Date
    04-04-2008
    Posts
    6

    Wow : )

    Hi Jason, and Thx!

    It looks very close...

    It conducts the operation very nicely, but it adds the following to the beginning of the filepath, such that the hyperlink is ineffective:

    file:///

    The above phrase appears right before the drive designation, eg, file:///C:

    One other last consideration: I forgot to mention that the spreadsheet comes with row headings. Any way to get it to skip that first row of headings?

    I can't thank you enough, my friend!

  4. #4
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Glad it helped.

    The "file:///" should not impact whether the hyperlink will work. Did you test to see if it works when you click on it? It should work.

    As for starting in row 2, that is easy enough. That red "A1" that I have is saying to start in cell A1. If you want to start in row 2, simply change to "A2".

    HTH

    Jason

  5. #5
    Registered User
    Join Date
    04-04-2008
    Posts
    6

    Wow II : )

    I must apologize, my friend; I'd moved the file without updating the filepath : (

    It works perfectly!!!!

    I saved the macro to my personal workbook, and so it seems it's available no matter what file I'm working on. This is a really useful tool, my friend. It's aimed to work on Excel spreadsheets that get spat out by a Filemaker Pro database. I'm going to test it some more tonight and keep you updated : )

    J

  6. #6
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Good to hear.

    If you are going to store it in your personal workbook, I would recommend changing:
    Please Login or Register  to view this content.
    to:
    Please Login or Register  to view this content.
    That way, what ever sheet you are on, it will work on, and you don't need to worry about changing the sheet name if it differs from file to file.

    Jason

  7. #7
    Registered User
    Join Date
    04-04-2008
    Posts
    6

    One more thing, if you're interested...

    As I mentioned in the above post, the Excel spreadsheet is spat out by a Filemaker Pro database. The database actually has the capability of spitting out Excel HYPERLINK syntax into the appropriate column: eg,

    =HYPERLINK("C:/Documents and Settings/Username/Desktop/Test.doc","Click for Report")

    But for some reason the exported data lands in the Excel cell as text, and not as an active hyperlink formula. It requires the further step of copying the data into the cell as a formula, which isn't practical. That's why I came here looking for a macro workaround, and luckily found yourself.

    There are numerous Filemaker tweakers working on this, vis a vis a thread at FMforums.org; the resulting solution would be a really handy marriage between Filemaker and Excel, as it would enable Filemaker to generate an Excel spreadsheet of a file directory of sorts, based on a found set of records.

    Do you have any idea how to get the exported data to land in Excel as a formula, rather than text as entered? If not, I'm happy to use your handy macro, and thanks again for that!

    Best,

    J
    Last edited by jhestler; 04-04-2008 at 03:17 PM.

  8. #8
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Unfortunately, I am not familiar with FM. Mabye someone else on here is, though. GL!

    Jason

  9. #9
    Registered User
    Join Date
    04-04-2008
    Posts
    6

    Thanks!

    This will do fine in the meantime, I think : )

    I've saved the macro to my personal workbook, so I can use it with any file. When I push the button, it runs the macro fine; but it opens the personal macro workbook and leave it open and superimposed over my spreadsheet. Any way to rectify that? I'd prefer to hit the button and see nothing happen other than the running of the macro : )
    Last edited by jhestler; 04-04-2008 at 06:44 PM.

  10. #10
    Registered User
    Join Date
    04-04-2008
    Posts
    6
    Quote Originally Posted by jhestler
    This will do fine in the meantime, I think : )

    I've saved the macro to my personal workbook, so I can use it with any file. When I push the button, it runs the macro fine; but it opens the personal macro workbook and leave it open and superimposed over my spreadsheet. Any way to rectify that? I'd prefer to hit the button and see nothing happen other than the running of the macro : )

    Problem solved. I only needed to "hide" Personal.xls : )

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1