Question

I'm attempting to run windows powershell through VBA in excel, while passing variables from VBA.

My code works successfully in powershell, but I get an error that says "The string is missing the terminator" when attempting to run the script through VBA. I also get an "Expected: list separator" error within VBA.

The code that I use in powershell is:

Get-ChildItem “H:\Users\Desktop\A Folder With Subfolders Containing Files” -Recurse -Filter "*.xlsm" | Copy-Item -destination “H:\Users\Desktop\A Different Folder I Want All the Files To Be In\”

What this does is it takes files nested within subfolders, and copies them without the subfolders into a different folder, ie. it flattens the folder hierarchy.

I am trying to run this powershell code from a VBA macro. This is my following code I am using to do so, but I am getting the above described errors.

Set source = H:\Users\Desktop\A Folder With Subfolders Containing Files

Set destination= H:\Users\Desktop\A Different Folder I Want All the Files To Be In

Shell("PowerShell -noexit ""&source&"" -Recurse -Filter "*.xlsm" | Copy-Item -destination ""&destination&"", vbNormalFocus)

Answer 1

Kindly provide the code.

Provide the code in commenta and i will solve it for you.

Looking forward for your reply.

Thanks.