CREATING EXCEL CHATS

[NW]

I need to export data to an Excel sheet in order to produce a chart for that data.
The problem I have at present is that I do not know how to refer to variable rows
in SetSourceData. (See line 28 where *** can be any no depending on the no of records Returned by the query in line 11). Is there a way to use variables in row/col ranges instead of fixed col/rows? (i.e. "A2:B99&quot.

Any help on this will greatly appreciated.
Thank you for you time & help.
NW


1. objExcel = CreateObject("Excel.Application&quot
2. ObjBook= ObjExcel.WorkBooks.Open("c:\reports\graph.xls&quot
3. objexcel.visible = .t.
4.
4. With objSheet
5. .Range("A1&quot.Value = "Namer"
6. .Range("B1&quot.Value = "Count"
7. EndWith
8.
9. **** SQL STATMENT FOR DATA....
10.
11. SELECT NAME, COUNT(NAME) AS COUNT1 INTO CURSOR cCursor
12. SELET cCursor
13.
14. nRow = 2
15.
16. SCAN
17. With objSheet
18. .Cells(nRow, 1).Value = cCursor.name
19. .Cells(nRow, 2).Value = cCursor.Count1
20. EndWith
21. nRow = nRow + 1
22. ENDSCAN
23.
24. With objExcel
25. .Charts.Add
26. With .ActiveChart
27. .ChartType = xlColumnClustered
28. .SetSourceData(objExcel.Sheets("Sheet1&quot.Range("A2:B***&quot, xlColumns)
29. EndWith
30. ENDWITH
[sig][/sig]

 

[jonscott8]

Hi NW,

objExcel = CreateObject("Excel.Application&quot

With objSheet
.Range("A1&quot.Value = "Namer"
.Range("B1&quot.Value = "Count"
EndWith

Typo, I assume??

With objExcel
.Charts.Add
With .ActiveChart
.ChartType = xlColumnClustered
.SetSourceData(objExcel.Sheets("Sheet1&quot.Range("A2:B***&quot, xlColumns)
EndWith
ENDWITH

Does this work for you?? I didnt think you could next WITH statements.


To answer your question:

lcRange='A2:B'+TRANSFORM(RECCOUNT())
.SetSourceData(objExcel.Sheets(&quot;Sheet1&quot.Range(lcRange), xlColumns) [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>Focus on the solution....Not the problem.[/sig]

 

[NW]

Hi Jon
Thanks for the answer. I'll try it.
NW [sig][/sig]

 

[suha]

HELLO
HOW ARE YOU, WHILE I'M SEARCHING FOR EXCEL CHARTS I FOUND YOUR QUESTION AND IT'S VERY NEAR TO WHAT I NEED SO I TOOK YOUR EXAMPLE AND COPY IT TO A PROGRAM AND TRY TO RUN IT IN VISUAL FOXPRO but while running the program it doesn't accept the written parameters as range or activesheet, how can i run such example is this a macro on excel or a special ole language or a class can you help me by explaining how can i use this example im my form to accomplish my work and what do i have to learn to have such graphs in my application.

thanks for you cooperation and best regards

SUHA [sig][/sig]

 

[jonscott8]

Hi Suha,

FWIW, in NW's code snippet above, his references to objSheet should be replaced with objExcel.

What version of excel are you using? version of VFP?

written parameters as range or activesheet

Unless my eyes deceive me, there is no reference to ActiveSheet in the above code.

Post your code and maybe someone will be able to help you. [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>The World Is Headed For Mutiny.....When All We Want Is Unity. - Creed[/sig]

 

[jfherring]

Just wondering if you have a header file, or where I can find what the VBA Excel constants are defined as. For example, I am trying o do the same thing, except with a pie chart. The line of code:
objExcel.ActiveChart.ChartType = xlColumnClustered

does not work, xlColumnClustered is not defined. Do you have a definition of what xlColumnClustered, and possible, what xlPie is?
Thanks,
John [sig][/sig]

 

[jonscott8]

Hi John,

xlColumnClusted=54
xlPie=5
xl3DPie=-4102

FWIW, these constants were extracted from Excel97, but I would presume they are the same as Excel2000.

I would highly recommend using VB's object browser for exploring COM Object libraries. If you arent familiar with it, explore the VB help file.

As a brief introduction:

In VB, create a new project. (You dont have to save the project, but in order to have access to the object browser, a project must be open)

Select <References...> from the Project Menu.

In the Available References listbox, select <Microsoft Excel 8.0 Object Library> (9.0 if you have Excel 2000). Click OK.

Press F2 or select <Object Browser> from the View Menu.

In the top drop-down box, select Excel. You will now be viewing the Excel Object Library. You can now view PEMs of the member classes.

Good luck. [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>The World Is Headed For Mutiny.....When All We Want Is Unity. - Creed[/sig]

 

[suha]

HI AGAIN

JON I'M USING EXCEL97 AND VFP6 BUT THE PROBLEM THAT I USED THE SAME EXAMLE OF (NW) AND AS I TOLD YOU I RUN IT AS A PROGRAM IN VFP6 BUT IT DIDN'T ACCEPT ALL WHAT IS WRITTEN.

UNTILL NOW I DON'T UNDERSTAND DO I HAVE TO RUN THIS PROGRAM UNDER VB AND HOW I RELATE IT TO VISUAL FOXPRO,
TO GET THE CHART BY EXCEL UNDER VFP APPLICATION.

THANKS FOR YOUR HELP

SUHA [sig][/sig]

 

[jonscott8]

Hi Suha,

AS I TOLD YOU I RUN IT AS A PROGRAM IN VFP6 BUT IT DIDN'T ACCEPT ALL WHAT IS WRITTEN.

Did it trigger an error? Is so, what was the error? Do you know the offending line of code?

DO I HAVE TO RUN THIS PROGRAM UNDER VB

The above code is native VFP code using Excel Automation. If you know little of COM Automation, I would recommend reading a white paper or article on the subject to familiarize yourself with exactly what is happening.

A few notes of interest(presuming you did an EXACT cut-n-paste of the above code):

ObjBook= ObjExcel.WorkBooks.Open(&quot;c:\reports\graph.xls&quot

1. Does this excel spreadsheet exist on your machine?

2. As I previously mentioned, all objSheet references should be changed to objExcel.

SELECT NAME, COUNT(NAME) AS COUNT1 INTO CURSOR cCursor

3. This SELECT query is errant because it contains no FROM clause. ie, SELECT NAME, COUNT(NAME) AS COUNT1 FROM MyTable INTO CURSOR cCursor

SELET cCursor

4. This line should read SELECT cCursor or SELE cCursor.

With objExcel
25. .Charts.Add
26. With .ActiveChart

5. AFAIK, you can NOT nest WITH statements.
[sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>The World Is Headed For Mutiny.....When All We Want Is Unity. - Creed[/sig]

 

[jfherring]

Jonscott8,
Thanks for the info. I did find a .h file with excel constants defined in it, which I #INCLUDE 'd in my prg and that did the trick. Need a copy?
John [sig][/sig]

 

[jonscott8]

Hi John,

Thanks for the offer, but typically, I only extract the necessary constants and include them in my app's header file. I dont see the need to include a header file with 1000's of constants when I only have the need for 2 or 3 of those constants.

It might be helpful in the future for others and myself, if you post where you found the .h file and the name of the file. [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>The World Is Headed For Mutiny,<br>
When All We Want Is Unity. - Creed[/sig]

 

[suha]

Hi Jon

thanks a lot for the information you provided me, as finally i can manage to get an Excel chart in my application.

suha