locked
Insert Formula to Excel Output Spreadsheet - with Quotes... RRS feed

  • Question

  • So... I'm running into a wall here.

    I need to insert these formulae into adjacent cells on a powershell generated spreadsheet:

    =HYPERLINK("\\"&A2&"\c$")

    =HYPERLINK("\\"&B2&"\c$")

    =HYPERLINK("https://"&B3&":4343")

    These all have characters that Powershell appears to be choking on.

    I tried modifying the formula like so:

    $Sheet.Cells.Formula($count,3) = '=HYPERLINK("\\"&A2&"\c$")'
    $Sheet.Cells.Formula($count,4) = '=HYPERLINK("\\"&B2&"\c$")'
    $Sheet.Cells.Formula($count,5) = '=HYPERLINK("https://"&B3&":4343")'

    Which failed.

    Tried again based on some reading (note the ` character to the left of the quote marks) 

    $Sheet.Cells.Formula($count,3) = "=HYPERLINK(`"\\`"&A2&`"\c$`")"

    This actually generated an error:

    Insufficient memory to continue the execution of the program.
    At C:\Command Prompt\Off_Domain_Test\OffDomainTest_List.ps1:33 char:1
    + $Sheet.Cells.Formula($count,3) = "=HYPERLINK(`"\\`"&A2&`"\c$`")"
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : OperationStopped: (:) [], OutOfMemoryException
        + FullyQualifiedErrorId : System.OutOfMemoryException

    Odd as there's no shortage of memory on this machine... I'm fairly sure this is syntax related and can't get my head around what I'm missing.  

    As always, pointers or answers are greatly appreciated.

    Ben

    Tuesday, May 17, 2016 3:39 PM

Answers

  • Your syntax is wrong.

    Cell reference is like this:

    $Sheet.Cells.Item(3,$count).Formula '=HYPERLINK(A6,"MyLink")'

    OR

    $link='www.google.com'
    $Sheet.Cells.Item(3,$count).Formula="=HYPERLINK(""http://$link"")"


    \_(ツ)_/



    • Marked as answer by BAWrites Tuesday, May 17, 2016 5:21 PM
    • Edited by jrv Tuesday, May 17, 2016 5:32 PM
    Tuesday, May 17, 2016 4:42 PM

All replies

  • Your syntax is wrong.

    Cell reference is like this:

    $Sheet.Cells.Item(3,$count).Formula '=HYPERLINK(A6,"MyLink")'

    OR

    $link='www.google.com'
    $Sheet.Cells.Item(3,$count).Formula="=HYPERLINK(""http://$link"")"


    \_(ツ)_/



    • Marked as answer by BAWrites Tuesday, May 17, 2016 5:21 PM
    • Edited by jrv Tuesday, May 17, 2016 5:32 PM
    Tuesday, May 17, 2016 4:42 PM
  • I think I'll pursue the second option, only because I'm doing a fair amount of concatenation and $strings will probably ease this through.  Thanks!

    Tuesday, May 17, 2016 5:21 PM
  • I fixed a mistake in option:

    $link='www.google.com'
    $Sheet.Cells.Item(3,$count).Formula="=HYPERLINK(""http://$link"")"


    \_(ツ)_/

    Tuesday, May 17, 2016 5:32 PM