Running Excel VBA/Macro in PowerShell ISE

alice123456789
3StarLounger
Posts: 203
Joined: 20 Oct 2015, 09:06

Running Excel VBA/Macro in PowerShell ISE

Post by alice123456789 »

Hi. Can an excel vba/macro be executed using powershell?
Thanks & Regards,
Alice

User avatar
Claude
cheese lizard
Posts: 6241
Joined: 16 Jan 2010, 00:14
Location: Sydney Australia

Re: Running Excel VBA/Macro in PowerShell ISE

Post by Claude »

Hi Alice,

does this help ?
Cheers, Claude.

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Running Excel VBA/Macro in PowerShell ISE

Post by 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.
Best wishes,
Hans

alice123456789
3StarLounger
Posts: 203
Joined: 20 Oct 2015, 09:06

Re: Running Excel VBA/Macro in PowerShell ISE

Post by 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.
Thanks & Regards,
Alice

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Running Excel VBA/Macro in PowerShell ISE

Post by 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.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

alice123456789
3StarLounger
Posts: 203
Joined: 20 Oct 2015, 09:06

Re: Running Excel VBA/Macro in PowerShell ISE

Post by 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?
Thanks & Regards,
Alice

alice123456789
3StarLounger
Posts: 203
Joined: 20 Oct 2015, 09:06

Re: Running Excel VBA/Macro in PowerShell ISE

Post by alice123456789 »

uhm I think the problem might be the microsoft exchange server is not connected yet..
Thanks & Regards,
Alice

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Running Excel VBA/Macro in PowerShell ISE

Post by 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.
Best wishes,
Hans

alice123456789
3StarLounger
Posts: 203
Joined: 20 Oct 2015, 09:06

Re: Running Excel VBA/Macro in PowerShell ISE

Post by 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.
Thanks & Regards,
Alice

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Running Excel VBA/Macro in PowerShell ISE

Post by HansV »

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

alice123456789
3StarLounger
Posts: 203
Joined: 20 Oct 2015, 09:06

Re: Running Excel VBA/Macro in PowerShell ISE

Post by alice123456789 »

The code stopped at the newly added line of code.
Thanks & Regards,
Alice

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Running Excel VBA/Macro in PowerShell ISE

Post by 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.
Best wishes,
Hans

alice123456789
3StarLounger
Posts: 203
Joined: 20 Oct 2015, 09:06

Re: Running Excel VBA/Macro in PowerShell ISE

Post by alice123456789 »

It won't work? :sad:
Thanks & Regards,
Alice

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Running Excel VBA/Macro in PowerShell ISE

Post by 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.
Best wishes,
Hans

alice123456789
3StarLounger
Posts: 203
Joined: 20 Oct 2015, 09:06

Re: Running Excel VBA/Macro in PowerShell ISE

Post by alice123456789 »

Thanks Hans for the advice!
Thanks & Regards,
Alice