How do I emulate Nothing parameter in VBA inside a Powershell script

2

I have Excel VBA code that calls Workbook.XmlImport()

result=ActiveWorkbook.XmlImport( _
        URL:=strXML, _
        ImportMap:=Nothing, _
        Overwrite:=True, _
        Destination:=Range("$A$1") _
     )

Note that the second parameter is Nothing -- no import map specified.

When I try to move this to PowerShell, I don't know how to specify Nothing.

$xl=New-Object -com Excel.Application;
$wb=$xl.Workbooks.Add();
$ws=$wb.WorkSheets.Item(1);
$r=$ws.Range('$A$1');
$XML='blahblah.xml';
$result=$wb.XmlImport($XML, ????, $True, $r);
$wb.SaveAs('filename.xlsx', 51, [Type]::Missing, [Type]::Missing, $False, $False, 1);
$xl.Quit()

If I use $null, [Type]::Missing, or [System.Reflection.Missing]::Value in place of ???? above, I get:

Argument: '2' should be a System.Management.Automation.PSReference. Use [ref].

Using [ref]$null in place of ???? above, I get:

Exception calling "XmlImport" with "4" argument(s): "Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"

What can I pass into the second parameter to mimic the Nothing in VBA?

excel
powershell
vba
asked on Stack Overflow Jul 4, 2017 by pteranodon • edited Jul 9, 2018 by Community

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0