Home Image-Pro Automation (Macros, Apps, Reports)

Different Results from OUTPUT WINDOW --> EXCEL using EXPORT TO EXCEL, SAVE, and COPY

Experts --

I am getting different results within EXCEL when it receives data from the OUTPUT WINDOW via the EXPORT (DDE), SAVE (TXT FILE), and COPY (CLIPBOARD) options.

The EXPORT option results in all of the data ending up on one row.

The SAVE option ends up the rows and columns that I want but a special character showing up in as "(µm)" rather than a plain "(µm)". 

The COPY / PASTE option (illustrated by last image -- 2013-07-31-152537 -- Copy and Paste Results.jpg) gets me the results that I want but it requires user action to make this happen.

I've tried various combinations of VBLF, VBCR, and VBCRLF without getting the combination that will work for me.

Right now I would say that if a solution for the SAVE (TXT FILE) option can get rid of the "special character" that is being interpreted by EXCEL then I can accomplish most of what I want to do.

Ultimately, it would be great to get a link between IPP9 and EXCEL up and running but I think the TXT file option is the best for now.

Thanks.

-- Matt

Best Answers

  • Answer ✓
    Yes, Excel opens the file as UTF-7 by default (from drag and drop), however UTF-8 is the format that allows to encode characters like µ properly so we can't win on both fronts. You just have to use File|Open in an Excel more recent than 2000 or use the macro I sent earlier to push the data to Excel.

    Pierre
  • Answer ✓
    Matt,

    Thank you for all the feedback, it looks like IPP7 used Unicode encoding when saving the output window content, which Microsoft Excel likes better, so we'll make sure to do the same for Premier 9.1.

    Pierre
  • Answer ✓
    Hi Matt,

    You can find additional info in the Automation Reference help (in the help button drop-down menu in the scripting workbench).

    The full command name is MediaCy.Automation.Measure.Measurements.TableCommands.SendToExcel.

    and MediaCy.Automation.Measure.Data.ExportCommands.ExportToExcel

    Yuri

