Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    transfering excel spreadsheet with hyperlinks into Access 2010

    I am not a programmer, but I figured a way to hyperlink in excel, but when I exported it into access the links did not work. Now I work with hundreds of thousands of files, the code I wrote in excel was: =HYPERLINK("F:\County\Digital\01445\" &D & ".001.tif","Link")
    I used this code because 01445 stands for volume number, the latter part of the code lets me number the pages automatically, but since the documents that are linked are multiple pages it goes to the first page only which is .001.tif, the "Link" renames the address as link. I really do not need to rename it link but the problem I have is that I have that same document that goes to multiple entries. I put an example on the bottom. As you can see I tell excel to look in the folder "digital" for the volume number and then it looks for the "document number" and adds .001.tif to it so it goes to the first page of the document. How can I put a code in one column that I can transfer into access and make the whole column a hyperlink. I work with county records which could amount to over a million records??? HELP!!! Please do not forget I am not a programmer so Please put in plain english. I am using Microsoft Office 2010.

    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 the volumn column, then go to Document number column get that number and add 001.tif. so it gets the first 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, State, County,Document #, Sequence #, Grantor, Grantee, Document Type, Book, Volume, Page, Filed, Date Filed, Time, Instrument Date, Reference, Prior Document[/QUOTE]

    Victor,

    Thanks.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Victor,

    Welcome to the Lounge as a new poster!

    I've tested the method in post #2 here and it works.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I still don't get it, since I am not going to a web page but directly to my computer, since the folders is in there. How can I make this formula work in Access, because in excel I am able to highlight all the files that are in one volume which could be a thousand lines since there are duplicates and use this code to put the right page and document number with each link on each individual line? Sorry I am confused.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Victor,

    Ok I'll try to explain it and hopefully I understand what you are trying to do.

    First, you are saving your spreadsheet to a .htm file on your computer not on a web page it's just a file formatted like a web page.
    ExcelSaveAsHTM.JPG
    Next, you go into access and use the Import wizard to import the .htm file {under the External Data Tab select More then HTML Document}.
    ImportWizard.JPG
    Now use the Browse button to find the .htm file where you saved it. Then click OK.

    On the next screen check the box at the top for "First Row Contains Column Headings" then click Next
    The next screen will show the Link column highlighted. Change the Data Type: to Hyperlink then Click Next
    On the next screen just click Next to allow Access to assign unique identifiers to each record...this should take care of your duplicate problem.
    On the next screen change the Import to Table: box to a table name of your choosing.
    Click Finish.
    Click Close.
    You're done.

    Now if you are trying to import only selected rows from the Excel worksheet you'll need to select them and copy them to a separate sheet (preferably a separate workbook) and follow the steps above for each one you create.
    Last edited by RetiredGeek; 2012-11-17 at 15:57.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    excel does not give an option to save as htm file?

    I am not working from the web at all, I take a text file and put it into excel, by puting in the code in excel to hyperlink it, I am able to take info from one column that tells excel what volume to look in, after that it goes to the document number column takes the number in that line and adds .oo1.tif to it, this way it links it automatically since I can do a a whole volumn at one time. When I creat this in excel it does not give me the option of saving as a htm. file? This is very confusing? I will attach the excel spreadsheet so you can see what I am working with.
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I saved the excel spreadsheet as a hml file and put it on my desktop, when I go to acccess and look for it, I click on it to bring it into access and get a message stating that the file does not contain data or the file is too large????

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'm going to suggest an alternate method,
    First in Excel
    Amend your hyperlink formula to display the actual link (like this =HYPERLINK("F:\Leon County\Digital\01448\00" &E:E & ".001.tif","##"&"F:\Leon County\Digital\01448\00" &E:E & ".001.tif"&"##")
    this is necessary, because Access doesn't "see" the Excel Hyperlink. The # symbols will be used by Access to define the link
    In Access,

    Create a linked table to your Excel Spreadsheet. External Data Ribbon >Import & Link Group > Excel
    Once inside the wizard, browse to your Excel spreadsheet and leave the default selection of Importing into a new table in the database, (subsequently you'll Append into the same table).
    You'll accept the option of the first row in the spreadsheet containing column headings and use the Next button.
    In the Field Options window, select the column containing the hyperlinks and change the data type to Hyperlink. Go through the subsequent columns, identifying the field types.
    Complete the wizard. Your links should be functional. The real trick here is amending your original hyperlink formula in Excel.

    Hope this helps.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Victor,

    Ok I've successfully imported your data per the steps outlined above. The one kicker is that when you look for the .htm file you have to get inside the initial folder where there is a .htm file for each SHEET in the workbook and select Sheet1.

    Export:
    Sheet1-htm.JPG
    Import:
    FieldTypeHyperLink.JPG

    Results: See zipped database file below.


    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    thank you all

    I will try each one, since it is simpler to program in excel for it to make a link by to taking a document number from a column, than typing in 3 million numbers. I did Leon by hand but have alot of other counties to do. Thank you for all your help!!!!

    Vic

  10. #10
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, I tried Webgenii's way and it worked great in excel but when I imported it into access, and I clicked on the hyperlink the following message was given: Microsoft Access cannot follow the hyperlink to 'G:\Madison County\Digital\1955\00027315.001.tif'. I edited the hyperlink for that line and it would make it the exact same way 'G:\Madison County\Digital\1955\00027315.001.tif' pressed ok, it worked fine but I cannot do this 980,798 lines? Why doesn't it work???

    Confused!!!!

  11. #11
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    FIGURED IT OUT THE PROBLEM WITH WEBGENII'S FORMULA IS THAT HE HAD SAID TO DO IT LIKE THIS: "like this =HYPERLINK("F:\Leon County\Digital\01448\00" &E:E & ".001.tif","##"&"F:\Leon County\Digital\01448\00" &E:E & ".001.tif"&"##") " BUT YOU ONLY NEED ONE POUND (#) SIGN AT EACH POINT NOT TWO.

    THANKS YOU JUST MADE MY DAY!!!!!!

Posting Permissions

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