[PoSh/VBA] Macro injecteren in Excel vanuit Powershell

[YellowOnline]

Zelf uitgekomen uiteindelijk en er een blogpost van gemaakt: Title TK: Creating Excel files with powershell.

---

Deze thread plaatsen is niet eenvoudig. PoSh hoort half hier thuis en half in WOS. VBA hoort dan weer thuis in OFF. Enfin, ik denk dat het hier nog het meeste thuishoort.

Summiere achtergrond: een script dat via SQL bepaalde informatie van SCCM opvraagt, daar allerlei bewerkingen mee doet en dit dan uitvoert naar een Excel bestand. Op werkblad 1 staat data, op werkblad 2 staat een dropdownlist met waaruit men een keuze kan maken (in casu: een softwarepakket) en de bedoeling is dat na de keuze er een grafiek tevoorschijn komt mbt. die software.

Je krijgt iets zoals dit:

Visual Basic .NET:

Sub CreateGraph()
    With ActiveSheet.ChartObjects.Add(Left:=349, Width:=607, Top:=15, Height:=225)
        .Chart.SetSourceData Source:=Sheets("Packages Data").Range("E2:P2")
        .Chart.ChartType = xlXYScatterLines
    End With
End Sub

(in het echt moet de SourceData dynamisch worden dmv. de dropdownlist overigens)

Ik steek dat in mijn PoSh script als een codeblock:

C#:

    $HetGenoemdeCodeBlock = @"
    Sub CreateGraph()
        With ActiveSheet.ChartObjects.Add(Left:=349, Width:=607, Top:=15, Height:=225)
                                    .Chart.SetSourceData Source:=Sheets("Packages Data").Range("E2:P2")
                                    .Chart.ChartType = xlXYScatterLines
        End With
    End Sub
    "@

En nu? Hoe pomp ik dit in mijn Excelfile? Vermoedelijk hoort dit thuis in mijn workbook-object ("$Workbook01"), maar ik zie geen manier om dit te doen. Er is een property die naar VBA verwijst, nl . Workbook.VBProject, maar daar schiet ik niets mee op.

Google biedt ook geen hulp: alle threads gaan over "hoe run ik een macro in Excel vanuit Powershell". Dat is niet mijn probleem: ik maak een Excel-object aan en probeer daar een macro in te stoppen.

Dank u voor uw ideeën en suggesties.

[ Voor 11% gewijzigd door YellowOnline op 18-07-2012 16:36 ]

[pedorus]

pedorus in "[Excel 2007/VBA]Met een macro, een macro toevoegen. Kan dat?"

[YellowOnline]

pedorus schreef op donderdag 12 juli 2012 @ 16:39:
pedorus in "[Excel 2007/VBA]Met een macro, een macro toevoegen. Kan dat?"

Het voorbeeld dat op TDWTF (nomen est omen) is verschrikkelijk, maar ik zal zien of ik met die .CodeModule niets kan aanvangen.

[YellowOnline]

Kick.

Heb ik nu m'n VBA code, weet ik nog steeds niet hoe ze toe te voegen

't Is te zeggen: ik heb iemand gevonden die hetzelfde voor elkaar krijgt in C#

C#:

_mExcelApp.Workbooks.Add(oMissing);
oModule = _mWorkbook.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
sCode = "allerlei VBA code"
oModule.CodeModule.AddFromString(sCode);
RunMacro(_mExcelApp, new object[] { "CellFormat" });

Maar bij mij gaat dit mis:

C#:

$Temp = $Workbook01.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule)

code:

Missing ')' in method call.
At line:1 char:44
+ $a= $Workbook01.VBProject.VBComponents.Add( <<<< Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule)
    + CategoryInfo          : ParserError: (CloseParenToken:TokenId) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingEndParenthesisInMethodCall

De foutmelding is niet de duidelijkste, maar hij valt vast over Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdMod. Wat opzoekwerk leert dat dit een constante waarde heeft van 1. Volgende poging:

C#:

$Temp = $Workbook01.VBProject.VBComponents.Add(1)

