Automatic Data Transfer & Collection?

[[Ry...]]

I've been asked to look into seeing if there is a way to automatically transfer measurements collected into premade templates that our company has. At the moment, after a measurement routine is completed - I have it set so that a PDF of the PiWeb report & an excel file are exported automatically to a local folder, which I then transfer to a date/work order labeled folder inside of our part specific folder on our network. After completion of measurements, I open each excel file individually and transfer the data over into our pre made first article inspection reports/capability study reports. This process can take some time.

This is the most efficient way I've found to do this, but I'm wondering if anyone else has a faster way of doing it. I would imagine coding a script or macro would be the way to go. I have no knowledge or experience in coding, so I wouldn't know how to go about creating one. Assuming the way to do this is through code, would I need to create a separate script for each program that I create?

Thanks in advance!

 

[[Je...]]

Hi Ryan.

Yes, I would recommend using a script outside of Calypso that looks at a particular folder, extracts the data of interest and then reorganizes it into the structure you need it to be for reporting.

Here's a tip:  You don't need to know how to code in order to do this.

[[Ri...]]

If it is as simple as copy/pasting data over, you can easily make an Excel Macro for this. 

I'm not an Excel Macro guru, but Google has been a life-saver. Here recently though I've been using Chat-GPT to help code these things for me. I strongly suggest people utilize these tools (unless they are experts of course, lol). 

 

If you cannot just simply copy/paste, you can recreate this template inside of PiWeb. Depending on the report complexity will dictate how complex it will be to recreate this report inside of PiWeb. Feel free to reach out to us, and we can assist with this if you'd like to continue on this path. 

[[DW...]]

Please sign in to view this quote.

This is my passion! Anything that is done repetitively, can be automated. The uphill task here, is you don't have any knowledge or experience. But never mind that, we live in the GPT age!

I script primarily in Powershell. Powershell is a versatile task automation framework and configuration management tool developed by Microsoft. On your machine, in the search bar, type "ISE" and Powershell ISE should come up. Right click, run as administrator.

You will be looking at a blue interface (host), and to the right will be where you write and execute lines of code.

Type this into the open area -

Install-Module -Name ImportExcel

This will install a module called 'ImportExcel' and is very useful and powerful.

Once complete, you can backspace all of the text.

Here is an example of how your next bit of code would look to accomplish your tasks. Everywhere there is a #, is a comment describing what the next section of code is trying to accomplish.

# Define paths
$measurementFolder = "C:\ExportedMeasurements"
$templateFolder = "C:\Templates"
$completedReportsFolder = "C:\CompletedReports"

# Get all Excel files from the measurement folder
$measurementFiles = Get-ChildItem -Path $measurementFolder -Filter "*.xlsx"

foreach ($measurementFile in $measurementFiles) {
    # Load the Excel file
    $measurementData = Import-Excel -Path $measurementFile.FullName

    # Determine the corresponding template (you'll need to modify this logic to match your naming convention)
    $templateFileName = "Template_" + $measurementFile.BaseName + ".xlsx"  # Modify as needed
    $templatePath = Join-Path -Path $templateFolder -ChildPath $templateFileName

    if (Test-Path -Path $templatePath) {
        # Load the template file
        $templateData = Import-Excel -Path $templatePath

        # Perform data transfer (modify this section based on your specific template structure)
        # Example: Assuming the template has a specific location for measurement data
        foreach ($row in $measurementData) {
            # Example: Transfer data from measurementData to templateData
            # You might want to specify the exact cells or columns to copy
            $templateData.YourColumnName = $row.YourMeasurementColumn
        }

        # Export the filled template to the completed reports folder
        $completedReportFileName = "CompletedReport_" + $measurementFile.BaseName + ".xlsx"
        $completedReportPath = Join-Path -Path $completedReportsFolder -ChildPath $completedReportFileName
        $templateData | Export-Excel -Path $completedReportPath -AutoSize

        Write-Host "Processed $measurementFile and created $completedReportFileName"
    } else {
        Write-Host "Template not found for $measurementFile"
    }
}

Write-Host "All files processed."

 

If you do not have ChatGPT yet (get it!) you can go to google and type "TalkAI" and talkai.info/chat/ should be at the top. This is a free no login API. This will help tremendously when you are trying to get your code working.

Unless I had an exact Excel file, and template, I can't narrow it down any more unfortunately. If you are able, you could upload each with private info changed so I there is something tangible to work with.

