Powershell Script incl. VBA Excel Macro

Question

I want to write a Powershell Script, which opens a existing file and puts a macro in it. Im searching and searching, but I found nothing.

My Code is:

$Word = New-Object -ComObject Word.Application
$Word.Visible = $true
$filepathagenda = "C:\Users\$Username\Desktop\Agenda.docx"
$Word.Documents.Open($filepathagenda)

The VBA code is:

Sub Macroname()
    Dim oTab As Table
    Dim i As Integer
    Dim x As Integer
    Dim Std As Double
    Dim Min As Double
    Dim Dauer As Double
    Dim Z As Double
    Dim ZInt As Double
    Dim ZDez As Double
    Dim Txt As String

    Set oTab = ActiveDocument.Tables(1)
    i = oTab.Rows.Count

    For x = 2 To i    
        On Error GoTo ErrorHandle:

        Std = CDbl(Left(oTab.Cell(x, 5), 2))
        Min = CDbl(Mid(oTab.Cell(x, 5), 4, 2))
        Txt = oTab.Cell(x, 4).Range.Text

        Dauer = CDbl(Left(oTab.Cell(x, 4), Len(Txt) - 2))

        If Min + Dauer < 60 Then
            oTab.Cell(x + 1, 5).Range.Text = Format(Std, "00") & ":" & Format(Min + Dauer, "00")
            oTab.Cell(x + 1, 5).Select
        Else
            Z = (Min + Dauer) / 60
            ZDez = Z - Int(Z)
            ZDez = ZDez * 60
            oTab.Cell(x + 1, 5).Range.Text = Format(Std + Int(Z), "00") & ":" & Format(ZDez, "00")
            oTab.Cell(x + 1, 5).Select
        End If

        GoTo NoError:

ErrorHandle:
    oTab.Cell(x + 1, 5).Range.Text = Format(Std, "00") & ":" & Format(Min, "00")
    oTab.Cell(x + 1, 5).Select
    Resume NoError:

NoError:
    Next x

Ende:
End Sub

How can i put a Macro into the file ? I just found some commands, where i can run a macro, but nothing about embedding a macro into a file like that. After the Script is embed into the docx/docm File i want to run the macro in the file with:

$word.run("Macroname") 
$word.quit() exit application

SmrtGrunt · Accepted Answer · 2019-04-23 15:15:46Z

2

You're probably looking for the .Import method of the Word document.

objDocumentObject.VBProject.VBComponents.Import(strFilePathAndName)

Where strFilePathAndName is a reference to a text document containing your macro.

The following uses a FileDialog method to choose the Word document and insert the macro line-by-line as a string, but it could also be done by referencing a filename variable using the method above.

Sub AddMacroToWordDoc()
    With Application.FileDialog(msoFileDialogOpen)
        .Show
        Dim appWord As Word.Application
        Set appWord = New Word.Application

        Dim docDocToAdjust As Word.Document
        Set docDocToAdjust = appWord.Documents.Open(Trim(.SelectedItems(1)))

        docDocToAdjust.VBProject.VBComponents("ThisDocument") _
            .CodeModule.AddFromString _
            "Sub Test()" & vbLf & _
            "  MsgBox ""It works""" & vbLf & _
            "End Sub"
    End With
End Sub

Of note, both of these methods require the file to be trusted before VBA code insertion will work (for obvious reasons).

Edit: If you're using this in VBA from another Microsoft product, you also need to have the Microsoft Word Object Library referenced.

edited Apr 23, 2019 at 15:15

answered Apr 23, 2019 at 13:41

SmrtGrunt

92912 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SmrtGrunt Over a year ago

@defjay91 - If my answer sufficiently answered your question, please mark the answer as the solution (and upvote if you feel appropriate), so that the question appears as resolved for others browsing the site. Thanks!

defjay91 Over a year ago

Hi @SmrtGrunt, thanks for your help, but both are VBA Commands - right? I need a PS Solution. Or is that impossible? My Problem is: I have a Agenda in OneNote I want to export it as a Word Document After export i want to run the Macro to calculate the times And i want to do the most of the steps via script..

SmrtGrunt Over a year ago

@defjay91 Those are the VBA commands, but as far as I know they are referencing the Windows OLE library, so there is probably a PS equivalent syntax.

Collectives™ on Stack Overflow

Powershell Script incl. VBA Excel Macro

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related