Running Excel VBA/Macro in PowerShell ISE

[alice123456789]

Hi. Can an excel vba/macro be executed using powershell?

[Claude]

Hi Alice,

does this help ?

[HansV]

When I follow Claude's link (thanks), the first hit is Hey, Scripting Guy! How Do I Run an Office Excel Macro on Multiple Workbooks?. This tells you everything you need to know, with examples.

[alice123456789]

Hi. Thanks for all the reference links! I've used one of the scripts that I found from the links from the website. It worked but an error occurred to the part where the 3rd module from my excel macro needs to be executed.

The error occurred here:

Code: Select all

Set olFld = olApp.Session.GetDefaultFolder(16) ' olFolderDrafts ---- ERROR STARTS HERE
    On Error Resume Next
    strMonth = Format(Date - Day(Date), "mmmm yyyy")
    Set olFl2 = olFld.Parent.Folders("[Artists] " & strMonth)
    On Error GoTo 0
    If olFl2 Is Nothing Then
        Set olFl2 = olFld.Parent.Folders.Add("[Artists] " & strMonth)
End If

And the error shows:

error_1.PNG

What could the problem be?

PS: When I run the whole macro via Excel it works just how it supposed to.

[Rudi]

I cannot see any errors in that block of ode you posted.

VBA does not always highlight the exact line where the error occurs, so the issue might be a few lines above or below the highlighted line.
-- Have you double checked that you have ended a code block with End If, or End Select, or End With?
-- Have you ensure that all your object variables have been 'Set'?

You could post a larger portion of the code so we can attempt to assist if you cannot find anything yourself.

[alice123456789]

The problem is the code works without executing it on powershell.. I did a breakpoint to this macro but it only stops at

Set olFld = olApp.Session.GetDefaultFolder(16)

What else could I've been missing?

[alice123456789]

uhm I think the problem might be the microsoft exchange server is not connected yet..

[HansV]

You could insert a line

Code: Select all

    olApp.Session.Logon

above the line that causes the problem, to see if that solves it.

[alice123456789]

Now the problem is within that line of code. I will check with my partner first if the problem is coming from the administrator account that I'm using. I will give y'all an update.

[HansV]

You could test whether it makes a difference if Outlook is already running before you execute the PowerShell script.

[alice123456789]

The code stopped at the newly added line of code.

[HansV]

I'm starting to suspect that this two-stage automation - using PowerShell to control Excel, and using Excel to control Outlook - won't work. Perhaps the code should be rewritten entirely in PowerShell, so that both Excel and Outlook are controlled from PowerShell. However, I won't be able to help you with that, I'm not familiar with PowerShell.

[alice123456789]

It won't work?

[HansV]

If I were you, I would consult someone who is familiar with both PowerShell and VBA. Such a person would be in a much better position to tell you what is possible and what isn't.

Since I don't know PowerShell, I'm not in a position to make authoritative statements about it.

[alice123456789]

Thanks Hans for the advice!