+ Reply to Thread
Results 1 to 10 of 10

hyperlinks

  1. #1
    Registered User
    Join Date
    09-08-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 SP3
    Posts
    45

    Question hyperlinks

    Hey there,

    I'm currently working on a problem that I'm having with Hyperlinks. Basically I want to copy the destination data that the hyperlink points to onto another sheet.

    The main problem I'm having here is that I cant seem to find a suitable method to do this....I had the idea of using the hyperlink.follow function and then copying the data once I'm there and then moving back to the source hyperlink. My only problem with that is that I dont know how to find the cell address that the hyperlink is in, once I have found that hyperlink.

    Therefore my question is two-fold:

    - Is there a way to copy the destination data using some sort of hyperlink function?
    - If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?

    I've attached the spreadsheet below to give a better idea of what Im doing, along with the "findHyperlinks" macro,within that workbook, that I am currently working on.

    I appreciate any help anyone is willing to offer me. I thought about posting onto this website a while back due to my inexperience but wanted to try and solve it myself before posting but obviously couldnt!

    Thanks in advance,

    Jag
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: hyperlinks

    Can you elaborate on the below ?

    If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?
    I follow (no pun intended) the idea of using the hyperlink to ascertain the range to be copied etc but what I don't follow is on what basis the hyperlink itself is being used to invoke the routine.

  3. #3
    Registered User
    Join Date
    09-08-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 SP3
    Posts
    45

    Re: hyperlinks

    Can you elaborate on the below ?


    Quote:
    If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?

    I follow (no pun intended) the idea of using the hyperlink to ascertain the range to be copied etc but what I don't follow is on what basis the hyperlink itself is being used to invoke the routine.
    Hey there,

    cheers for the speedy reply. The spreadsheet that I am wanting to use the function on has 2 sheets. One sheet has criteria which are linked to another sheet. The criteria descriptions are listed on the second sheet and can then be viewed by using hyperlinks to point to the certain cells which contain the info, which is why I have to do it in this method. I would have posted that original sheet but cant due to security restrictions at my workplace. So hopefully that has made things a bit clearer. If you are still unsure then just ask away - to be honest, I was also a bit 'wierded out' about the task in hand but I have to do it, its my job!

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: hyperlinks

    I'm probably being dense but it's hard to apply the narrative above to the file you uploaded (I appreciate you can't upload the real file).

    Perhaps if you outline the workflow in terms of the sample file that may help... ie user does x, invokes y which in turn invokes z or something along those lines.

  5. #5
    Registered User
    Join Date
    09-08-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 SP3
    Posts
    45

    Re: hyperlinks

    --------------------------------------------------------------------------------
    I'm probably being dense but it's hard to apply the narrative above to the file you uploaded (I appreciate you can't upload the real file).

    Perhaps if you outline the workflow in terms of the sample file that may help... ie user does x, invokes y which in turn invokes z or something along those lines.
    Hey man,

    I appreciate your help, I guess my excel sheet I've created isnt ideal in representing what Im trying to achieve.

    I've attached hopefully a much better, clearer sheet....this might make it clearer what Im trying to do.

    Basically the way that the sheet works currently is:

    1)user looks at the requirement.
    2)user wants to find out more about this requirement so in order to do this, clicks on the "VAM" hyperlink to find out how to pass the requirement.
    3)Hyperlink then redirects the user to the VAM sheet where the information is listed.

    My objective with this sheet is to:
    1) Run a macro.
    2) This macro will then scan across the Compartment Details sheet.
    3) Find a cell which contains a hyperlink
    4) Create a new worksheet
    5) Copy the information that the hyperlink points to, onto the new worksheet in the first coloumn it see's empty.

    With this new worksheet I intent to import the information into my other system which extracts the data so that I can stick it into a report - but I guess you wont be concerned with this part of my problem.

    Does that make it any clearer about what I'm trying to achieve?
    Attached Files Attached Files
    Last edited by therealjag; 11-10-2009 at 07:18 AM.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: hyperlinks

    OK so just, finally, to be clear, VAM Pt2 is meant to represent your preferred output ?

    and by:

    Create a new worksheet
    and

    Copy the information that the hyperlink points to, onto the new worksheet in the first coloumn it see's empty
    I am presuming you mean: copy all hyperlink data to one sheet (rather than one sheet per link) and copy said data to first empty row (rather than column)

    Is all of the above correct ?

  7. #7
    Registered User
    Join Date
    09-08-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 SP3
    Posts
    45

    Re: hyperlinks

    Hey sorry no, that VAM pt 2 is just another list that gets accessed by the hyperlinks. I've attached the excel sheet again without VAM pt2 as perhaps its not needed for my shorthand example that I have given you.

    In the future I am looking to copy the info to a new workbook but if I can get it to copy to a new worksheet, in the same format as on the VAM sheet then that would be good enough for me to get the functionality working.

    Hope thats clearer!
    Attached Files Attached Files

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: hyperlinks

    Apologies for delay - was not around.

    The below is I confess a guesstimate re: requirements...

    Please Login or Register  to view this content.
    The above when run would create a new sheet with the details (A:T) of each hyperlinked cell, you will get duplicates of course where the links are the same.

    If needed you could keep track of ranges processed and exclude repeats.

  9. #9
    Registered User
    Join Date
    09-08-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 SP3
    Posts
    45

    Re: hyperlinks

    Hey wow, thats amazing, cheers for the help...I guess I have a lot of work to do before I can get anywhere near that level of competence on excel.

    If I were to now try and copy that data onto a new workbook instead of worksheet, how would I go about doing this?

    And I am intrigued by your suggestion about keeping a list of the ranges processed, how would this be possible?

    Cheers for all your help so far, it is helping a gain a better understanding of excel and vbscript day by day.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: hyperlinks

    Quote Originally Posted by therealjag
    If I were to now try and copy that data onto a new workbook instead of worksheet, how would I go about doing this?

    And I am intrigued by your suggestion about keeping a list of the ranges processed, how would this be possible?
    Pretty ugly code but the below would for ease compile the data onto one sheet within main file before copying to a new file, saving & closing new file (same location as parent file but with added extension of _LIST_datetimestamp), deleting the created sheet from the main file and reverting back to original sheet.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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