Hi All,

I have an issue that I can't work out....

I am trying to use PowerShell to write an excel formula which contains a variable into a cell.

$batchname = "Batch15c"

$vl = "=VLOOKUP(D2,x:\messaging\$batchname!R1:R300,2,FALSE)"

Using powergui I can see $vl = "=VLOOKUP(D2,x:\messaging\Batch15c!R1:R300,2,FALSE)"

I use this to put the value in a cell - $ws.Cells.Item(2,5).value() = $vl

and when I check the cell I have:-

=VLOOKUP(D2,x:'C:\messaging\[Batch15c]Batch15c'!R1:R300,2,FALSE)

any ideas what I am missing or doing wrong?

TIA

Andy

What's the full path, filename and worksheet name in the external workbook you're trying to reference? Assuming that you're looking up something in a file named "Batch15c.xls", and the sheet name is "Sheet1", your formula should look like this:

$vl = "=VLOOKUP(D2,'x:\messaging\[$batchname.xls]Sheet1'!R1:R300,2,FALSE)"

The signle quotes around everything before the ! character are necessary, as are the square brackets around the file name portion, the file extension and the worksheet name.

Edit:  For more information on this type of external reference, see http://office.microsoft.com/en-ca/excel-help/create-an-external-reference-link-to-a-cell-range-in-another-workbook-HP010102338.aspx#BMintro

• Edited by David WyattMVP 22 hours 59 minutes ago

Hi David,

Thanks for the quick reply. I have changed things slightly since posting the message in an attempt to get it working.

I now have

$ws.cells.item(2,5).value() = "=VLOOKUP(D2,'x:\messaging\[$batchname.csv]Sheet1'!$A$1:$F$300,2,FALSE)"

as I want to put the formula in that cell but I get the error message

Exception setting "Value": "Exception from HRESULT: 0x800A03EC"
At E:\Viridor\exceltest.ps1:39 char:27
+     $ws.cells.item(2,5).value <<<< () = "=VLOOKUP(D2,'x:\messaging\[$batchname.csv]Sheet1'!$A$1:$F$300,2,FALSE)"
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertySetValueTI

 $ws.Cells.Item(2,5).value() = $vl

Should be:

 $ws.Cells.Item(2,5).value2 = $vl

No p

Sorry - I think it should look like this:

$vl = "=VLOOKUP(D2,'x:\messaging\[$batchname.xls]Sheet1'!R1:R300,2,FALSE)"
$sheet.Cells.Item(1,1).Value2= $vl

I altered the code slightly to fit my needs

$vl = "=VLOOKUP(D2,'x:\messaging\[$batchname.csv]Sheet1'!$A$1:$F$300,2,FALSE)"
$ws.Cells.Item(2,5).Value2= $vl

But still get the same error

Exception setting "Value2": "Exception from HRESULT: 0x800A03EC"
At E:\Viridor\exceltest.ps1:40 char:22
+     $ws.Cells.Item(2,5). <<<< Value2= $vl
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

You've added some $ characters to your latest version, which need to be escaped when they're in a double-quoted powershell string. Try this:

$ws.cells.item(2,5).value() = "=VLOOKUP(D2,'x:\messaging\[$batchname.csv]Sheet1'!`$A`$1:`$F`$300,2,FALSE)"

In my tests, it didn't matter whether I used Value(), Value2 or Formula when assigning a formula to a cell via automation.  All 3 worked fine. I'm not sure whether you can refer to a CSV file in this way, though. I haven't tried that, and it might be a better question to be asked over in the Office forums.

• Marked as answer by UKMorite 21 hours 49 minutes ago

Thanks guys, that works perfectly

I might be able to get a cup of tea this afternoon now

I have found in PowerShell it is better to not use the old $A:$5 syntax.  Use absolute. 'A1:F300'  which avoids the issue of escaping.

Is there some new way of writing absolute cell references in Excel?  I was only aware of the "$" character for that.

Is there some new way of writing absolute cell references in Excel?  I was only aware of the "$" character for that.

