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

mbatchelor

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

PierreD

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

PierreD

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

YuriG

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

PierreD

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

mbatchelor

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

PierreD

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

mbatchelor

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

mbatchelor

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

PierreD

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.

mbatchelor

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

mbatchelor

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

YuriG

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

mbatchelor

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

mbatchelor

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