Controlling where you post Excel data

NickB

(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.

 

NickB

(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

 

NickB

(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

 

NickB

(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.

ChMi

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.

YuriG

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

ChMi

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

YuriG

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

ChMi

Thanks Yuri.  Just the information I need.
Best,

Chris