Home Image-Pro Plus Automation with Macros

Controlling where you post Excel data

(Originally posted by Laura on 6/13/2006)

When i send my data to excel, it doesn't start at the top of the sheet, but instead writes it further down apparently where it last left off. So i'm stuck with a large amount of blank cells heading my data.

 

Comments

  • (Originally posted by Dean on 6/13/2006)

    You have control of where to place the data into an Excel spreadsheet through the Export Options. If you want it to start at cell A1 each time, choose -

    Position data set at:
    Row: 1 Col: 1
    Increment position for next data set by:
    Row: 0 Col: 0

     

  • (Originally posted by CTMurray on 6/21/2006)

    I would like to hop in as well. I need to use a macro to open a specific Excel spreadsheet. It is working now but opens a new workbook or will add data to my desired workbook if it is already open. Can't figure out how to get it to open a specific file. Here is what I have tried, including making the file name even more detailed - but to no avail.

    ret = IpDde(DDE_SET, "append", "2")
    ret = IpDde(DDE_SET, "row", "2")
    ret = IpDde(DDE_SET, "col", "1")
    ret = IpDde(DDE_SET, "topic", "[Cumberland template.xls]Sheet1")
    ret = IpDde(DDE_SET, "target", "C:\Srccode\o2kpsr1\Disk1\PFiles\MSOffice\Office\EXCEL.EXE")
    ret = IpBlbSaveData("", S_HEADER+S_Y_AXIS+S_STATS+S_DDE)

    Any thoughts

     

  • (Originally posted by Chris Tully on 6/21/2006)


    Please take a look at Solution #703 on the Solutions Zone. This is a macro that demonstrates controlling Excel from an Image-Pro macro via COM, rather than DDE. In particular, it deomstrates putting the data in specific cells, charting data, and working with two workbooks at the same time. This method is slightly more complicated, since you have to extract the data you want to send to Excel from Image-Pro into arrays in your macro, and then send it over to Excel. However, in return for the added complexity, you gain incredible amounts of flexibility and power - you can start with a completely blank spreadsheet, and populate it with the necessary data AND formulae.

  • I need to do a very similar thing with IP10 so would like to look at this old macro to see if it is re-usable. I don't want to reinvent "the wheel". Anyone know where Solutions Zone has gone? Specifically Solution #703 as suggested by NickB.
  • edited March 2023
    Hi ChMi,

    In the new version of Image-Pro the position can be defined in the command, AbsolutePosition property, like this:

        Public Sub ExcelTest
            With  Mediacy.Automation.Measure.Data.ExportCommands.ExportToExcel(Nothing)
                .AbsolutePosition=New System.Drawing.Point(4,2)'X/Y position, Column/Row in the active sheet
                .Run("My text to Excel","")
            End With
        End Sub

    Please check this forum post https://forums.mediacy.com/discussion/comment/2470#Comment_2470 

    Yuri

  • Hi Yuri,

    Thanks for this - a great start. I was hoping to exercise a little more control (automation) from my script.
    1) A shell to Open a specified Workbook based on a path and filename
    2) Test that the Workbook is Open
    3) Create/Select a specified Worksheet as necessary
    4) Output datatable data to specified locations (defined as variables dependent upon object class)
    5) Output corresponding Features Manager and some configuration data to specified cell addresses on the corresponding worksheet

    You get the picture.

    Best,

    Chris
  • edited March 2023
    Hi Chris,

    The ExportToExcel command has other usefull properties, here is the link to the help page: https://help.mediacy.com/Image-Pro/Automation/11/html/T_MediaCy_Commands_Export_ExportToExcel.htm 

    Also, the macro language can execute any VBA Excel commands, if you use Excel.Application object, as in the example below (project attached)

    Imports Microsoft.Office.Interop.Excel
    
    Public Module Module1
    
        Public Sub ExcelTest
                Try
                  Dim excel As new Microsoft.Office.Interop.Excel.Application
                  Dim  book As Microsoft.Office.Interop.Excel.Workbook
                  Dim  sheet As Microsoft.Office.Interop.Excel.Worksheet
                
                  excel.Visible = True
                  book = excel.Workbooks.Add
                  excel.ActiveSheet.Name = "Expenses Report"
                  Dim RowCount As Integer = RowCount - 1
                  For i As Integer = 0 To 9
                    excel.Range("A" + (i + 1).ToString, "A" + (i + 1).ToString).Value = i+1
                  Next
                Catch err As System.Exception
                    MsgBox(err.Message)
                End Try
        End Sub
    
    End Module
    You can find more details about this class on Microsoft website https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.application?view=excel-pia 

    Yuri

  • Thanks Yuri.  Just the information I need.
    Best,

    Chris
Sign In or Register to comment.