Solved

Excel VBA with Powershell

Posted on 2015-02-16
12
166 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
  • 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
 
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

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now