Populate GPO from Text File using VBScript or other

Question

Ok, so we need to create a GPO that allows our users to only use specific programs.

GPO Location:

• User Configuration
  • Policies
    • Administrative Templates [...]
      • System
        • Run only specified Windows applications

Then setting the GPO to enabled and clicking on List of allowed applications --> Show...

I have created an excel spreadsheet containing the names of all the programs and their associated executable files with other pertinent information so that we can easily organize, add, delete, etc. the executable files that we need to allow our users access to.

This spreadsheet then dumps all the executable files into a text file.

Here is an example of what the text file looks like:

Acrobat.exe
chrome.exe
calc.exe

.
.
.

There are a lot of entries and these are likely subject to change. What I am trying to do is create a script that will take that text file and populate the GPO automatically. I don't care if we have to open the window and then run it, it does not need to run from the task scheduler (although that would be amazing if someone has that code ready). We just need it to populate this ridiculous amount of executable filenames into the fields.

Here is code I found (VBScript) that when run, should populate the fields automatically, however I cannot get it to run in the Group Policy Management Editor (it runs in the windows explorer window instead and ends up searching for some of the files)

' Open the text file, located in the same path as the script
Set objFSO = CreateObject("Scripting.FileSystemObject")
strPath = Mid(Wscript.ScriptFullName, 1, InStrRev(Wscript.ScriptFullName, wscript.ScriptName) -1)
Set objFile = objFSO.OpenTextFile(strPath & "appList.txt")

' Activate the "Show Contents" window with the "List of allowed applications".
' Note the window must be opened already and we should have selected where in 
' the list we want to enter the data before running the script
set WshShell = WScript.CreateObject("WScript.Shell")
WScript.Sleep 1000
WshShell.AppActivate "Show Contents"

' Read the file line by line
Do While objFile.AtEndOfStream <> True

    ' Each line contains one EXE name
    exeName = objFile.ReadLine

    ' Escape forbidden chars { } [ ] ( ) + ^ % ~
    exeName = Replace(exeName, "[", "{[}")
    exeName = Replace(exeName, "]", "{]}")
    exeName = Replace(exeName, "(", "{(}")
    exeName = Replace(exeName, ")", "{)}")
    exeName = Replace(exeName, "+", "{+}")
    exeName = Replace(exeName, "^", "{^}")
    exeName = Replace(exeName, "%", "{%}")
    exeName = Replace(exeName, "~", "{~}") 

    ' Send the EXE name to the window
    WScript.Sleep 100
    WshShell.SendKeys exeName

    ' Move to the next one
    WshShell.SendKeys "{TAB}"    

Loop

objFile.Close

from: http://blogs.msdn.com/b/alejacma/archive/2011/03/24/how-to-update-quot-run-only-specified-windows-applications-quot-gpo-programmatically-vbscript.aspx

Answer 1

Ok, so I tried it many different ways. If anyone is looking for an answer to do this, this is the way I've figured it out and the way I've decided to proceed. I will post all relevant code below.

In Excel, the format of my table is as follows: (With obviously WAY more entries)

Here is the VBA code I used to turn the data from this file into the proper format for the registry key:

VBA - In Excel

Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)
    Dim WholeLine As String
    Dim FNum As Integer
    Dim RowNdx As Long
    Dim ColNdx As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim StartCol As Integer
    Dim EndCol As Integer
    Dim CellValue As String

    Application.ScreenUpdating = False
On Error GoTo EndMacro:
    FNum = FreeFile
    StartRow = 2
    If SelectionOnly = True Then
        With Selection
            StartCol = .Cells(2).Column
            EndRow = .Cells(.Cells.Count).Row
            EndCol = .Cells(2).Column
        End With
    Else
        With ActiveSheet.UsedRange
            StartCol = .Cells(2).Column
            EndRow = .Cells(.Cells.Count).Row
            EndCol = .Cells(2).Column
        End With

    End If
    If AppendData = True Then
        Open FName For Append Access Write As #FNum
    Else
        Open FName For Output Access Write As #FNum
    End If
    For RowNdx = StartRow To EndRow
        WholeLine = ""
        For ColNdx = StartCol To EndCol
            If Cells(RowNdx, ColNdx).Value = "" Then
                CellValue = ""
            Else
                CellValue = Cells(RowNdx, ColNdx).Value
            End If
            WholeLine = WholeLine & Chr(34) & CellValue & ".exe" & Chr(34) & "=" & Chr(34) & CellValue & ".exe" & Chr(34) & Sep
        Next ColNdx
        WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
        Print #FNum, WholeLine; ""

    Next RowNdx

EndMacro:
    On Error GoTo 0
    Application.ScreenUpdating = True
    Close #FNum
End Sub
Sub PipeExport()
    Dim FileName As Variant
    Dim Sep As String

    FileName = Application.GetSaveAsFilename(InitialFileName:="appList", filefilter:="Text (*.txt),*.txt")
    If FileName = False Then
         ''''''''''''''''''''''''''
         ' user cancelled, get out
         ''''''''''''''''''''''''''
        Exit Sub
    End If
    Sep = "|"
    If Sep = vbNullString Then
         ''''''''''''''''''''''''''
         ' user cancelled, get out
         ''''''''''''''''''''''''''
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Extension: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
    SelectionOnly:=False, AppendData:=False

End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    PipeExport
End Sub

The file that is created is appList.txt and its format is the same format as the registry key:

"Acrobat.exe"="Acrobat.exe"
"AcroRd32.exe"="AcroRd32.exe"

Now in your GPO, add a unique program name to the allowed applications list (say test1234.exe) and in your registry editor, go to Edit > Find test1234.exe. Export that registry key under File > Export. Remove the test1234.exe line and paste in your text file. Then reimport that file and you're done!