Solved

Excel VBA with Powershell

Posted on 2015-02-16
12
593 Views
Last Modified: 2016-04-05
I would like to call a powershell script from Excel VBA and return the data to a string. Here is the .ps1 code:
$ADUser = get-aduser $args[0]
$strUser = $ADUser.SurName + ", " + $ADUser.GivenName
return $strUser 

Open in new window

Here is the VBA I have:
Sub test()
    retval = Shell("powershell.exe C:\Documents\Templates\NameLookup.ps1 " & Sheet(4).Cell(6,1).Value, 1)
    MsgBox (retval)
End Sub

Open in new window

The MsgBox displays a number and not lastname, firstname.
0
Comment
Question by:m_travis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 40612758
Hi,
as far as I know it is not possible to return something from the Powershell output directly back to Excel VBA.
The "workaround" is to save the results from within the Powershell to a file and read that after the execution in VBA.
Please see also:
http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_26393097.html

HTH
Rainer
0
 
LVL 1

Author Comment

by:m_travis
ID: 40612843
Using the following code doesn't seem to be working.

$ADUser = get-aduser $args[0]
$strUser = $ADUser.SurName + ", " + $ADUser.GivenName
Out-File -filepath C:\Documents\Templates\NameLookupResults.txt $strUser

Open in new window

0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 40613043
Hi,
this should do the trick:
$ADUser = get-aduser $args[0]
$strUser = $ADUser.SurName + ", " + $ADUser.GivenName
Out-File -filepath C:\Documents\Templates\NameLookupResults.txt -inputobject $strUser

Open in new window


HTH
Rainer
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:m_travis
ID: 40613053
I wish it did. Never writes the output to the txt file.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40613327
Hi, for your Powershell script, try this:
$ADUser = get-aduser $args[0]
$ScriptPath = $MyInvocation.MyCommand.Path
$strUser = $ADUser.SurName + ", " + $ADUser.GivenName
$OutFileName = "$($ScriptPath.SubString(0, $ScriptPath.Length - 4))_Results.txt"
$strUser | Out-File $OutFileName

Open in new window


And for your Excel function, try this:
Sub test()
    Set objShell = CreateObject("WScript.Shell")
    objShell.Run "powershell.exe -ExecutionPolicy RemoteSigned -File C:\Temp\NameLookup.ps1 " & Sheets(4).Cells(6, 1).Value, 0, True
    myFile = "C:\Temp\NameLookup_Results.txt"
    Set objFSO = CreateObject("Scripting.FileSystemObjecT")
    Set objFile = objFSO.OpenTextFile(myFile, 1, False, -1)
    strContents = objFile.ReadAll
    objFile.Close
    MsgBox strContents
End Sub

Open in new window


Regards,

Rob.
0
 
LVL 1

Author Comment

by:m_travis
ID: 40617199
I am receiving an error from Visual Basic:

"Run-time error '62':
Input past end of file"

This happens on the following line of code:
strContents = objFile.ReadAll

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40617835
That might mean the Powershell script never wrote to the file.

Change the Excel routine to this:
Sub test()
    Set objShell = CreateObject("WScript.Shell")
    strPS1Script = "C:\Temp\NameLookup.ps1"
    strResultsFile = "C:\Temp\NameLookup_Results.txt"
    strLookupValue = Sheets(4).Cells(6, 1).Value
    strCommand = "powershell.exe -ExecutionPolicy RemoteSigned -File " & strPS1Script & " " & strLookupValue
    MsgBox "Running " & strCommand
    objShell.Run strCommand, 0, True
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If objFSO.FileExists(strResultsFile) = True Then
	    Set objFile = objFSO.OpenTextFile(strResultsFile, 1, False, -1)
	    If Not objFile.AtEndOfStream Then
		    strContents = objFile.ReadAll
		    objFile.Close
		    MsgBox strContents
		Else
			MsgBox strResultsFile & " is empty"
		End If
	Else
		MsgBox "Unable to find " & strResultsFile
	End If
End Sub

Open in new window


There's a few messages in there you can use for debugging so you know what's going on.  Make sure the file being read is exactly the same that the Powershell script is outputting.

Rob.
1
 
LVL 1

Author Comment

by:m_travis
ID: 40626261
I feel that it is the powershell code that is the issue. I can use the code to print to console.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40626590
Ok, let's go through some trouble-shooting steps.
1) Change your Powershell code to this.  It has a popup message showing you the argument it sees, and the output file path that it will write to:
$objShell = New-Object -ComObject Wscript.Shell
$argument = $args[0]
$ADUser = get-aduser $argument
$ScriptPath = $MyInvocation.MyCommand.Path
$strUser = $ADUser.SurName + ", " + $ADUser.GivenName
$OutFileName = "$($ScriptPath.SubString(0, $ScriptPath.Length - 4))_Results.txt"
$objShell.Popup("Argument: $argument`r`nOutput File: $OutFileName",0,"Done",0)
$strUser | Out-File $OutFileName

Open in new window


2) In the Excel routine, make sure that you specify strPS1Script = "C:\Temp\NameLookup.ps1" as the exact path to the Powershell script, and that you specify strResultsFile = "C:\Temp\NameLookup_Results.txt" as the exact path of the output file that you see in the Powershell message box.

3) Delete the output file, and run the Powershell script on it's own from a Powershell console by typing the full path to the script file, with a username argument on the end of it.  If the results file is written, we can move on.

4) Delete the output file again, and in the Excel routine, change strLookupValue = Sheets(4).Cells(6, 1).Value to point to a cell that contains a username.  The above example is on sheet 4 (in order), in cell "A6".  Run the routine, and you should see a message box that tells you what Powershell command it is going to run.  Copy that text, open a command prompt (DOS prompt), and paste in the command, and see if you get expected results.

5) Let me know which messages you get along the way, and we'll see if we can figure out what's going on.

Regards,

Rob.
0
 
LVL 1

Accepted Solution

by:
m_travis earned 0 total points
ID: 40746569
Here was the solution to my problem:
1. write the data from excel to a temp txt.
2. import and process data in powershell
3. write data to temp txt file
4. import data and process new data in excel
0
 
LVL 1

Author Closing Comment

by:m_travis
ID: 40755962
After research I realized there is no easy way to send data directly to power shell script from excel.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 41537398
Great work Rob - very useful posts.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Group policies can be applied selectively to specific devices with the help of groups. Utilising this, it is possible to phase-in group policies, over a period of time, by randomly adding non-members user or computers at a set interval, to a group f…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question