En alweer een error:

code:

You cannot call a method on a null-valued expression.
At line:1 char:43
+ $a= $Workbook01.VBProject.VBComponents.Add <<<< (1)
    + CategoryInfo          : InvalidOperation: (Add:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Ik blijf verder zoeken, maar misschien kan iemand meer C# georiënteerd hier iets zinnigs over zeggen?

EDIT

Gevonden: het is een security issue. "Trust access to the VBA project object model" moet enabled worden op de PC die het Excelbestand genereert.

Het eindresultaat is als volgt, voor als iemand ooit iets gelijkaardigs voor heeft. Ik ga er van uit dat je al een $Workbook01 hebt. Zoniet:

C#:

$Excel01 = New-Object -ComObject Excel.Application
$Excel01.Visible = $True
$Excel01.SheetsInNewWorkbook = 1
$Workbook01 = $Excel01.Workbooks.Add()
$Worksheet01 = $Workbook01.Sheets.Item(1)

Enfin, de code mbt. VBA dus:

C#:

# #region VBA Code
    Write-Log "Injecting VBA code" -ForegroundColor White -Indent 8 -Talk

    $VBACode = @"
Sub SelectPackage()
    'By YellowOnline
    Debug.Print "SELECTPACKAGE"
    Debug.Print "    Creating box"
    ActiveSheet.DropDowns.Add(82.5, 0, 266.25, 15.75, False).Select
    Set ddlSelectPackage = ActiveSheet.DropDowns(1)
    ddlSelectPackage.Name = "ddlSelectPackage"
    ddlSelectPackage.Placement = xlFreeFloating
    ddlSelectPackage.ListFillRange = "'Packages Data'!$A$2:$A$113"
    ddlSelectPackage.DropDownLines = 44 'Pretty much goes down to row 30
    ddlSelectPackage.Display3DShading = True
End Sub

Sub AddGraph()
    'By YellowOnline
    Debug.Print "ADDGRAPH"
    
    'Declarations
    Dim Finder As Range
    Dim ChartSurface As Range
    Dim ChartObject As ChartObject
    Dim strGraphName As String
   
    'Creating the chart
    arrGraphLabels = "='Packages Data'!$E$1:$P$1"
    Debug.Print "    Labels: " & arrGraphLabels
    strRow = CStr(ActiveSheet.DropDowns(1).ListIndex + 1) '+1 because of the Title row
    Debug.Print "    Row   : " & strRow
    Sheets("Packages Graph").Activate
    arrGraphValues = "='Packages Data'!$E$" + strRow + ":" + "$P$" + strRow
    Debug.Print "    Values: " & arrGraphValues
    strGraphName = "='Packages Data'!" + "$A$" + strRow
    Debug.Print "    Name  : " & strGraphName
    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = strGraphName
    ActiveChart.SeriesCollection(1).Values = arrGraphValues
    ActiveChart.SeriesCollection(1).XValues = arrGraphLabels
        
    'Deleting the automatic Legend
    ActiveChart.Legend.Select
    Selection.Delete
    
    'Setting size
    Set ChartSurface = ActiveSheet.Range("D4:D30")
    Set ChartObject = ActiveChart.Parent
    ChartObject.Height = ChartSurface.Height
    ChartObject.Width = ChartSurface.Width
    ChartObject.Top = ChartSurface.Top
    ChartObject.Left = ChartSurface.Left
End Sub
"@
    
    Try
        {
        $VBAModule = $Workbook01.VBProject.VBComponents.Add(1)
        $VBAModule.CodeModule.AddFromString($VBACode)
        }
    Catch
        {
        Write-Log "ERROR: <To Do>" -Indent 16 -Talk
        $Excel01.Quit()
        $Excel01 = $Null
        [gc]::Collect()
        [gc]::WaitForPendingFinalizers()
        Exit-Script 1
        }
    
    # #endregion VBA Code

Write-Log en Exit-Script zijn eigen cmdlets, negeer die maar.

[ Voor 49% gewijzigd door YellowOnline op 18-07-2012 11:17 ]