时间戳

首页 > 解决方案 > 从powershell中执行excel vba宏，宏内的for循环不起作用

excel - 从powershell中执行excel vba宏，宏内的for循环不起作用

问题描述

我正在尝试从 powershell 中执行 Excel VBA 宏。在宏内部，我使用 for 循环删除 excel 单元格中的一些错误值。
循环不是由 powershell 执行的，但是如果我在 VBA 编辑器中运行宏，它可以正常工作。

请在下面找到我的代码：

来自 Powershell 的代码：

$excel = New-Object -Com Excel.Application                                   $objMisVal = [System.Reflection.Missing]::Value                                                                                                           
$xlsdoc = "C:\Path\ExcelFile.xlsm"
$sheetname = "XXX"
$makro = "formatanpassungen"                       
$workbook = $excel.workbooks.open($xlsdoc, $objMisVal, $objMisVal, $objMisVal, `
$objMisVal, $objMisVal, $objMisVal, $objMisVal, $objMisVal, $objMisVal, `
$objMisVal, $objMisVal, $objMisVal, $objMisVal, $objMisVal)                                   

$excel.visible = $true

$excel.run($makro)                                 

$Workbook|Get-Member *Save*
$workbook.Save()

Excel VBA中的代码：

Sub formatanpassungen()
Dim iZeile As Integer
Dim sRechnungsNrOLD As String
Dim sRechnungsNrNEW As String

'ganzes Arbeitsblatt nach doppelten Rechnungsnummern durchsuchen
For i = 4 To iZeile

    If Sheets("XXX").Cells(i, 9) = 0 Then GoTo 10

    'Ermittlung RG-Nr OLD
    If i >= 5 Then sRechnungsNrOLD = Sheets("XXX").Cells(i - 1, 9)

    'Ermittlung RG-Nr NEW
    sRechnungsNrNEW = Sheets("XXX").Cells(i, 9)

    'Wenn RG-NrNEW = RG-NrOLD, then....
    If sRechnungsNrNEW = sRechnungsNrOLD Then
        Sheets("XXX").Cells(i, 14) = ""
        Sheets("XXX").Cells(i, 15) = ""
    End If

10:

Next i
End Sub

标签： excelvbapowershell

解决方案

下面的代码现在可以工作，但不是完整的代码。

' Aktualisieren Makro

Dim iZeile As Integer
Dim sRechnungsNrOLD As String
Dim sRechnungsNrNEW As String

iZeile = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row

With Range("I4:I" & iZeile)
    .NumberFormat = "General"
    .Value = .Value
End With

'Formatanpassungen
iZeile = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
For i = 4 To iZeile

    If Sheets("xxx").Cells(i, 9) = 0 Then GoTo 10

    'Ermittlung RG-Nr OLD
    If i >= 5 Then sRechnungsNrOLD = Sheets("xxx").Cells(i - 1, 9)

    'Ermittlung RG-Nr NEW
    sRechnungsNrNEW = Sheets("xxx").Cells(i, 9)

    'Wenn RG-NrNEW = RG-NrOLD, dann....
    If sRechnungsNrNEW = sRechnungsNrOLD Then
        Sheets("xxx").Cells(i, 14) = ""
        Sheets("xxx").Cells(i, 15) = ""
    End If

10:

Next i

'Alle Pivot-Tabellen aktualisieren
Sheets("ÜBERSICHT_Alle").Select
Range("A7").Select
ActiveWorkbook.RefreshAll
Sheets("xxx").Select

End Sub

推荐阅读