Need help to automate some commands from Excel to Powershell

Hello,

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
-Z3
-AA3
-AB3


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

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 CastroCEO Faru Bonon IT - EE Solution ExpertCommented:
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
$WorkSheetsName=@()
foreach($item in $workBook.Worksheets){
	$WorkSheetsName+=$item.Name
}

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

$row=3
$scripts=@()

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
$j=1;
$scripts | %{
    if($j -eq 1){
        Invoke-Expression $_
        start-sleep -Seconds 10
    }
    else{
        Invoke-Expression $_
    }
    $j++
}

$readexcel.Quit()
[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 CastroCEO Faru Bonon IT - EE Solution ExpertCommented:
Well nvm... Good luck with it bud I don't work on Vba
Virus Depot: Cyber Crime Becomes Big Business

The rising threat of malware-as-a-service is not one to be overlooked. Malware-as-a-service is growing and easily purchased from a full-service cyber-criminal store in a “Virus Depot” fashion. View our webinar recording to learn how to best defend against these attacks!

Qlemo"Batchelor", 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 HGTEST01@Domain.net -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 HGTEST01@Domain.net -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
Else
    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
Qlemo"Batchelor", 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.
Qlemo"Batchelor", 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 CastroCEO Faru Bonon IT - EE Solution ExpertCommented:
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:

Split:
-- 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.

Pber
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
Powershell

From novice to tech pro — start learning today.