none
No output from Powershell, when called via VBA RRS feed

  • Question

  • I have a excel sheet with list of user names and need to get the TerminalServicesProfilePath, HomeDirectory and HomeDrive letter. VB is unable to get these information from AD. Object does not support this property error when asking for objuser.TerminalServicesProfile path.

    My goal is to fill the 2nd and 3rd columns in the excel sheet upon button click, with username in the first column.

    a) Tried calling a powershell script from vba, did not get any output.

    b) Tried calling powershell script from vba to write a txt file, but text file is not created.

    Powershell script works alone perfectly, but not when called from VBA.

    Powershell Script :

    param([string]$username)
    Import-Module ActiveDirectory
    write-host "Got User's ID : " $username
    $ADUser = Get-ADUser $username | select -ExpandProperty disting*
    $ADUser = [ADSI]”LDAP://$ADUser”
    write-host  $ADUser.InvokeGet(“terminalservicesprofilepath”)
    write-host  $ADUser.InvokeGet(“terminalserviceshomedirectory”)
    write-host  $ADUser.InvokeGet(“terminalserviceshomedrive”)
    Add-Content c:\temp\RDS_temp.txt $ADUser.InvokeGet(“terminalservicesprofilepath”)
    Add-Content c:\temp\RDS_temp.txt $ADUser.InvokeGet(“terminalserviceshomedirectory”)
    Add-Content c:\temp\RDS_temp.txt $ADUser.InvokeGet(“terminalserviceshomedrive”)

    VBA code :

    Set objWshScriptExec = objShell.Exec("powershell.exe -ExecutionPolicy Remotesigned -File c:\temp\Read_RDS.ps1 -username """ & username & """")
    objStdOut = objWshScriptExec.StdOut.ReadAll
    MsgBox objStdOut 'I am getting msgbox with "Got User's ID : user1" but blank lines below it.
    Open "c:\temp\RDS_temp.txt" For Input As #1 'I get file does not exist error
    Do Until EOF(1)
     Line Input #1, Readdata
            If Not Left(Readdata, 1) = "*" Then
                    Cells(4, 2).Value = Readdata
            End If
    Loop
    Close #1

    • Moved by Bill_Stewart Wednesday, July 26, 2017 7:41 PM This is not a VBA support forum
    Tuesday, June 20, 2017 2:55 AM

All replies

  • Your ADSI statement will not return any results.

    $ADUser = Get-ADUser $username -Properties terminalservicesprofilepath, terminalserviceshomedirectory, terminalserviceshomedrive
    write-host  $ADUser.terminalservicesprofilepath
    write-host  $ADUser.terminalserviceshomedirectory
    write-host  $ADUser.terminalserviceshomedrive
    

    You must explicitly ask for those properties.


    \_(ツ)_/


    • Edited by jrv Tuesday, June 20, 2017 3:34 AM
    • Marked as answer by Ramkumar Ponnuram Tuesday, June 20, 2017 5:45 AM
    • Unmarked as answer by Ramkumar Ponnuram Tuesday, June 20, 2017 5:45 AM
    Tuesday, June 20, 2017 3:33 AM
  • Here is how to get user information in VBA without PowerShell:

    https://stackoverflow.com/questions/21110232/getting-ad-details-based-on-username


    \_(ツ)_/

    Tuesday, June 20, 2017 3:43 AM
  • Hi JRV

    My powershell script works fine when running it directly from powershell console. It doesn't return value only when calling via VBA.

    Thanks for the alternate way, will give this a try as well.

    Cheers

    Ram

    Wednesday, June 21, 2017 12:57 AM
  • VBA has issue when getting value for Terminal Services Object. Other attributes I can use VBScript to get them. But not the Terminal Services.
    Wednesday, June 21, 2017 12:59 AM
  • Now try this:

    $ADUser = Get-ADUser $username -Properties terminalservicesprofilepath, terminalserviceshomedirectory, terminalserviceshomedrive
    write-Output  $ADUser.terminalservicesprofilepath
    write-Output  $ADUser.terminalserviceshomedirectory
    write-Output  $ADUser.terminalserviceshomedrive

    Notice the difference!


    \_(ツ)_/

    Wednesday, June 21, 2017 1:01 AM
  • I am getting error when running the code

    $username = testuser1
    $ADUser = Get-ADUser $username -Properties terminalservicesprofilepath, terminalserviceshomedirectory, terminalserviceshomedrive
    write-host  $ADUser.terminalservicesprofilepath
    write-host  $ADUser.terminalserviceshomedirectory
    write-host  $ADUser.terminalserviceshomedrive
    Get-ADUser : One or more properties are invalid.
    Parameter name: terminalservicesprofilepath
    At line:2 char:11
    + $ADUser = Get-ADUser $username -Properties terminalservicesprofilepath, terminal ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (testuser1:ADUser) [Get-ADUser], ArgumentException
        + FullyQualifiedErrorId : One or more properties are invalid.
    Parameter name: terminalservicesprofilepath,Microsoft.ActiveDirectory.Management.Commands.GetADUser


    Wednesday, June 21, 2017 1:03 AM
  • Then this is what you want:

    $usr = ([adsisearcher]"samaccountname=$username").FindOne().GetDirectoryEntry()
    $usr.PSBase.invokeget('TerminalServicesHomeDirectory')
    $usr.PSBase.invokeget('TerminalServicesHomeDrive')
    $usr.PSBase.invokeget('TerminalServicesProfilePath')


    \_(ツ)_/


    • Edited by jrv Wednesday, June 21, 2017 1:48 AM
    Wednesday, June 21, 2017 1:47 AM
  • Tried it same error, it is unable to recognise the terminalserviceprofilepath property.

    Tried the "write-output" on my script, but excel is unable to get it.

    Example :

    write-output "Terminal Service Profile Path is : " $ADUser.terminalservicesprofilepath

    in excel when i do a msgbox on the returned value, i get 

    Terminal Service Profile Path is :

    thats all, no information next to the message. But if i run the powershell script from the powershell console, i get the required output.

    Terminal Service Profile Path is : \\server\share

    Wednesday, June 21, 2017 2:13 AM
  • this works independently, but not when calling from VBA.

    Tried getting powershell to write to a file, but it is not writing to a file when calling from vba.

    it is writing to a file when i manually run it in powershell console

    Wednesday, June 21, 2017 2:15 AM
  • strange to note that it works partly, or it is missing the AD functions when running from VBA.

    a simple variable returns the value to VBA, but if i have any AD cmdlet or file writing cmdlet, they don't return any value directly or through a variable.

    the powershell script is able to return the user id, i sent as parameter to the powershell.

    Wednesday, June 21, 2017 2:21 AM
  • What was you PS code that you ran in VBA?


    \_(ツ)_/

    Wednesday, June 21, 2017 2:23 AM