Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run PowerShell Commands from Excel VBA

Status
Not open for further replies.

remeng

Technical User
Jul 27, 2006
509
1
0
16
US
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
 
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
 
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
 
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 ,,,
 
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
 
Something like:

Code:
[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]
 
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
 
You need to do (add one of these):

MSScript_slmdrt.png


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
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.
 
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
 
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
 
For simple cases like this VBA has the LIKE operator that doesn't require adding a reference or binding an object.
 
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
 
Thanks everyone! The code works and I'm happy with the results.

Till next time,

Mike
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top