×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Run PowerShell Commands from Excel VBA

Run PowerShell Commands from Excel VBA

Run PowerShell Commands from Excel VBA

(OP)
Hi All;

I am trying to run 2 different codes in PowerShell from VBA. Both lines of code when entered into PowerShell directly work, but from Excel VBA, they don't execute. I've tried both independent of each other just to see if PowerShell was doing anything, but neither code worked and PowerShell didn't do anything. I tried both with PowerShell closed and Opened.

Order of operations:

1) Change the directory to the variable pathway. Pathway is a value stored in a field on a worksheet called Menu. Pathway includes the entire folder string. Something like:

CODE

"C:\Users\Desktop\Sandbox\file name correction macro\test files" 


2) Execute PowerShell code. This doesn't include the Change Directory code:

CODE

Get-ChildItem -recurse -include *~* | foreach-object { $name = $_.fullname; $newname = $name -replace '~.*\.','.'; rename-item $name $newname } 

3) Close PowerShell if it opens.

VB Code I tried:

CODE

Dim StringCommand_tilde As String
Dim Change_Directory As String

'Change_Directory = "CD " & pathway

StringCommand_tilde = "Get-ChildItem -recurse -include *~* | foreach-object { $name = $_.fullname; $newname = $name -replace '~.*\.','.'; rename-item $name $newname } "


    Set WshShell = CreateObject("WScript.Shell")
    Set WshShellExec = WshShell.Exec(StringCommand_tilde)
    strOutput = WshShellExec.StdOut.ReadAll 

Since this is new ground for me, where am I going wrong and how can I address the roadblock?

Thanks,

Mike

RE: Run PowerShell Commands from Excel VBA

(OP)
Update:

I am able to run the two different scripts now from Excel but I'd like to combine them into a single string. How can I do that?

Here is the working code.

CODE

Shell ("POWERSHELL.exe -noexit CD " & "C:\Desktop\file name correction macro\test files")

Shell ("POWERSHELL.exe -noexit Get-ChildItem -recurse -include *~* | foreach-object { $name = $_.fullname; $newname = $name -replace '~.*\.','.'; rename-item $name $newname }") 

When I am in PowerShell I can combine the code with a ;, but in VBA it doesn't seem to work.

CODE

cd "C:\Desktop\file name correction macro\test files" ; Get-ChildItem -recurse -include *~* | foreach-object { $name = $_.fullname; $newname = $name -replace '~.*\.','.'; rename-item $name $newname } 

Thanks,

Mike

RE: Run PowerShell Commands from Excel VBA

Just a shot in the dark. . .

Shell ("POWERSHELL.exe -noexit CD C:\Desktop\file name correction macro\test files ; Get-ChildItem -recurse -include *~* | foreach-object { $name = $_.fullname; $newname = $name -replace '~.*\.','.'; rename-item $name $newname }")

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Run PowerShell Commands from Excel VBA

Quick question - is there a particular reason you need to run this in Powershell, given you are working in VBA, which can do pretty much the same thing ,,,

RE: Run PowerShell Commands from Excel VBA

(OP)
Hi Andy,

The code still does not work.

Strongm,

I didn't realize that Excel VBA had that capability. Can you point me toward a solution?

Thanks,

Mike

RE: Run PowerShell Commands from Excel VBA

Something like:

CODE -->

Public Sub Rename()
    Dim fn As String
    
    ChDir "C:\Desktop\file name correction macro\test files"
    fn = Dir("*~*")

    Do While fn <> ""
        With New RegExp  'requires a reference to Microsoft vbScript Regular Expressions
            .Pattern = "~.*\."
            Name fn As .Replace(fn, ".")
        End With
        fn = Dir
    Loop

End Sub 

RE: Run PowerShell Commands from Excel VBA

(OP)
Hi Strongm,

what does this line of code mean? I am getting a "Compile error: User-defined type not defined" error.

CODE

New RegExp  'requires a reference to Microsoft vbScript Regular Expressions 

Thanks,

Mike

RE: Run PowerShell Commands from Excel VBA

You need to do (add one of these):

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Run PowerShell Commands from Excel VBA

(OP)
Thanks Andy. If someone is sent the file, do they need to also add the Reference or will it just work since I set it up in the project?

The code works.

RE: Run PowerShell Commands from Excel VBA

It will just work, all of that will be included in the Excel (.xlsm) file

But the best way to know is to try and see smile

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Run PowerShell Commands from Excel VBA

If you prefer not to set a reference as shown in the screen shot from Andy, the alternative is late binding.

CODE -->

Public Sub Rename()
    Dim fn As String
    Dim RegExp AS Object

    Set regExp = CreateObject("vbscript.regexp")	'Does not require a reference to Microsoft vbScript Regular Expressions

    ChDir "C:\Desktop\file name correction macro\test files"
    fn = Dir("*~*")

    Do While fn <> ""
        With RegExp  
            .Pattern = "~.*\."
            Name fn As .Replace(fn, ".")
        End With
        fn = Dir
    Loop

End Sub 

RE: Run PowerShell Commands from Excel VBA

For simple cases like this VBA has the LIKE operator that doesn't require adding a reference or binding an object.

RE: Run PowerShell Commands from Excel VBA

It does indeed - however LIKE will only verify there's a match, it won't do the replace. So you would have to write additional code to deal with that. The regular expression does it all for you in one hit

RE: Run PowerShell Commands from Excel VBA

(OP)
Thanks everyone! The code works and I'm happy with the results.

Till next time,

Mike

RE: Run PowerShell Commands from Excel VBA

Remeng
You should click the Great Post in one or more of the replies to acknowledge the people who helped and identify the key posts for others.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close