We manage to call an Excel macro through powershell and it is working. It's a pretty extensive code (815 lines) but here is the part we call the vba macro and put the result on a vector to filter the value (orders, in this case) that match the value we want to reach:
$WorksheetAutomation12.Activate()
$ExcelAutomation.Run("Descobrir")
$WorksheetAutomation12.Columns.Item('D').NumberFormat = "0"
$AuxMacro = $CNPJCount+5
$FilterOrdersMacro = @()
for($i=6;$i -le $AuxMacro;$i++)
{
$MacroValue = $WorksheetAutomation12.Cells.Item($i, 4).value()
if(($MacroValue -ne 0) -and ($MacroValue -ne $null))
{
$FilterOrdersMacro += , "$MacroValue"
}
}
$FilterOrdersMacro
and the VBA macro:
Option Explicit
Dim dv() As Double
Dim dvTeste() As String
Dim dMeta As Double
Dim e As Long
Dim eTeste As Long
Dim blAchou As Boolean
Dim vOrigem()
Dim vOrigemTeste()
Dim rLast As Long
Dim blParar
Dim dDiferença As Double
Sub Descobrir()
With ThisWorkbook.Sheets("Macro")
'rLast is the last used row:
rLast = .Cells(.Rows.Count, "A").End(xlUp).Row
'Put column A into a vector:
vOrigem = Application.Transpose(.Range("A1:A" & rLast))
vOrigemTeste = Application.Transpose(.Range("B1:B" & rLast))
'"Meta" is the value we want to reach
dMeta = .Range("C2")
.Range("C5:C" & rLast + 4).ClearContents
.Range("E2:F2").ClearContents
.Range("E4") = "Executing . . ."
Recursar
.Range("E4").ClearContents
'Throw the solution on the worksheet
If blAchou Then
' DisporResultado
Else
If blParar Then
Else
End If
End If
End With
End Sub
Sub DisporResultado()
With ThisWorkbook.Sheets("Macro")
Dim n As Long
Dim nTeste As Long
.Range("C5:C" & rLast + 5).ClearContents
.Range("D5:D" & rLast + 5).ClearContents
.Range("E2") = Soma(dv)
.Range("F2") = dDiferença
For n = 1 To UBound(dv)
.Cells(n + 5, "C") = dv(n)
Next n
For nTeste = 1 To UBound(dvTeste)
.Cells(nTeste + 5, "D") = dvTeste(nTeste) '
Next nTeste
End With
End Sub
Function Recursar(Optional r0 As Long)
Dim r As Long
Dim n As Long
Dim rTeste As Long
Dim nTeste As Long
Dim dSoma As Double
If r0 = 0 Then
e = 0
eTeste = 0
r0 = 1
blAchou = False
blParar = False
dDiferença = 1.79769313486231E+308
End If
DoEvents
For r = r0 To rLast
e = e + 1
eTeste = eTeste + 1
ReDim Preserve dv(1 To e)
ReDim Preserve dvTeste(1 To eTeste)
dv(e) = vOrigem(r)
dvTeste(eTeste) = vOrigemTeste(r)
If Abs(dSoma - dMeta) < Abs(dDiferença) Then
dDiferença = dSoma - dMeta
DisporResultado
End If
Select Case dSoma
Case Is < dMeta
If r = rLast Then
e = e - 2
eTeste = eTeste - 2
If e > 0 Then
ReDim Preserve dv(1 To e)
ReDim Preserve dvTeste(1 To eTeste)
End If
Else
Recursar r + 1
End If
Case Is > dMeta
e = e - 1
eTeste = eTeste - 1
If e > 0 Then
ReDim Preserve dv(1 To e)
ReDim Preserve dvTeste(1 To eTeste)
End If
If r = rLast Then
e = e - 1
eTeste = eTeste - 1
If e > 0 Then
ReDim Preserve dv(1 To e)
ReDim Preserve dvTeste(1 To eTeste)
End If
End If
Case dMeta
blAchou = True
End Select
If blAchou found Or blParar Then Exit Function
Next r
End Function
Function Soma(v As Variant) As Double
Dim n As Long
Dim dSoma As Double
For n = 1 To UBound(v)
dSoma = dSoma + v(n)
Next n
Soma = dSoma
End Function
Sub Parar()
blParar = True
End Sub
captions: Parar = Stop. Soma = sum. Achou = value found.
thanks for all your help :)