Answers

  • edited July 2013

    Matt,

    I am filing a bug for the Output Window "Export To Excel", so that it should be fixed in Premier 9.1.

    With regard to the special character, it's likely that it comes from the way that you inserted the (µm) as we don't do any special processing: the text is just sent as is, and then formatted in Excel.

    Note that we mostly use the Output Window as a quick way to output text, but usually not as a presentation/formatting tool. So what I can suggest for now is to use a macro such as the following which will fully automate the data transfer to Excel.

    The command used below has a number of properties that we can discuss if you need more control over the process.

        Sub ExportTableToExcel
            Dim table As New System.Data.DataTable
            table.Columns.Add("Region")
            table.Columns.Add("Feature")
            table.Columns.Add("Box Length (μm)")
            table.Columns.Add("Cent. X (μm)")
            table.Columns.Add("Cent. Y (μm)")
            table.Rows.Add("A",1,1.49,4.00,0.72)
            table.Rows.Add("B",1,1.49,4.00,0.72)
            table.Rows.Add("C",1,1.49,4.00,0.72)
            With Measure.Data.ExportCommands.ExportToExcel(Nothing)
                .Run("Table Title",table)
            End With
        End Sub
  • Pierre --

    Thank you for your prompt response.

    I'm sure this example for sending data directly to EXCEL without the stop in the OUTPUT WINDOW very helpful.

    The following are the statements that control the generation of the "units" text that EXCEL is interpreting with a special character when the OUTPUT WINDOW CONTENTS are SAVED then OPENED with EXCEL.

            'Learn the abbreviation for the active calibration unit
            MySpatialCalibrationUnitAbbrev = ThisApplication.ActiveImage.SpatialCalibration.UnitAbbrev
    
    
            'Build the string that will send the header row to the output window
            MyString = _
                "" & vbTab & _
                "" & vbTab & _
                "" & vbTab & _
                "" & vbTab & _
                "" & vbTab & _
                vbLf & _
                "Region" & vbTab & _
                "Feature" & vbTab & _
                "Length " & vbTab & _
                "X " & vbTab & _
                "Y " & vbTab & _
                vbLf & _
                "" & vbTab & _
                "" & vbTab & _
                "(" & MySpatialCalibrationUnitAbbrev & ")" & vbTab & _
                "(" & MySpatialCalibrationUnitAbbrev & ")" & vbTab & _
                "(" & MySpatialCalibrationUnitAbbrev & ")" & vbTab & _
                ""
    Is is possible that

            ThisApplication.ActiveImage.SpatialCalibration.UnitAbbrev

    is putting in a special character that is being ignored / stripped by NOTEPAD (on viewing) and CLIPBOARD (during copy / paste) when the IPP9 generates the TXT but seen by EXCEL?

    After a bit more testing (please see attached image of EXCEL showing 3 TXT files from same image with 3 different calibrations), the "special" character does not show up with the image is uncalibrated (I set the units abbrev as "pix", or when the image is calibrated in millimeters (IPP9 reports the units abbrev as "mm") so it looks like there is an issue with micrometers / μm.

    Your information will help me.  I hope my information will help you.

    Thanks again.

    -- Matt
  • Rod,

    If I try your code with one of our calibrated images (in μm) I am not getting the extra symbol, would you be able to email me the image causing troubles?

    Pierre
  • Pierre --

    I've attached (and will e-mail) 2 tif, 1 txt, 1 xlsx files.

    I hope this gives you the resources to investigate this.

    Thanks.

    -- Matt


  • For those folks following this discussion / question, I goofed when I created

        Calibrated Example for PD.tif

    and I forgot to save it after calibrating it.

    I've fixed that and there is a new version named

         Calibrated Example for PD - 2nd Attempt.tif

    that is calibrated.

    Sorry for the confusion!

    -- Matt
  • Oh OK now I see, the problem is not with the calibration but with the encoding of the file saved from the output window, which is UTF-8. When you open the file in Excel, you have to make sure to select that option because it seems to use UTF-7 by default.image
  • Pierre --

    I think you are homing in on the problem.

    When I do the FILE OPEN within EXCEL, UTF-8 seems to be the DEFAULT and the file opens with the

        (µm)

    When I do a DRAG AND DROP into EXCEL or do a SEND TO EXCEL from a TXT FILE ICON, the file opens with the

        (µm)

    I do not understand what is going on "behind the scenes" but when the TXT file generated by IPP9 in this manner is opened with EXCEL 2000, none of the three choices (MACINTOSH, WINDOWS ANSI, or MSDOC PC-8) generate the desired

        (µm)

    Please see attached

        2013-08-01-143631.JPG

    It looks like there is something "undesirable" within the TXT file.

    This is supported by a DOS TYPE command (please see -- 2013-08-01-144717.jpg) that shows that the issue may be a font or something connected to the µ character.

    -- Matt
  • Pierre --

    Thank you for looking into this matter and determining why the "µ" character from the MICROMETER CALIBRATION ABBREVIATION looks different in EXCEL depending on the path it takes while moving from the the IPP9 OUTPUT WINDOW to EXCEL 2013.

    I've tested the a similar QUERY CALIBRATION + PRINT TO OUTPUT WINDOW + SAVE TXT FILE + DRAG AND DROP INTO EXCEL using WIN XP PRO, IPP7, and EXCEL 2000 and the "µ" character makes the trip from IPP7's CALIBRATION INFORMATION all the way to EXCEL 2000 (and 2013) successfully.  The TXT file generated by IPP7's OUTPUT WINDOW is attached.

    I'm sure there are bigger issues to resolve within IPP9 but the symptoms of this one may be worthwhile to keep in mind if other goofy things happen with "special characters".

    I'll tackle using TABLES rather than the OUTPUT WINDOW after I get all of the other features of my software working well.

    Thanks again.

    -- Matt
  • edited August 2013
    Hi Matt,

    If you just want to sent measurement table to Excel including calibration units, you can simply use Measurements  SendToExcel command (recorded when you click the Excel button in measurements table):

        Public Function SendMeasuremetnsToExcel() As SimpleScript
            SendMeasuremetnsToExcel = New SimpleScript
            Dim doc1
    
            With Application.DocumentCommands.Active(SendMeasuremetnsToExcel)
                .Run(doc1)
            End With
    
            With Measure.Measurements.TableCommands.SendToExcel(SendMeasuremetnsToExcel)
                .UseStatistics = False
                .Run(doc1)
            End With
    
        End Function
    

    It sends um abbreviation properly.

    Regards,

    Yuri

  • Yuri --

    I'm not sending everything in the MEASUREMENT DATA TABLE to EXCEL but I'm sure that your EXAMPLE CODE will come in handy when I need to do that.

    Can you direct me to the appropriate HELP FILE to understand what I can do with

    Measure
    .Measurements.TableCommands.SendToExcel
    (from your example)

    and

    Measure.Data.ExportCommands.ExportToExcel
    (from Pierre's example)

    Thanks.

    -- Matt
  • Yuri --

    Thank you for the directions to the help on this function.

    When I do an F1 - HELP with SENDTOEXCEL highlighted, the MACRO SCRIPTING HELP appears with the SENDKEYS INSTRUCTION highlighted.

    I can manually open the AUTOMATION REFERENCE HELP and then go to the SEARCH TAB and then put SENTTOEXCEL in to the search box and then I get the info about this PROPERTY.

    Is there a more streamlined way to get from SENDTOEXCEL (or other AUTOMATION keywords) to the appropriate place in the AUTOMATION REFERENCE HELP FILE?  Perhaps a SHIFT + F1 brings up a help file about WINWRAP BASIS and ALT + F1 and CTRL + F1 don't seem to do anything.

    Thanks again.

    -- Matt
Sign In or Register to comment.