Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlinking in excel

    I am not a programmer, but I figured a way to hyperlink in excel, and got some help from you all to exported it into access. Now I am trying to write code which will go something like this:What I wrote in excel was: =HYPERLINK("F:\County\Digital\01445\" &H:H & ".tif","Link")
    I used this code because 01445 stands for volume number, the latter part of the code lets me number the pages automatically. I put an example on the bottom. As you can see I tell excel to look in the folder "digital" for the volume number, then it finds the volume and then it looks in column H for the "the page number" and adds ".tif" to it since they are tif files, but it keeps telling me there is an error??? How can I put a code in where I am giving it the path all I want to to do automatically is get the page number from one column? I am using Microsoft Office 2010. I got it to work when I had pages that were number by a document number but now these volumes only have sequential numbers and I would like to be able to do a complete volume at one time.

    Here are my column headings, in excel I wrote that in the link it had to first go to my folder County and inside there it would find the volumn number which is in address, then go to a page number which is in column H and get that number and add .tif so it gets that page. All the info is in the columns but how do I direct it in the hyperlink without having to do it manually each line?
    Link, Document, Grantor, Grantee, Document Type, Book, Volume, Page, Filed, Date Filed, Time, Instrument Date, Reference, Prior Document

    Victor,

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Would it work if you used the cell reference (eg H1) instead of H:H and then copy it down the column?

    If not, can you give an example of what is in the various columns and what the link should be? This would allow us to help you create the hyperlink formula with the text from the various columns.


    Steve

  3. #3
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F COLUMN G COLUMN H COLUMN I COLUMN J COLUMN K COLUMN L
    HYPERLINK DOC NUMBER GRANTOR GRANTEE INSTR TYPE BOOK VOLUME PAGE FILED DATE TIME DOC DATE REFERENCE

    Here are the columns, I have a folder that is in drive H, its called Robertson County, within that folder there is another folder called Digital, within the Digital Folder there are volumes numbered (00997-01189) inside each volume the pages are numbered sequentially 1-899, I put the code =Hyperlink("H:\Robertson County\Digital\00997\(here I want it to go to column "H" and get the page number) but I can not get it to do it tells me there is an error, I have tried using =HYPERLINK("H:\Robertson County\Digital\00997\" & H") but get an error message. Any help will be greatly appreciated!

    Thanks

  4. #4
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Column a ..... Column b.......column c.......column d......column e .......column f.....column g.......column h......column i......column j....column k .....column l
    hyperlink.... Docnumber....grantor .......grantee........instr type ...... Book........volume .......... Page ......... Filed date...... Time ...... Doc date..... Reference

    they moved so i put periods keep the spacing

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps something like this (in the cell in row2):
    =HYPERLINK("F:\County\Digital\01445\" &H2 & ".tif","Link")

    Then you can copy it down the column for each row
    Steve

  6. #6
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It worked fine the first volume but the next volume the pages would not ,match up? There are alot of duplicates in the excel lines since in a document there can be more than one grantor or grantee, so while page in the first volume were going sequential even though there were duplicates when the next volume starts under volume 00997\ page 797, with volume 00998\page 001 it starts it at page 4 for some reason??? for example:
    20074477 PLANTERS & MERCHANTS STATE BANK LASTOR DOROTHY L REL D/TRUST OR 0997 781 8/3/2007 11:03 6/25/2007 REL VOL890 PG792-797
    20074478 PRIESTLY ROBERT B FIRST SOURCE ENERGY INC RATIFICATION OR 0997 782 8/3/2007 11:05 8/1/2007 J M VIESCA SUR A46 1983 AC
    20074478 PRIESTLY ROBERT B FIRST SOURCE ENERGY INC RATIFICATION OR 0997 782 8/3/2007 11:05 8/1/2007 RAT VOL899 PG526
    20074479 WILLIAMS IMA LEE DCD EST WILLIAMS IMA LEE DCD EST C C PROBATE OR 0997 783 8/3/2007 11:07 8/1/2007 SEE INSTRUMENT
    20074480 HUGHES VICKIE SADLER NAVASOTA VALLEY ELECTRIC COOP INC EASEMENT & R/W OR 0997 795 8/3/2007 11:11 10/20/2006 M C REJON SUR 26.589 AC(EM)
    20074481 HUNTER ROBERT NAVASOTA VALLEY ELECTRIC COOP INC EASEMENT & R/W OR 0997 797 8/3/2007 11:11 5/11/2007 L17 BEAVERS COVE ADD(EM)
    20074482 OAK GROVE MANAGEMENT CO LLC TEXAS COMMISSION ENVIRONMENTAL QUALITY AMENDED SOLID WASTE OR 0998 001 8/3/2007 11:16 8/2/2007 AMD VOL984 PG168-172
    20074482 OAK GROVE MANAGEMENT CO LLC TEXAS COMMISSION ENVIRONMENTAL QUALITY AMENDED SOLID WASTE OR 0998 001 8/3/2007 11:16 8/2/2007 J HENSLEY SUR A174 ETAL 4.268 AC
    20074482 REYNOLDS H GENE JR ATTY FACT TEXAS COMMISSION ENVIRONMENTAL QUALITY AMENDED SOLID WASTE OR 0998 001 8/3/2007 11:16 8/2/2007 AMD VOL984 PG168-172
    20074482 REYNOLDS H GENE JR ATTY FACT TEXAS COMMISSION ENVIRONMENTAL QUALITY AMENDED SOLID WASTE OR 0998 001 8/3/2007 11:16 8/2/2007 J HENSLEY SUR A174 ETAL 4.268 AC
    20074483 INGRAM G H JR BURLINGTON RESOURCES OIL & GAS CO LP AMENDMENT OR 0998 006 8/3/2007 11:24 8/2/2007 E MCMILLAN SUR A222 380.999 AC
    20074483 INGRAM G H JR BURLINGTON RESOURCES OIL & GAS CO LP AMENDMENT OR 0998 006 8/3/2007 11:24 8/2/2007 AMD VOL880 PG58
    20074484 LAXSON E F LEOR ENERGY LP AMENDMENT OR 0998 008 8/3/2007 13:13 7/26/2007 J M VIESCA 11 LGE A46 139.8 AC
    20074484 LAXSON E F LEOR ENERGY LP AMENDMENT OR 0998 008 8/3/2007 13:13 7/26/2007 AMD VOL889 PG425
    20074484 LAXSON E F ENCANA OIL & GAS USA INC AMENDMENT OR 0998 008 8/3/2007 13:13 7/26/2007 J M VIESCA 11 LGE A46 139.8 AC
    20074484 LAXSON E F ENCANA OIL & GAS USA INC AMENDMENT OR 0998 008 8/3/2007 13:13 7/26/2007 AMD VOL889 PG425

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't understand exactly. It is especially difficult since I can not read the text completely. Could you attach a workbook with a sample file and also for a representative sample of them indicate what the hyperlink should be?

    Steve

  8. #8
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel hyperlinking

    I have attached the spreadsheet, as you can see I am trying to use excel to determine from column H where to hyperlink the document. My formula starts like this: =HYPERLINK("H:\Robertson County\Digital\009997\" &H1 & ".tif"), I am telling it to go to my H drive, find Robertson County folder, inside that folder is another folder named Digital, inside that folder are volumes 00997-01189, each volume has pages numbered from 1 to 899 sometimes more sometimes less, I want to direct excel to find the page number that is expressed in cloumn H, now you can see there are alot of duplicates which I cannot remove meaning there can be multiple grantees and grantors to a document. I bring this all into excel from a text file that the county supplies. Now this works well if there is only one volumn done at a time on an excel spreadsheet, but if I use multiple volumes on a spreadsheet the second volume pages do not match up? pLease advise what could be happening.

    Vic
    Attached Files Attached Files

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    I'll look at your file posted.
    Based on your previous data, I have attached a file with what I think you want.

    zeddy
    Attached Files Attached Files

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    I have attached an updated version of your file.
    Is this what you are looking for????

    zeddy
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Robertson County 00997.xlsxZeddy this has no way of working, there is no such document by that name. I have a text doc plus I have images of the Documents. The images are in a folder in my computer in "H Drive" within that drive is a folder called "Robertson County", within this folder is another folder called "Digital" and within Digital folder are all the volumes numbered 00997-01189, now within the volume folders are images of the documents which are number like pages 1 through 799 plus or minus a few pages. The purpose of the excel text file is to have an index that can link to the document, I am able to do only one volume at a time on an excel page because it seems to get confused when there are multiple volumes on the excel spreadsheet where the first volume works correctly but the second volume the pages do not match up. I have attached an excel spreadsheet for one volume. The formula that is in the column show the formula that it needs to upload into access, but if you click on the hyperlink it will show the whole formula for excel and access. The problem I have is that I have do each volume individually instead of using a spreadsheet with all the volumes where I can click on page 1 of volume 997 and highlight the whole volume and paste the code and then go to the next volume and do the same thing with only changing the volume number.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure what the problem is. Zeddy's formula takes into account both the volume and the page for each row (and a "global path"). What other info does the link need to grab? What column is it in?

    Steve

  13. #13
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Zeddys formula tells you to go county, then digital and then in digital you should find 0997781.tif, but there needs to be a break between volume number 00997 and then page number 781.tif. The volume 00997 is a folder inside the Digital folder, and 00997 has 784 pages in it, Did you all look at that exel sheet I sent earlier with the hyperlink?

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    In the [Robertson County 00997.xlsx] file, column [G] has the Volume number as text value "0997" whereas your hyperlink va;ue in column [A] assumes it is "00997".
    So I think your problem is in column [G].
    If you use my previous posted file as an example, you can generate an appropriate formula that will combine the required Volume and Page.tif as required.
    But if your column does not have the correct text formatted Volume you will never be able to correctly locate the required folder.
    That is "..\Digital\00997\xxx.tif" is NOT the same as "..\Digital\0997\xxx.tif"

    zeddy

  15. #15
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes but it does not show the formula you used?

Page 1 of 2 12 LastLast

Posting Permissions

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