Search code examples
excelvbscriptbookmarks

How to add a Bookmark within excel using VBScript?

I am trying to reduce some manual work through vbscript.

I have to create a summary page with has the list of all details with hyperlinks (bookmark). Once the link is clicked it has to take me to the sheet which has the details.

I tried to create a hyperlink like this.

objExcel.ActiveSheet.Cells(1, 1) = "=HYPERLINK("Sheet2!A1", "Inventory")"

but it didn't work out. The above code works for website link though.

 objExcel.ActiveSheet.Cells(1, 1) = "=HYPERLINK(""http://www.google.com"", ""Google"")"

I'm stuck here. Please help me how to create a bookmark using VBSCRIPT.

Solution

  • I have found the answer. :)

    The below code would work fine if we add a # to Address i.e #Sheet2!A1.

    objExcel.ActiveSheet.Cells(1, 1) = "=HYPERLINK("#Sheet2!A1", "Inventory")"

    We should ensure that double quotes are also available while pasting in the cell. The hyperlink value should be like the below 

    "=HYPERLINK("&Chr(34)&"#Sheet2!A1"&Chr(34)&","&Chr(34)&"Link"&Chr(34)&")"

    where Chr(34) is double quotes.