Add Hyperlinks to Excel Through C# RRS feed

  • 質問

  • I have been running this syntax in VBA which works exactly as it should

    Function AH()
    Dim lr As Long, i As Long
    Dim sSheet As String
    Dim WS As Worksheet
    Set WS = ActiveSheet
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To lr
      If WS.Cells(i, 2).Value Like "*,*" Or WS.Cells(i, 2).Value Like "*'*" Or WS.Cells(i, 2).Value Like "*&*" Or WS.Cells(i, 2).Value Like "*-*" Or WS.Cells(i, 2).Value Like "*.*" Then
        ActiveSheet.Hyperlinks.Add Anchor:=WS.Cells(i, 15), Address:="", SubAddress:="'" & WS.Cells(i, 2).Value & "'!A1", TextToDisplay:=CStr(WS.Cells(i, 15))
        ActiveSheet.Hyperlinks.Add Anchor:=WS.Cells(i, 15), Address:="", SubAddress:=WS.Cells(i, 2).Value & "!A1", TextToDisplay:=CStr(WS.Cells(i, 15))
      End If
    Next i
    End Function

    Now I just tried to port this to C# and it does not throw any errors, and it seems to function exactly as it shoudl, however whenever I try to click one of the links I get this error 'reference is not valid'

    What is incorrect in this syntax?

    public void AddHyperlinksToTotalWorksheet()
    	long lr, i;
    	string cellVal;
    	Excel.Worksheet WS = xlApp.ActiveWorkbook.ActiveSheet;
    	lr = WS.Cells[WS.Rows.Count, 2].End(Excel.XlDirection.xlUp).Row;
    	for (i = 2; i <= lr; i++)
    		cellVal = WS.Cells[i, 2].Value;
    		Excel.Range rangeToHoldHyperlink = WS.Range["O" + i];
    		if (cellVal == "*,*" || cellVal == "*'*" || cellVal == "*&*" || cellVal == "*-*" || cellVal == "*.*")
    			xlApp.ActiveSheet.Hyperlinks.Add(rangeToHoldHyperlink, "", "'" + cellVal + "!A1", "");
    			xlApp.ActiveSheet.Hyperlinks.Add(rangeToHoldHyperlink, "", cellVal + "!A1", "");

    2016年12月19日 5:08


  • HI,

    By comparing, I think you missed a single quote for the hyperlink address.

    Please replace  "'" + cellVal + "!A1" into "'" + cellVal + "'!A1" at the first line of Hyperlinks.Add



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2016年12月20日 6:13