Hyperlinks.add

[dgkindy]

I am trying to export a recordset to excel. One of the values is also to be set as a hyperlink to a fill. The error message that comes up is "Type Mismatch".

I include a bit more then just what I believe is the problem code so that you have a better understanding as to what is going on.

While Not rs.EOF
oSheet.Range("A1").Offset(Row, 0) = rs!Skid
oSheet.Range("A1").Offset(Row, 1) = rs!Part_Number
oSheet.Range("A1").Offset(Row, 2) = rs!Manufacturer
oSheet.Range("A1").Offset(Row, 3) = rs!Description
oSheet.Range("A1").Offset(Row, 4) = rs!POText
If Not IsNull(rs!Literature) Then
oSheet.Range("A1").Offset(Row, 5) = rs!Literature
HyperAnchor = "oSheet.Range(A1).Offset(" & Row & ", 5)"
HyperAddress = VDDestination & Trim(rs!Manufacturer) & "\" & rs!Literature
HyperTextToDisplay = rs!Literature
oSheet.Range("A1").Offset(Row, 5).Hyperlinks.Add Anchor:=HyperAnchor, Address:=HyperAddress, TextToDisplay:=rs!Literature

oSheet.Range("A1").Offset(Row, 5).Hyperlinks.Add Anchor:=HyperAnchor, Address:=HyperAddress, TextToDisplay:=rs!Literature
End If
rs.MoveNext
Row = Row + 1
Wend

 

[dgkindy]

Hyperlinks

I am currently getting "Type Mismatch Error"

This is the only part I need to close the loop on my design so could really use the help.

Does anyone see the problem with what I have done

 

[boblarson]

Can you highlight which part of the code it is stopping on? That would help troubleshooting.

 

[dgkindy]

HyperAnchor = "oSheet.Range("A1").Offset(" & Row & ", 5)"

I believe this is the problem code. As part of the correct formating of a range, the correct structure would be Range("A1"), but as a string, it recognizes the " as the end of string and not the "" around the range value?
How do I ignore the "" around the range value?

 

[boblarson]

You can try either using single quotes for the part within the string, or use Chr(34) concatenated in.

 

[dgkindy]

Simplied problem

'DOESN'T WORK
Sub Macro1()
Row = 7
Testanchor = "Range(" & Chr(34) & "A1" & Chr(34) & ").Offset(" & Row & ", 1)"

Range("A1").Select
ActiveSheet.Hyperlinks.Add _
Anchor:=Testanchor, _
Address:="D:\Documents and Settings\500928646\My Documents\Baglan Bay PLC at Possum Point.doc", _
TextToDisplay:="Baglan Bay PLC at Possum Point.doc"
End Sub

'WORKS
Sub Macro2()
Range("C9").Select
ActiveSheet.Hyperlinks.Add _
Anchor:=Range("C9").Offset(1, 1), _
Address:="..\..\500928646\My Documents\Baglan Bay PLC at Possum Point.doc", _
TextToDisplay:="pops"
End Sub

I tried your suggestion, the string looks better when you display the value but it still is coming up with the same error "Type Mismatch"

I created the two bits of code, one work and the other does not.

 

[dgkindy]

Below is two pieces of code. The excel code works which basically is the same as access but in the access code I have the osheet which is now what I think is causing me the problem. Not sure what else to try at this point.

Portion of code from Access:
Dim oSheet As Excel.Worksheet
Set oSheet = oBook.Worksheets(1)

oSheet.Range("A1").Offset(Row, 5).Hyperlinks.Add _
Anchor:=oSheet.Range("A1").Offset(Row, 5), _
Address:=VDDestination & Trim(rs!Manufacturer) & "\" & rs!Literature, _
TextToDisplay:=rs!Literature

Sample code from Excel
Sub Macro1()
Row = 1
TextDisplay = "Test1.pdf"
VDDestination = "D:\Documents and Settings\500928646\Desktop\Doc\" 'Storage location for new project manuals

ActiveSheet.Hyperlinks.Add _
Anchor:=Range("A1").Offset(Row, 1), _
Address:=VDDestination & TextDisplay, _
TextToDisplay:=TextDisplay
End Sub

 

[dgkindy]

Found solution

The anchor address has to be the active cell.

The solution is to scroll through the spreadsheet and making the cell active when you need to insert the hyperlink

Row=2
Do loop until EOF

Cells(Row,5).select
HyperLinks.add Address= Selection, Hyperaddress= (File Location), Text=(Display text)

Row= Row +1
end Loop