Excel Report Not Generating Using Characteristic Template

[[Kr...]]

Hello,

I am unable to generate an Excel report from ZEISS CALYPSO using the characteristic template. My goal is to combine multiple report datasets into a single Excel sheet, similar to process study data.

The report only displays the header information (Measurement Plan, Date, Drawing No., Part No., etc.), but the characteristic results (Actual, Nominal, Tolerance, Deviation) are missing. Could this be related to the template configuration? Kindly advise if any settings or steps need to be corrected to include the characteristic results.

Thank you in advance for your support.

[[DW...]]

Please sign in to view this username.

 Did you select the Characteristics.xlt when setting up your Excel output? If you want to compile many Calypso XLS files into one easy to copy and paste XLSX block, use the following PowerShell script to do that instead of trying to do anything with the Zeiss macro enabled template. It is important to note the naming convention of your files (with incremental part number) needs to be something like _001, _002, _003...._010, _011, _012 etc so when the script sorts by ascending, you don't get file names sorted like _1, _10, _2, _3, etc.

This is a PowerShell script that once you give it the path to your folder containing the XLS files, it manipulates them with the final result being a file called "compiledExcel.xlsx" located in the same folder. Copy and paste the block of data into whatever official report you have, and that's it. Massive amounts of data compiled quickly and accurately. This script can be further refined, but the heavy lifting is here.  

 

<#
.SYNOPSIS
Converts .xls to .xlsx, trims specified ranges, and compiles data into a master Excel file.
#>

param(
    [Parameter(Mandatory = $false)]
    [string]$Directory = "C:\PATH TO YOUR FILES"
)

# --- Helper: Logging function ---
function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $timestamp = (Get-Date).ToString("yyyy-MM-dd HH:mm:ss")
    Write-Host "[$timestamp][$Level] $Message"
}

# --- Verify directory ---
if (-not (Test-Path $Directory)) {
    Write-Error "Directory not found: $Directory"
    exit 1
}

Write-Log "Processing files in directory: $Directory"

# --- Start Excel safely ---
try {
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
    $excel.AutomationSecurity = 3  # Disable macros
    Write-Log "Excel instance started successfully."
} catch {
    Write-Error "Failed to start Excel COM object. Ensure Excel is installed. $_"
    exit 1
}

# --- Function: Safely close and release COM object ---
function Release-ComObject {
    param($obj)
    if ($null -ne $obj) {
        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($obj)
        Remove-Variable obj -ErrorAction SilentlyContinue
    }
}

# --- Convert .xls to .xlsx ---
Write-Log "Converting .xls files to .xlsx..."
Get-ChildItem -Path $Directory -Filter "*.xls" -ErrorAction SilentlyContinue | ForEach-Object {
    $xlsFile = $_.FullName
    $newFileName = [System.IO.Path]::ChangeExtension($xlsFile, ".xlsx")

    try {
        Write-Log "Converting $xlsFile..."
        $workbook = $excel.Workbooks.Open($xlsFile)
        $workbook.SaveAs($newFileName, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
        $workbook.Close($false)
        Write-Log "Converted: $xlsFile ? $newFileName"
        # Uncomment next line to delete original
        # Remove-Item $xlsFile -Force
    } catch {
        Write-Warning "Failed to convert $xlsFile $($_)"

    } finally {
        if ($null -ne $workbook) {
            Release-ComObject -obj $workbook
        }
    }
}

# --- Process .xlsx files (remove A1:H13) ---
Write-Log "Trimming range A1:H13 from all .xlsx files..."
Get-ChildItem -Path $Directory -Filter "*.xlsx" -ErrorAction SilentlyContinue | ForEach-Object {
    $xlsxFile = $_.FullName
    try {
        Write-Log "Processing $xlsxFile..."
        $workbook = $excel.Workbooks.Open($xlsxFile)
        $worksheet = $workbook.Sheets.Item(1)
        $worksheet.Range("A1:H13").Delete()
        $workbook.Save()
        Write-Log "Trimmed file: $xlsxFile"
    } catch {
        Write-Warning "Error processing file $xlsxFile $_"
    } finally {
        if ($null -ne $workbook) {
            $workbook.Close($false)
            Release-ComObject -obj $workbook
        }
    }
}

# --- Compile into one Excel file ---
Write-Log "Compiling data into master file..."
$destinationExcelFilePath = Join-Path $Directory "compiledExcel.xlsx"

if (Test-Path $destinationExcelFilePath) {
    Remove-Item $destinationExcelFilePath -Force -ErrorAction SilentlyContinue
    Write-Log "Removed existing compiledExcel.xlsx"
}

try {
    $destinationWorkbook = $excel.Workbooks.Add()
    $destinationWorksheet = $destinationWorkbook.Sheets.Item(1)
    $columnIndex = 1

    Get-ChildItem -Path $Directory -Filter "*.xlsx" | Sort-Object Name | ForEach-Object {
        $sourcePath = $_.FullName
        try {
            Write-Log "Copying data from $sourcePath"
            $sourceWorkbook = $excel.Workbooks.Open($sourcePath)
            $sourceWorksheet = $sourceWorkbook.Sheets.Item(1)
            $sourceValues = $sourceWorksheet.Range("B1:B1000").Value2
            if ($sourceValues) {
                $destinationWorksheet.Cells.Item(1, $columnIndex).Resize(1000, 1).Value2 = $sourceValues
                Write-Log "Copied column $columnIndex from $sourcePath"
                $columnIndex++
            } else {
                Write-Warning "No data found in B1:B1000 for $sourcePath"
            }
        } catch {
            Write-Warning "Error reading from $sourcePath $_"
        } finally {
            if ($null -ne $sourceWorkbook) {
                $sourceWorkbook.Close($false)
                Release-ComObject -obj $sourceWorkbook
            }
        }
    }

    $destinationWorkbook.SaveAs($destinationExcelFilePath)
    Write-Log "Compiled workbook saved to $destinationExcelFilePath"
} catch {
    Write-Error "Error compiling Excel files: $_"
} finally {
    if ($null -ne $destinationWorkbook) {
        $destinationWorkbook.Close($true)
        Release-ComObject -obj $destinationWorkbook
    }
}

# --- Cleanup Excel ---
Write-Log "Cleaning up Excel COM objects..."
try {
    $excel.Quit()
    Release-ComObject -obj $excel
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
    Write-Log "Excel closed and resources released."
} catch {
    Write-Warning "Error during Excel cleanup: $_"
}

Write-Log "Script completed successfully."

Edited yesterday at 07:11 PM