A1:B100

This works whenI use it to lookup IP addresses in an external sheet

$vlookup"=VLOOKUP(F1,'c:\scripts\[book1.xlsx]Sheet1'!A1:B255,2,FALSE)"

Of course you cannot build it like this in the wizard.  You can open the wizard on it but if you change the range the wizard will revert back to $ syntax.

Since Excel 2002 I believe we can use this.

Another method is:

$vl=@'
=VLOOKUP(B6,'[book1.xlsx]Sheet1'!$A$2:$B$12,1,FALSE)
'@

This allows the $ without the double-quoted string and yet still allows the single quotes in the string.

But A1:B100 is a relative cell reference, not absolute.  The difference is that those values will increment if you copy or fill the formula to another cell in Excel.

http://office.microsoft.com/en-ca/excel-help/switch-between-relative-absolute-and-mixed-references-HP010342940.aspx

In the xase of vlookup it is an absolute reference because we are not moving or copying.  With vlookup it refers to the $A$1 directly.

It works.  It avoids issues in PowerShell and the wizard correctly uses it except when you use the wizard to select at which time the wizard switches to the absolute reference.

Another way to explain it could be to say that since an external spreadsheet reference is no open then there is no active cell so A1 refers to $A$1.

So far as I have used it, it

I don't think we're on the same page about what an absolute reference is.  The formula works either way, so long as no one tries to copy the formula to another location in Excel (after the script is complete.)  I used this code as a test (with a random spreadsheet I had sitting in my test directory):

$excel = New-Object -ComObject Excel.Application
$wb = $excel.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$excel.Visible = $true

$ws.Range("A1").Value2 = 'SomeValue'
$ws.Range("B1").Value2 = "=VLOOKUP(A1,'$pwd\[test.xls]Sheet1'!C2:D9,2,FALSE)"

After that code executes, I have a copy of Excel up on my screen, and cell B1 has a formula of "=VLOOKUP(A1,'C:\source\Temp\[test.xls]Sheet1'!C2:D9,2,FALSE)".  If I copy row 1 to row 2, cell B2 has a formula of "=VLOOKUP(A2,'C:\source\Temp\[test.xls]Sheet1'!C3:D10,2,FALSE)".

Because the cell references were relative, C2:D9 became C3:D10.

In the case of injecting the formula and using it to reference an external sheet we have no problem with it.  If you want the formula to be copied then you must use absolute reference. We are no copying but are injecting.

If you are concerned then just be sure to escape the cell references.   I have used this quite a bit and have not had any issues as long as we don't try copying formulas or using this in a place where we might be specifying a copy.

What's the full path, filename and worksheet name in the external workbook you're trying to reference? Assuming that you're looking up something in a file named "Batch15c.xls", and the sheet name is "Sheet1", your formula should look like this:

$vl = "=VLOOKUP(D2,'x:\messaging\[$batchname.xls]Sheet1'!R1:R300,2,FALSE)"

The signle quotes around everything before the ! character are necessary, as are the square brackets around the file name portion, the file extension and the worksheet name.

Edit:  For more information on this type of external reference, see http://office.microsoft.com/en-ca/excel-help/create-an-external-reference-link-to-a-cell-range-in-another-workbook-HP010102338.aspx#BMintro

• Edited by David WyattMVP Friday, April 04, 2014 11:33 AM

You've added some $ characters to your latest version, which need to be escaped when they're in a double-quoted powershell string. Try this:

$ws.cells.item(2,5).value() = "=VLOOKUP(D2,'x:\messaging\[$batchname.csv]Sheet1'!`$A`$1:`$F`$300,2,FALSE)"

In my tests, it didn't matter whether I used Value(), Value2 or Formula when assigning a formula to a cell via automation.  All 3 worked fine. I'm not sure whether you can refer to a CSV file in this way, though. I haven't tried that, and it might be a better question to be asked over in the Office forums.

• Marked as answer by UKMorite Friday, April 04, 2014 12:43 PM