within excel, i have a button with the following macro when i click it
Sub MigrateFile_Click()
strCommand = "Powershell -File ""C:Testmigrate.ps1"""
Set WshShell = CreateObject("WScript.Shell")
Set WshShellExec = WshShell.Exec(strCommand)
End Sub
the powershell script looks like
#Read the Excel Data and change path accordingly
$data = Import-Excel -Path "C:TestExcelFile.xlsm" -Worksheet data
#Declare Site URL and List names
$SiteUrl = "https://consato.sharepoint.com/sites/Stage"
$UserName="[email protected]"
$Password = "password"
# Update with the internal name of the SharePoint list.
$ListName = "Documents"
#Initiate a connection to SharePoint site
$SecurePassword = ConvertTo-SecureString -String $Password -AsPlainText -Force
$Cred = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $UserName, $SecurePassword
#connect to sharepoint online site using powershell
Connect-PnPOnline -Url $SiteURL -Credentials $Cred
try {
ForEach($record in $data) {
Write-host "Adding record $($record.'Info')"
Add-PnPFile -Path C:TestExcelFile.xlsm -Folder "Data" -Values @{Title=$($record.'Info')}
}
} catch {
Write-host "Error: $($_.Exception.Message)" -ForegroundColor Red
}
but the script never executes when pressing the button. The script works whenever i run it by itself within powershell. Anyone know what’s missing?