Moving Excel chart to a specific location in the Active sheet

[lms17]

Hi,

I want to move an Excel chart that's already created, to a specific cell in the sheet. I am able to move the chart around with Shapes.IncrementLeft and such commands but I want to move the chart to a specified cell. I created a macro to cut and paste the chart to a certain cell. The code is below:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Range("A14").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Paste

I have 2 issues with this in Labview however.

For the command "ActiveSheet.ChartObjects("Chart 1").Activate", I can't seem to implement this as I don't see the Activate option under ChartObjects.

The 2nd issue is that I get an error with the command "ActiveWindow.WindowState = xlNormal". I have attached a .jpg of the code I'm using in Labview. Any help appreciated.

Rgds,

lms17

[Jeremy_T]

Hi lms17

I hope you're having a good day. I've been taking a look at this for you and just had a couple of questions. What error are you getting? Is there an error code that appears? If so, I can look it up more specifically and try to find a solution. If you could attach a screenshot of the error then that could be very handy. Also, is there any chance you could attach the VI you've created so that I can run it on my machine and see whether I get similar issues cropping up?

Look forward to hearing back from you, have a great weekend!

Kind Regards

[lms17]

Hi Jeremy,

I've done up a small VI similar to my application. I came across another issue while doing this however. I have attached the VI and a dud Excel sheet that I was testing it on. In the dud Excel sheet I just created a standard chart as an object in the worksheet. I couldn't select this however in my Labview program. I was getting an error "invalid index" when trying this. I tried a number of things but none worked.

Anyhow, I have a breakpoint in the VI. If you run the VI to the breakpoint and while the VI is in pause mode, manually select the chart in the Excel application, then continue the VI my original error will pop up. I have also made the error the default value in the error out cluster.

I have come across a better way of moving a chart in Excel rather than cutting and pasting it too. I have included this in the VI in the disabled diagram. It would still be good to know how to perform the cut and paste operation however along with activating a chart object in an Excel application that had a pre-created chart. Any help appreciated.

Thanks,

lms17

[smercurio_fc]

Using the macro recorder is great for figuring out the sequence of commands to use to navigate the ridiculously complex Office classes. In this case, however, it leads you down the wrong path since the macro recorder takes a shortcut that you will not be able to take when you try to do this programmatically. The macro recorder took advantage of having a chart selected, so you got the ActiveChart.ChartArea.Select constructs. Doing it programmatically you need to get a ChartObject class and then access the Chart property to get the chart. Something like this:

 

Message Edited by smercurio_fc on 04-21-2009 09:51 AM

[Michael_Munroe]

I suggest that you remove the three activewindow commands from your code.  These appear to be from a recorded macro and serve no purpose in your chart manipulation.  The error may be that you cannot maximize a window that is not visible. 

ActiveWindow.Visible = False
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
 

[lms17]

 Hi Smercurio_fc,

I have tried your solution but once I have the chart refnum, I get an error:

Error -2146827284 occurred at Unknown System Error in add_more_series_to_a_chart.vi

This error code is undefined. No one has provided a description for this code, or you might have wired a number that is not an error code to the error code input.

It seems that I can't get a proper refnum for the chart. Any help appreciated. I have attached the VI I'm working with.

Rgds,

lms17

[lms17]

Hi Michael,

Thanks for the suggestion. I have removed these from the VI.

Rgds,

lms17

[J.Mamakos]

I have done this very thing before using activex within LabVIEW. If you just give me a moment, i'll get pictures up of the section of code you'll need. (i'm not allowed to share my VIs)

James

[J.Mamakos]

The first picture is of a simple VI I've got which adds a new workbook, though you can use the 'index' method of the invoke node instead and specify an existing workbook (name as a string, or the index as a number).

 The second picture is part of another VI that deals with my chart appearance and placement within the workbook.  You could use all the code that's shown but to just place a chart in relation to specific cells, you only need the section i shall describe now.

I've returned the distance from the left and the top of the sheet (in relation to the graph) and then inputted that to the ChartObject property node.  Where there is a pink SubVI called 'Define Cell', you can just input a string specifying the cell in the top left corner of the area you want the graph, in the form 'A1'.  For your purpose, the mathematical operators and the lower of the two identical bits of code are unnecessary, as are the 'Frequency', 'Strings', 'i/p level Out' controls.  The 'Index' function of the top left invoke node need only have a simple string constant/control input.

I do not know how much previous experience you've had with LabVIEW, but if you want/need any/all of this code explained, I'd be happy to do so.  The appearance may also be different to what you're used to since i use LabVIEW 6.1

[J.Mamakos]

If you wish to use the 'Define Cell' custom VI shown in the code, I've included pictures of it (one with it's case statement 'true' and one with it 'false').  It's inputs are the numbers of the row and column which the cell is in.  For example, if you wanted to define cell 'C5', the column input would be 3, and the row input would be 7.  This operation allows you to specify cells programatically and mathematically.

There are multiple ways to access a chart in excel (Shape, Object, ChartObject, Chart) but for this purpose, the ChartObject layer is the most appropriate.

As i said before, if you need anything explaining, please ask.

Hope this helps.

James

Message Edited by James Mamakos on 04-28-2009 04:53 AM