Forum Discussion

SESA758156's avatar
SESA758156
Copper Contributor
Aug 08, 2024

How to suspend execution until excel workbook 100% synced with Microsoft forms

I have a PowerShell script that's extracting cell values from a table that's synced to a Microsoft Form. However, when I run the script in the morning it's extracting the cell values before the forms is 100% synced so I'm not getting all of the data from the day before.

 

I can sleep the script for maybe 20 seconds but is there a way to suspend execution of the script until the forms is 100% synced then immediately continue execution because sometimes it's already synced and other times it's not?

 

 

 

# create excel instance
$excel = new-object -comobject excel.application

# open excel workbook
try {
    $workbook = $excel.Workbooks.Open($filePath, $true)
} catch {
    throw "microsoft excel cannot access file -> please run the script again"
}

# set excel worksheet
$worksheet = $workbook.Sheets.Item(1)

# set excel table
$table = $worksheet.ListObjects.Item("Form")

# extract cell values
$values = @()
for ($i = 9; $i -le 15; $i++) {
    foreach ($cell in $table.Range.Columns.Item($i).SpecialCells(12).Cells) {
        $values += $cell.Value2 | Where-Object { $_ -match "^\d" }
    }
}

 

 

No RepliesBe the first to reply

Resources