Results to spreadsheet

[[Mi...]]

Hi all.  I'm performing a 30 piece capability study.  I've figured out how to get individual inspection reports in Excel, but is there a way to make all 30 pieces report to the same spreadsheet? Ive searched this new forum but coming up empty.

 

Mike

[[To...]]

 

[[Ow...]]

Excel_Merge.xls

[[Ow...]]

Use the excel macro I attached above to consolidate all the excel reports from one plan.

[[Mi...]]

Nice. I saved the runs out in pdf format, Im thinking the macro will only work to combine the files if I saved them out as xls sheets. 

They did not tell me they wanted to xls for the results. 

[[To...]]

Please sign in to view this quote.

Yes, this looks like the solution if you've already run the parts.

[[Ow...]]

Please sign in to view this quote.

You can rerun the actuals with the excel file chosen, choose the xls excel file, xlsx gives me trouble sometimes. Don't forget to uncheck print PDF reports or you'll kill a lot of trees.

Go to plan and choose subsequent evaluation of several measurements.

[[Mi...]]

I got the xls sheets all saved in a folder. I click combine and choose the path where the said folder is located on my desk top. I then get a DONE message. Where does it put the combined xls sheets? Or am I doing the combining wrong?

I must be missing something here lol.

 

 

[[Ow...]]

Please sign in to view this quote.

It should open up a new excel document with all the information combined that will look something like shown in this pic.

Ooops, I did type the wrong extension😒, you should use the "C:\Users\Public\Documents\Zeiss\CALYPSO\data\excel_report\Characteristic.xlt" not xls. You'll have to rerun the actuals to get xlt.

Go to Resources and select  'define report' and select the Characteristic.xlt file (if using an much older version of Calypso, the file path will be different than above)

I use it a lot and occasionally it does have hiccups and I have to restart my computer but, works great most of the time.

 

 

Edited April 1, 2025

[[DW...]]

Please sign in to view this quote.

I am placing this here in case anyone runs into this in a future search. This is a PowerShell script that looks at a directory containing the XLS files from a location, opens and saves each file as XLSX to strip the macros, opens and saves each file after deleting the header, and then iterates through each XLSX file copying the desired data and appending a new compiledExcel.xlsx. Once complete, you just open the newly created compiled file, and copy/paste into your report. If you run the same template for all reports, this can be amended to go directly into those templates.

This is a little more advanced than the aforementioned XLS macro file, but can be tweaked to be very helpful.

 

# Define the directory containing the XLS files
$directory = "C:\PATH TO YOUR FILES"

# Create a new Excel application instance
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# Convert .xls files to .xlsx
Get-ChildItem -Path $directory -Filter "*.xls" | ForEach-Object {
    try {
        $workbook = $excel.Workbooks.Open($_.FullName)
        $newFileName = [System.IO.Path]::ChangeExtension($_.FullName, ".xlsx")
        $workbook.SaveAs($newFileName, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
        $workbook.Close($false)
        Write-Host "Converted: $($_.FullName) to $newFileName"
        # Uncomment the next line to delete the original .xls file
        # Remove-Item $_.FullName -Force
    } catch {
        Write-Host "Failed to convert $($_.FullName): $_"
    }
}

# Process .xlsx files
Get-ChildItem -Path $directory -Filter "*.xlsx" | ForEach-Object {
    try {
        $workbook = $excel.Workbooks.Open($_.FullName)
        $worksheet = $workbook.Sheets.Item(1)
        $worksheet.Range("A1:H13").Delete()
        $workbook.Save()
        $workbook.Close()
    } catch {
        Write-Host "Error processing file $($_.FullName): $_"
    }
}

# Compile data into a new Excel file
$destinationExcelFilePath = Join-Path $directory "compiledExcel.xlsx"
if (Test-Path $destinationExcelFilePath) {
    Remove-Item $destinationExcelFilePath -Force
}
$destinationWorkbook = $excel.Workbooks.Add()
$destinationWorksheet = $destinationWorkbook.Sheets.Item(1)

$columnIndex = 1
Get-ChildItem -Path $directory -Filter "*.xlsx" | Sort-Object Name | ForEach-Object {
    $sourceWorkbook = $excel.Workbooks.Open($_.FullName)
    try {
        $sourceWorksheet = $sourceWorkbook.Sheets.Item(1)
        $sourceValues = $sourceWorksheet.Range("B1:B946").Value2
        $destinationWorksheet.Cells.Item(1, $columnIndex).Resize(946, 1).Value2 = $sourceValues
        $columnIndex++
    } finally {
        $sourceWorkbook.Close($false)
    }
}

$destinationWorkbook.SaveAs($destinationExcelFilePath)
$destinationWorkbook.Close($true)

# Quit Excel and clean up
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()