I have my own scripts that go to a folder containing the standard xls files from the CMM, saves each as an xlsx to get rid of the macros, then strips the header out of all the xlsx files, and finally grabs the actual data I need in column B, and copies it into a single Excel file for easy Copy/Paste into our official Excel reports. I can process 100 files in less than 30 seconds start to finish.

Start small and you will eventually be coding like a pro with the help of GPT!

 

 

[[Ün...]]

Hello

A macro file that combines Excel files that I got from the forum, I used it a few times in the past and it is very successful, it may be useful for you.

You can also combine your files with Power Query in Excel.

 

Birleştir_1.xls

[[Ry...]]

Please sign in to view this quote.

Thank you so much for this very well detailed response. I'm definitely going to feed this into ChatGPT for some specific help with the coding, but I very much appreciate what you have provided as for a direction to start in! I've attached 2 excel files if you'd like to work with them & see what you could do.

 

Thanks to everyone who chimed in!

Part Measurements_01-14-2025 Help_.xls FAI Template_01-14-2025 Help_.xlsm

[[DW...]]

Please sign in to view this quote.

# Define the directory containing the XLS and XLSX 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 template Excel file
$destinationExcelFilePath = "C:\PATH TO YOUR FILE\FAI Template_01-14-2025 Help_.xlsm"

# Open the template file
$destinationWorkbook = $excel.Workbooks.Open($destinationExcelFilePath)
$destinationWorksheet = $destinationWorkbook.Sheets.Item(1)

$columnIndex = 12 # Column L is 12
Get-ChildItem -Path $directory -Filter "*.xlsx" | Sort-Object Name | ForEach-Object {
    $sourceWorkbook = $excel.Workbooks.Open($_.FullName)
    try {
        $sourceWorksheet = $sourceWorkbook.Sheets.Item(1)
        # Get values from B1:B3
        $sourceValues = $sourceWorksheet.Range("B1:B3").Value2
        # Write values to L15:L17 (or M15:M17, etc.)
        $destinationRange = $destinationWorksheet.Range(
            [char]($columnIndex + 64) + "15:" + [char]($columnIndex + 64) + "17"
        )
        $destinationRange.Value2 = $sourceValues
        $columnIndex++ # Move to the next column for the next file
    } finally {
        $sourceWorkbook.Close($false)
    }
}

# Save and close the template file
$destinationWorkbook.Save()
$destinationWorkbook.Close($true)

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

 

 

This is a Powershell script that goes to the directory where you have your XLS, converts them to XLSX format (to remove macros), then removes the headers, and finally looks at the XLSX files with no headers and grabs the information in B1:B3 and sends it your template, filling in rows L15:L17, and iterating for each file/new row. I made three XLS files and made up data and it works correctly. It looks like in your template you can do up to 10? samples via the comment, but I only see three columns (L, M, N) for data? Let me know if you can run this script and correlate results.

 

 

[[DW...]]

Please sign in to view this username.

 Did you get to run this code yet?

[[Ry...]]

Please sign in to view this username.

 Sorry no I haven't. My work load is beyond measurable at the moment so this task has been assigned to someone else at our company. Thank you for your input though, it is much appreciated and has not gone un noticed. I will send this thread to whoever works on it

[[Ky...]]

Through Calypso it is not possible to automatically send to a particular folder outside of the default saving location defined in your environment settings.

 

The PiWeb report, however, is able to be saved elsewhere. If you don't need it in that local folder, you can use the information in this document to set the default saving location for that report to that date/work folder:

https://portal.zeiss.com/knowledge-base?id=587888

[[Ja...]]

What you are trying to accomplish is something that I have been doing for a really long time. A coding file for this has already been created. I got it from this forum a long time ago. First, there is a way to automatically save your excel file to where ever you want it for each program/part number using a batch file. save excel report.pptx Then, there is a file that will combine them all into one excel file so you can copy and paste them all at the same time.Merge.xls A lot of people said they prefer exporting to a table file. I never actually got that working so I don't know if that works better or not.

[[DW...]]

Please sign in to view this quote.

Not trying to take away from the .xls file, but that is a very outdated Excel macro file that shouldn't be used any more for security reasons. There are much safer and powerful languages to script in (Powershell, Python, etc).

Please sign in to view this username.

 The script provided is legit, and will save you an insane amount of time compiling.

[[Ja...]]

I believe you. I would like to try your method myself when I get time.