Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Ohio, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a multi-tabed spreadsheet that has 4 columns and with a column of hyperlinks. The spreadsheet is getting too cumbersome with all the hyperlinks so I want to export it into an Access Database and I'm having trouble importing it and getting the hyperlinks. I have set up the table to include a hyperlink field and that gave me an error message (An error occurred trying to import file xyz.xls. The file was not imported). I've tried importing, and cut/copy/paste.

    If I set the table to text for the field, it just brings in the text and not the links. I've done the exporting to an spreadsheet and I know I get the text drop like 'DOG 09-004#../xxx/2009/xxxx004.PDF# , but how do I go the other way?

    I keep getting a text field.

    Any suggestions?
    Thanks

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Penny' post='798114' date='15-Oct-2009 14:22']I have a multi-tabed spreadsheet that has 4 columns and with a column of hyperlinks. The spreadsheet is getting too cumbersome with all the hyperlinks so I want to export it into an Access Database and I'm having trouble importing it and getting the hyperlinks. I have set up the table to include a hyperlink field and that gave me an error message (An error occurred trying to import file xyz.xls. The file was not imported). I've tried importing, and cut/copy/paste.

    If I set the table to text for the field, it just brings in the text and not the links. I've done the exporting to an spreadsheet and I know I get the text drop like 'DOG 09-004#../xxx/2009/xxxx004.PDF# , but how do I go the other way?

    I keep getting a text field.

    Any suggestions?
    Thanks[/quote]


    Have a solution that works, BUT you need to create a module and then run a procedure

    I just tested this, and did it the following way

    1. Imported the data from Excel with the Hyperlink being added into a Text field
    2. Added a new Hyperlink field into the table
    3. Ran the Code as shown below (Which I put into a module)
    You would need to change table and field names to get it to work

    Might help


    Function ToHyper()

    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
    Dim strTable As String, strTextField As String, strHyperField As String

    'Put Your table name here and the names of the test Field and the HyperLink Field
    strTable = "tblHyper"
    strTextField = "Link" 'This is the text field wih the Address In
    strHyperField = "HLink" 'This is the new Empty HyperLink Field

    'Now Run Function On table
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    Do Until rst.EOF
    If rst(strTextField) & "" <> "" Then
    rst.Edit
    rst(strHyperField) = rst(strTextField)
    rst.Update
    End If
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    MsgBox "Conversion Complete", vbExclamation


    End Function
    Andrew

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do the hyperlinks in Excel have display text different from the actual file path or web address? In that case, Access will import the display text instead of the file path / web address, which is useless for your purpose.
    To remedy this, you could do this:

    Create the following custom function in a standard module:

    Code:
    Function HyperlinkAddress(oCell As Range) As String
      On Error Resume Next
      HyperlinkAddress = oCell.Hyperlinks(1).Address
    End Function
    (The line On Error Resume Next avoids error values if the cell referred to does not contain a hyperlink.)

    Say that column D contains hyperlinks, starting in D1. Enter the following formula in the corresponding cell in the first empty column to see the hyperlink address:

    =HyperlinkAddress(D1)

    If you store the function in a module in your Personal.xls, use

    =Personal.xls!HyperlinkAddress(D1)

    You can fill this down as far as needed.

    Save the workbook, then import it into Access.

    If you import into a new table, the new column will be imported as text, but you can then change it to hyperlink.
    If you import into an existing table with the appropriate field (column) defined as hyperlink, the hyperlinks will be imported correctly.

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Ohio, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there any way I can get Excel to give me the hyperlink path? The spreadsheet is a listing of documents and the different revisions. If it is a draft I store it under a draft subdirectory (because of the file naming) and if it's a final revision, it's in a final subdirectory with the spreadsheet linking the two subdirectories with the hyperlinks. Also to add to the problem, I list it in date sequence and the hyperlink is off the date column. So document "abc" might have rev 0 on 10/1980, then a draft revision on 5/1984 then rev. 1 on 8/1990 etc. Sometimes there are more than one draft. So the code you provided me will let me link to a subdirectory but then I'd would have to go back through to change out the other subdirectory link, and it's display text is a date.

    If I could get Excel to display the hyperlink path, that would help me out to do a find and replace on the Access side. I got the data imported into a database but the hyperlink paths did not come over. The dates are blue and underlined like they are hyperlinks but no path associated with them.

    Any ideas?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm afraid I don't understand. Could you post a small sample workbook? The data can be faked if you want to hide proprietary information.

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Location
    Ohio, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is a sample of the spreadsheet. Column E is the hyperlink path. I colored the different ones. Is there an easier way to get the path in excel? Any help would be appreciated.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the attached zip file you'll find a version of the workbook that uses the HyperlinkAddress function to extract the hyperlink address from column D.
    I have also included an Access database. I imported the worksheet into tblImport. The Address field was imported as text. I then changed the data type of the Address field from Text to Hyperlink.
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Mar 2002
    Location
    Ohio, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh Hans, I think I love you. Thanks, you always come through for me. I appreciate it.

Posting Permissions

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