Need help to automate some commands from Excel to Powershell


I have an Excel file with some Powershell scripts that are automatically filed from other cells.
What I need is to create a button that will copy /past each command individually in one Powershell session.
The scrips are located on the cells (W3,X3,Y3,Z3, AA3 and AB3)

I will like firs to open powershell running a ps1 file and the on that session to copy paste the scrips located in the cells a following:

- W3
- wait time 10 seconds
- X3
- Y3

Thank you and I appreciate the help.
Razvan MelinteService Desk level 2 support analystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
Hi Melinte, well if you double-click any Ps1 you will get PS running, or even if you right-click a ps1 file you will get the option to be "run in PowerShell".

W3, X3, Y3, Z3, AA3, and AB3
1st you need to map the Columns:
column.pngW3 = column 23 row 3
X3 = column 24 row 3
Y3 = column 25 row 3
Column 26 has to be skipped
AA3 = column 27 row 3
AB3 = column 28 row 3

#Global Variables
$global:ScriptLocation = $(get-location).Path     #Get Current Script location
$Global:FilePath = "$Global:ScriptLocation\PSWithExcel.xlsx"    #Specify the path to the Excel file (should be in the same folder of the running script)

# Create an Object Excel.Application using Com interface
$readExcel = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$readExcel.Visible = $true

# Open the Excel file and save it in $WorkBook
$WorkBook = $readExcel.Workbooks.Open($Global:FilePath)

#Get All WorkSheets in the Book
foreach($item in $workBook.Worksheets){

$WorkSheet = $WorkSheetsName | select -First 1
#Open the workbook Itself
$WorkSheet = $WorkBook.sheets.item($WorkSheet)
#Activate the Wanted sheet
$WorkSheet.Activate() | Out-Null


23..28 | %{
    if($_ -ne 26){ #26 = z there's nothing there.
        #Load Script on W3 3,23
        #Load Script on X3 3,24...
        $scripts+= $WorkSheet.Cells.Item($row,$_).Text

#Powershell Execution
$scripts | %{
    if($j -eq 1){
        Invoke-Expression $_
        start-sleep -Seconds 10
        Invoke-Expression $_

[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$readExcel) | Out-Null

Open in new window

Razvan MelinteService Desk level 2 support analystAuthor Commented:
Thank you for the script and sorry for the confusion, I thought if I will mention that I need a button that is clear that I need a vba code.
Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
Well nvm... Good luck with it bud I don't work on Vba
What were the top attacks of Q1 2018?

The Threat Lab team analyzes data from WatchGuard’s Firebox Feed, internal and partner threat intelligence, and a research honeynet, to provide insightful analysis about the top threats on the Internet. Check out our Q1 2018 report for smart, practical security advice today!

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It would be much easier to just construct a file containing all the commands, including the 10 seconds wait, and start PowerShell with that.
Razvan MelinteService Desk level 2 support analystAuthor Commented:
Capture1.JPGI've tried to construct a file but when the command is copied from the Excel file , because is a powershell scrip auto-filed from other cell is typed as a formula, and  when I past it to the ps1 file is copied with all the comas as you can see attached file (Capture.jpg) because the ps1 file has the below command :

"dsadd user ""CN=HGTEST01,OU=UsersM2,OU=Accounts,OU=PL2,DC=Domain,DC=net"" -disabled no -pwd Windows2111 -upn -fn ""sds""-ln ""Test"" -mobile  . -display ""sds Test"" -company ""Global Shared Services"" -acctexpires never -Title ""Billing Administrator"" -mgr ""CN=Kosinm01,OU=UsersM2,OU=Accounts,OU=PL2,DC=Domain,DC=net"" -dept ""OTC"" -tel . -mi . -desc ."

Instead of :

 dsadd user "CN=HGTEST01,OU=UsersM2,OU=Accounts,OU=PL2,DC=Domain,DC=net" -disabled no -pwd Windows2111 -upn -fn "sds"-ln "Test" -mobile  . -display "sds Test" -company "Global Shared Services" -acctexpires never -Title "Billing Administrator" -mgr "CN=Kosinm01,OU=UsersM2,OU=Accounts,OU=PL2,DC=Domain,DC=net" -dept "OTC" -tel . -mi . -desc .
Capture.JPGthe script that I used is :
Dim Command As String

Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer

Set rng = Worksheets("HGSS").Cells(3, 23)

myFile = "C:\Users\Admmelinr01\Desktop\test2.ps1"

fnum = FreeFile()
Open myFile For Output As #fnum

    For i = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count
    cellValue = rng.Cells(i, j).Value
    If j = rng.Columns.Count Then
    Write #fnum, cellValue
    Write #fnum, cellValue,
End If

 Next j
Next i

Close #fnum

Call Shell("powershell -noexit -file ""C:\Users\admmelinr01\Desktop\test2.ps1""", vbMaximizedFocus)

Application.Wait (Now + TimeValue("0:00:10"))

Kill myFile

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
dsadd is a commandline tool, so why do you try to run it in PowerShell?
I cannot tell why VBA is enclosing the cell content in double quotes when writing to the file, sorry.
Razvan MelinteService Desk level 2 support analystAuthor Commented:
I need the commandline dsadd to be runed in Powershell so I can automatically created ad AD account.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No, you need to use New-ADUser in PowerShell, or run dsadd in a cmd prompt ;-). But if you insist on using dsadd, replace Write with Print, so text is not getting enclosed in double quotes again before writing into the file.

As said, I recommend to build the PS1 file completely including start-sleep 10 to wait for 10 seconds.
Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
Or you can just export the content in csv and the the values from a single file in ps1. It doesn't have any sense to complicate a simple task like creating a new user like that.
I agree with qlemo
PberSolutions ArchitectCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

-- Razvan Melinte (https:#a42399757)
-- Jose Gabriel Ortega C (https:#a42398302)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Experts-Exchange Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.