Jump to content

Recommended Posts

  • 2 weeks later...

PowerShell script to convert (Save as) an open excel file to CSV. (AI generated)

This should work at the end of your CMM program.

Create a batch file named "inspection_end.bat"

@echo off
REM Run PowerShell script from the same folder
powershell -ExecutionPolicy Bypass -File "%~dp0inspection_end.ps1"

exit /b

 

Create a ps1 file (PowerShell) named "inspection_end.ps1".

Use the code below.


param(
    [Parameter(Mandatory = $true)]
    [string]$OutputCsvPath,

    [string]$WorksheetName
)

# Excel constants
$xlCSVUTF8 = 62        # CSV UTF-8 (Excel 2016+)
$xlWorksheet = -4167

try {
    # Attach to running Excel
    $excel = [Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")

    if (-not $excel) {
        throw "Excel is not running."
    }

    $workbook = $excel.ActiveWorkbook
    if (-not $workbook) {
        throw "No active workbook."
    }

    # Select worksheet if specified
    if ($WorksheetName) {
        $sheet = $workbook.Worksheets.Item($WorksheetName)
        if ($sheet.Type -ne $xlWorksheet) {
            throw "Specified sheet is not a worksheet."
        }
        $sheet.Activate() | Out-Null
    }

    # Suppress prompts
    $excel.DisplayAlerts = $false

    # Save As CSV (active sheet only)
    $workbook.SaveAs($OutputCsvPath, $xlCSVUTF8)

    Write-Host "Saved as CSV:`n$OutputCsvPath"

} catch {
    Write-Error $_.Exception.Message
} finally {
    if ($excel) {
        $excel.DisplayAlerts = $true
    }

    # Release COM objects
    foreach ($obj in @($sheet, $workbook, $excel)) {
        if ($obj -and [System.Runtime.InteropServices.Marshal]::IsComObject($obj)) {
            [void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($obj)
        }
    }
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
}

Both of these files should be in your CMM program folder (The program you are running).

Edited
missed an instruction
×
×
  • Create New...