OutputTo/TransferSpreadsheet to Excel a table with a column of links

[kidvegas19]

Access/Excel 2003.
I have a table of data I would like to export to Excel. For each record in this table there is a link designed to take the user to the location where the related folder (and thus the support documentation for the record) lives. This table field is of the type 'Hyperlink'. The links look something like this:

http://server1/directory/subdirectory/record1/

http://server1/directory/subdirectory/record2/

http://server1/directory/subdirectory/record3/

When I use the OutputTo method in the macro builder the results in the spreadsheet boggle me. The column of links appears to be active but they don't go anywhere, or so it seems. Turns out each and every link on the spreadsheet is linked to the spreadsheet they are on. In other words, instead of being linked as what shows in the cells:

http://server1/directory/subdirectory/record1/

http://server1/directory/subdirectory/record2/

http://server1/directory/subdirectory/record3/

The link is to:

File:///\\C\Data\testfolder\table_index.xls
File:///\\C\Data\testfolder\table_index.xls
File:///\\C\Data\testfolder\table_index.xls

Fine. I'll try TransferSpreadsheet. This works except that this method apparently uses an apostrophe delimited type scheme as all the fields have an apostrophe as the first character which makes the hyperlink not a hyperlink. Removing the apostrophe makes the hyperlink a hyperlink and it links correctly.

What on earth am I missing/doing wrong? I can not believe that something this basic would be this impossible to do.

I can't attack this using code. I need to stick with the builders etc. I can't do post mortem work on the spreadsheet either. It just needs to be generated, work correctly, and be replaced when the next regeneration happens.

Any thoughts? Hopes? ROTFL'ing wondering why I would use those two methods instead of using......?

Thanks you for the help.

 

[kjv1611]

I am shocked as well, I would have thought Access to Excel could handle links just fine, though I don't remember trying.

I can tell you a way to fix it via VBA, though, if you want. You can run the code within Excel or Access, but easiest coding will be within Excel.

Basically, use your first method to send the data over, and then run the following code (adapted to your situation) in Excel. It takes very little time, and probably can be improved. I just put it together last night to create hyperlinks on a column of cells, based on a file-naming scheme:

Sub AddLink()
    'adding a hyperlink to field1 for each record.
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim x As Long 'row
    'Do not need variable for column, because it will be known at each step.
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    
    For x = 2 To Range("A65000").End(xlUp).Row
        ws.Cells(x, 1).Select
        ActiveCell.Formula = "'" & ActiveCell.Formula
        ActiveCell.NumberFormat = "General"
        ActiveCell.Formula = "=HYPERLINK(""" & ActiveCell.Value & """,""" & ActiveCell.Value & """)"
    Next x
    
    ws.Cells(1, 10).Select
    MsgBox "complete!", vbInformation, "Complete"
    
    If ws Is Nothing Then Else Set ws = Nothing
    If wb Is Nothing Then Else Set wb = Nothing
    
End Sub

I already modified the code some, without testing, hoping it will be more accurate to what your need is. My need was a little more complex in that the hyperlink did not already exist.

Try it out, see if it works, and post back with results.

 

[kidvegas19]

Thanks for the post kjv1611, I hope it will help somebody sometime. Like I say though, I can't mess with it via code this time, especially in Excel.

That being said, I don't see why your code scheme would not work. If I get a chance I will give it a try.

Thanks again!