Run PowerShell Commands from Excel VBA

[remeng]

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:

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

2) Execute PowerShell code. This doesn't include the Change Directory 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:

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

 

[remeng]

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.

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.

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

 

[Andrzejek]

Just a shot in the dark. . .
[tt]
Shell ("POWERSHELL.exe -noexit CD C:\Desktop\file name correction macro\test files [highlight #FCE94F];[/highlight] Get-ChildItem -recurse -include *~* | foreach-object { $name = $_.fullname; $newname = $name -replace '~.*\.','.'; rename-item $name $newname }")
[/tt]

---- Andy

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

 

[strongm]

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 ,,,

 

[remeng]

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

 

[strongm]

Something like:

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

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

End Sub[/color]

 

[remeng]

Hi Strongm,

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

New RegExp  'requires a reference to Microsoft vbScript Regular Expressions

Thanks,

Mike

 

[Andrzejek]

You need to do (add one of these):

---- Andy

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

 

[remeng]

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.

 

[Andrzejek]

It will just work, all of that will be included in the Excel (.xls[highlight #FCE94F]m[/highlight]) file

But the best way to know is to try and see

---- Andy

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

 

[sxschech]

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

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

 

[mintjulep]

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

 

[strongm]

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

 

[remeng]

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

Till next time,

Mike

 

[dhookom]

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