Excel Hyperlink with .NET

[flynbye]

I'm struggling with this one and am thinking that I'm close but setting up something incorrectly. Basically I build detail pages with customer information and on a main page summarize the data. For ease of use I'd like to set up a link to the other pages in column A. The .NET code that I have does place the link in the column but when I look at the address in the link I'm getting something like this: 'Pg%201'!$A$1

I'm intrigued by this as the value that I'm placing in my code is 'Pg 1'!$A$1

My code:
mainSheet.Hyperlinks.Add(mainRange.Offset(0, -1), "'" & sheetName & "'" & "!" & currRange.Address.ToString, , , CType(pg + 1, String))

Suggestions?

I always makes things much harder than they should be... that way later I can slap myself in the forhead after spending hours and hours on two lines of code and say to myself "DUH!"

 

[macleod1021]

I haven't messed with creating link to excel spreadsheets like that, but my first guess would be that you need to precede your link with the path to the executable and full path to the workbook.

 

[RonRepp]

If you have a sheet that has a space in it, you must qualify the sheetname with the apostrophe. Something like this: 'My Work Sheet'.

You can do this through code by searching for a space in activesheet.name. If it contains a space, your code would look something like this:

[b]XL.ActiveSheet.Hyperlinks.Add XL.Selection, "", "'Sheet 2'!A3", "FindMe"[/b]

However, if there is no space, it looks like this:

[b]XL.ActiveSheet.Hyperlinks.Add XL.Selection, "", "Sheet2!A3", "FindMe"[/b]

I hope this helps.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[flynbye]

Thanks guys... just for everyone's information what I eventually ended up doing was simply using the old VB6 formatting on the hyperlink setup and while .NET complains some about it, it does actually except it and link the code. So, what I ended up with looks like this:

Dim linkAddr As String = "'" & sheetName & "'" & "!" & currRange.Address.ToString
pg = pg + 1
Dim PgStr As String = pg.ToString
mainSheet.Hyperlinks.Add(Anchor:=mainRange.Offset(0, -1), Address:="", SubAddress:=linkAddr, TextToDisplay:=PgStr)
mainRange.Offset(0, -1).HorizontalAlignment = Excel.Constants.xlCenter


Thanks for the assist guys... I always appreciate it!

I always makes things much harder than they should be... that way later I can slap myself in the forhead after spending hours and hours on two lines of code and say to myself "DUH!"