Excel& PowerShell-带有VLOOKUP的PasteSpecial失败 [英] Excel & PowerShell - PasteSpecial Failure With VLOOKUPs

问题描述

我将VLOOKUP插入电子表格的F列中，如下所示:

I have a VLOOKUP being inserted into my spreadsheet's F column like this:

$vLookup = "=VLOOKUP($refCol,'$xlsLocsDIR[locs.xlsx]Device'!`$B`$2:`$C$rowsDvcs,2,FALSE)"
$sheetSave.Cells.Item(2,6).Formula = $vLookup

需要明确指出的是，这样正确地将以下内容保存在Excel中:

Which is, to be clear, saved properly in Excel like this:

= VLOOKUP(E2，'[locs.xlsx] Device'！$ B $ 2:$ C24549,2，FALSE)

=VLOOKUP(E2,'[locs.xlsx]Device'!$B$2:$C24549,2,FALSE)

(参考文件中有〜25k行，但我装有VLOOKUP的文件中有超过200k .)

由于要在其中执行VLOOKUP的文件大小很大，并且客户可能正在使用32位OS或Excel，因此我一次只能复制/粘贴不超过30000行，填满所有20万行，如下所示:

Because of the size of the file in which I'm doing the VLOOKUP within, and the customer could be utilizing 32-bit OS or Excel, I have to Copy/Paste no more than around 30000 rows at a time, to fill all 200k rows out, like so:

#32-bit OS/Excel app compatibility
#Excel/32-bit OS/memory errors occur if doing more than 30k cells
#instead, we do 20k, save, 20k, save, etc

for ($i=2; $i -le $rowsTrans; ($i+30000))
{
    #set the stop point, not to exceed total usedrows
    if (($i + 30000) -gt $totalRows)
        {$j = $totalRows}
    else 
        {$j = ($i+30000)}

    #copy the data
    $copyCell = (("F" + $i))
    $copyRange = $sheetTrans.Range($copyCell)
    $copyRange.Copy() | Out-Null

    $sheetSave.Activate()
    $pasteRange = $sheetTrans.Range(("F"+$i+":F"+$j)).Select()
    $sheetSave.PasteSpecial(7)
    $fileWorking.Save()
}

我只想将FLOOKUP公式从单元格F2复制到F列的下20k行中，然后保存文件，然后再次遍历直到填充整个文件.

I only want to copy the VLOOKUP formula from Cell F2, into the next 20k rows of column F, then save the file, and iterate through again until I've populated the entire file.

执行上述操作时，除了尝试了本示例以外，我还尝试了其他方法，我总是会收到MethodInvocation错误，除非我明确地使文件/工作表可见，就像这样，在上面的循环:

When I do the above, and I've tried different methods aside from this example, I always receive a MethodInvocation Error, unless I explicitly make the file/sheet visible, like so, before the above loop:

$xlsObject.Visible = $true

我对Copy()/PasteSpecial()函数调用有什么误解?为什么页面必须可见?

What am I misunderstanding about the Copy() / PasteSpecial() function calls? Why does the sheet have to be visible?

注意:我试图匿名化以上代码，并限制了理解该问题所必需的内容.代码功能，我只是不希望在任何时候都可以看到Excel实例.我希望脚本对最终用户不可见.

我收到的MethodInvocation错误通常如下:

The MethodInvocation error I receive is typically as follows:

使用"1"参数调用"PasteSpecial"的异常:的PasteSpecial方法 工作表类失败" 在线:1字符:25 + $ sheetTrans.PasteSpecial<<<< (9) + CategoryInfo:未指定:(:) []，MethodInvocationException + FullyQualifiedErrorId:ComMethodTargetInvocation

Exception calling "PasteSpecial" with "1" argument(s): "PasteSpecial method of Worksheet class failed" At line:1 char:25 + $sheetTrans.PasteSpecial <<<< (9) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

推荐答案

我可以通过做一些直接引用来解决此问题，并更改分配范围的方式，然后调用PasteSpecial，如下所示:

I was able to solve this by doing a few direct references, and changing the way I assigned the range, and then calling PasteSpecial, like so:

$pasteRange = $sheetTrans.Range(("F"+$i+":F"+$j))
$pasteRange.PasteSpecial($xlPasteValues) | Out-Null

带有这样的声明:

Add-Type -ASSEMBLY "Microsoft.Office.Interop.Excel" | out-null
$global:xlPasteFormulas = -4123
$global:xlPasteValues = -4163

这篇关于Excel&amp; PowerShell-带有VLOOKUP的PasteSpecial失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！