Drawing on a Excel chart using the API


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 15 of 15

Thread: Drawing on a Excel chart using the API

  1. #1
    Michael O'Reilly Guest

    Drawing on a Excel chart using the API


    Has anyone managed to draw lines or rectangles on a Excel chart using API
    calls.
    I want to draw a "zoom" box on a chart. I have code to do this working on
    a regular VB form, but Excel charts don't have a Device context which is
    require for the API calls. I also tried embedding the chart in a Picturebox
    and using it's Device context but that also doesn't work.

    As a last resort I tried placing a picture box over the chart and then making
    it transparent, but the code does nothing once the Picturebox is made transparent.

    Anyone got any ideas or sucessfully done this.

    BTW I'm using Excel 2000 but would prefer to get this working in Excel97
    also.

    Thanks

    Michael



  2. #2
    Matthew Solnit Guest

    Re: Drawing on a Excel chart using the API

    "Michael O'Reilly" <oreillymj@home.com> wrote in message
    news:3bab72fa$1@news.devx.com...
    >
    > Has anyone managed to draw lines or rectangles on a Excel chart using API
    > calls.
    > I want to draw a "zoom" box on a chart. I have code to do this working on
    > a regular VB form, but Excel charts don't have a Device context which is
    > require for the API calls. I also tried embedding the chart in a Picturebox
    > and using it's Device context but that also doesn't work.
    >
    > As a last resort I tried placing a picture box over the chart and then making
    > it transparent, but the code does nothing once the Picturebox is made

    transparent.

    Do you want to actually modify the spreadsheet, or just give the user a "faked"
    display of the spreadsheet with the drawings on top? If it's the first, then
    you can use Excel Automation to add Shape objects. If it's the second, then I'm
    pretty sure there's a way to manipulate window transparency such that certain
    colors are opaque. However, I've never done this.

    -- Matthew Solnit



  3. #3
    Matthew Solnit Guest

    Re: Drawing on a Excel chart using the API

    "Michael O'Reilly" <oreillymj@home.com> wrote in message
    news:3bab72fa$1@news.devx.com...
    >
    > Has anyone managed to draw lines or rectangles on a Excel chart using API
    > calls.
    > I want to draw a "zoom" box on a chart. I have code to do this working on
    > a regular VB form, but Excel charts don't have a Device context which is
    > require for the API calls. I also tried embedding the chart in a Picturebox
    > and using it's Device context but that also doesn't work.
    >
    > As a last resort I tried placing a picture box over the chart and then making
    > it transparent, but the code does nothing once the Picturebox is made

    transparent.

    Do you want to actually modify the spreadsheet, or just give the user a "faked"
    display of the spreadsheet with the drawings on top? If it's the first, then
    you can use Excel Automation to add Shape objects. If it's the second, then I'm
    pretty sure there's a way to manipulate window transparency such that certain
    colors are opaque. However, I've never done this.

    -- Matthew Solnit



  4. #4
    Michael O'Reilly Guest

    Re: Drawing on a Excel chart using the API


    Matthew,

    I want to provide the user with a way of zooming in on the data on a chart.
    The user will drag out a rectangular box like in a paint application and
    then zoom in on that part of chart. I don't want the chart to me modified.
    It's just a selection tool. Then I'll need to get x and y coords for the
    selected area and map that back to the underlying data values.

    But first I need to get the actual rectangle drawn on the chart. It does
    expose and Hwnd or Hdc.

    Michael

    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Michael O'Reilly" <oreillymj@home.com> wrote in message
    >news:3bab72fa$1@news.devx.com...
    >>
    >> Has anyone managed to draw lines or rectangles on a Excel chart using

    API
    >> calls.
    >> I want to draw a "zoom" box on a chart. I have code to do this working

    on
    >> a regular VB form, but Excel charts don't have a Device context which

    is
    >> require for the API calls. I also tried embedding the chart in a Picturebox
    >> and using it's Device context but that also doesn't work.
    >>
    >> As a last resort I tried placing a picture box over the chart and then

    making
    >> it transparent, but the code does nothing once the Picturebox is made

    >transparent.
    >
    >Do you want to actually modify the spreadsheet, or just give the user a

    "faked"
    >display of the spreadsheet with the drawings on top? If it's the first,

    then
    >you can use Excel Automation to add Shape objects. If it's the second,

    then I'm
    >pretty sure there's a way to manipulate window transparency such that certain
    >colors are opaque. However, I've never done this.
    >
    >-- Matthew Solnit
    >
    >



  5. #5
    Michael O'Reilly Guest

    Re: Drawing on a Excel chart using the API


    Matthew,

    I want to provide the user with a way of zooming in on the data on a chart.
    The user will drag out a rectangular box like in a paint application and
    then zoom in on that part of chart. I don't want the chart to me modified.
    It's just a selection tool. Then I'll need to get x and y coords for the
    selected area and map that back to the underlying data values.

    But first I need to get the actual rectangle drawn on the chart. It does
    expose and Hwnd or Hdc.

    Michael

    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Michael O'Reilly" <oreillymj@home.com> wrote in message
    >news:3bab72fa$1@news.devx.com...
    >>
    >> Has anyone managed to draw lines or rectangles on a Excel chart using

    API
    >> calls.
    >> I want to draw a "zoom" box on a chart. I have code to do this working

    on
    >> a regular VB form, but Excel charts don't have a Device context which

    is
    >> require for the API calls. I also tried embedding the chart in a Picturebox
    >> and using it's Device context but that also doesn't work.
    >>
    >> As a last resort I tried placing a picture box over the chart and then

    making
    >> it transparent, but the code does nothing once the Picturebox is made

    >transparent.
    >
    >Do you want to actually modify the spreadsheet, or just give the user a

    "faked"
    >display of the spreadsheet with the drawings on top? If it's the first,

    then
    >you can use Excel Automation to add Shape objects. If it's the second,

    then I'm
    >pretty sure there's a way to manipulate window transparency such that certain
    >colors are opaque. However, I've never done this.
    >
    >-- Matthew Solnit
    >
    >



  6. #6
    Willy Van den Driessche Guest

    Re: Drawing on a Excel chart using the API

    Not that it answers your question, but we have programmed exactly the same
    using the TeeChart pro chart. It has built-in zooming
    (http://www.teemach.com/products/products.htm). The synchornization between
    chart and "grid data" was easy to do.

    --
    Van den Driessche Willy
    For a work in progress :
    http://users.skynet.be/wvdd2/index.html



  7. #7
    Willy Van den Driessche Guest

    Re: Drawing on a Excel chart using the API

    Not that it answers your question, but we have programmed exactly the same
    using the TeeChart pro chart. It has built-in zooming
    (http://www.teemach.com/products/products.htm). The synchornization between
    chart and "grid data" was easy to do.

    --
    Van den Driessche Willy
    For a work in progress :
    http://users.skynet.be/wvdd2/index.html



  8. #8
    Matthew Solnit Guest

    Re: Drawing on a Excel chart using the API

    "Michael O'Reilly" <oreillymj@home.com> wrote in message
    news:3bab9776$1@news.devx.com...
    > I want to provide the user with a way of zooming in on the data on a chart.
    > The user will drag out a rectangular box like in a paint application and
    > then zoom in on that part of chart. I don't want the chart to me modified.
    > It's just a selection tool. Then I'll need to get x and y coords for the
    > selected area and map that back to the underlying data values.
    >
    > But first I need to get the actual rectangle drawn on the chart. It does
    > expose and Hwnd or Hdc.


    How are you containing the spreadsheet on your VB form? If you are using the
    OLE control, it has an hWnd property.

    -- Matthew Solnit



  9. #9
    Matthew Solnit Guest

    Re: Drawing on a Excel chart using the API

    "Michael O'Reilly" <oreillymj@home.com> wrote in message
    news:3bab9776$1@news.devx.com...
    > I want to provide the user with a way of zooming in on the data on a chart.
    > The user will drag out a rectangular box like in a paint application and
    > then zoom in on that part of chart. I don't want the chart to me modified.
    > It's just a selection tool. Then I'll need to get x and y coords for the
    > selected area and map that back to the underlying data values.
    >
    > But first I need to get the actual rectangle drawn on the chart. It does
    > expose and Hwnd or Hdc.


    How are you containing the spreadsheet on your VB form? If you are using the
    OLE control, it has an hWnd property.

    -- Matthew Solnit



  10. #10
    Michael O'Reilly Guest

    Re: Drawing on a Excel chart using the API


    Matthew,

    When I open VB, I don't see an OLE control for Excel charts. I have to embed
    the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
    object library doesn't seem to contain a Hwnd property.

    Are you talking about the MsGrph.ocx control which does have a Hwnd property.
    I've tried drawing on that without any success as well.

    Michael

    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Michael O'Reilly" <oreillymj@home.com> wrote in message
    >news:3bab9776$1@news.devx.com...
    >> I want to provide the user with a way of zooming in on the data on a chart.
    >> The user will drag out a rectangular box like in a paint application and
    >> then zoom in on that part of chart. I don't want the chart to me modified.
    >> It's just a selection tool. Then I'll need to get x and y coords for the
    >> selected area and map that back to the underlying data values.
    >>
    >> But first I need to get the actual rectangle drawn on the chart. It does
    >> expose and Hwnd or Hdc.

    >
    >How are you containing the spreadsheet on your VB form? If you are using

    the
    >OLE control, it has an hWnd property.
    >
    >-- Matthew Solnit
    >
    >



  11. #11
    Michael O'Reilly Guest

    Re: Drawing on a Excel chart using the API


    Matthew,

    When I open VB, I don't see an OLE control for Excel charts. I have to embed
    the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
    object library doesn't seem to contain a Hwnd property.

    Are you talking about the MsGrph.ocx control which does have a Hwnd property.
    I've tried drawing on that without any success as well.

    Michael

    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Michael O'Reilly" <oreillymj@home.com> wrote in message
    >news:3bab9776$1@news.devx.com...
    >> I want to provide the user with a way of zooming in on the data on a chart.
    >> The user will drag out a rectangular box like in a paint application and
    >> then zoom in on that part of chart. I don't want the chart to me modified.
    >> It's just a selection tool. Then I'll need to get x and y coords for the
    >> selected area and map that back to the underlying data values.
    >>
    >> But first I need to get the actual rectangle drawn on the chart. It does
    >> expose and Hwnd or Hdc.

    >
    >How are you containing the spreadsheet on your VB form? If you are using

    the
    >OLE control, it has an hWnd property.
    >
    >-- Matthew Solnit
    >
    >



  12. #12
    Matthew Solnit Guest

    Re: Drawing on a Excel chart using the API

    "Michael O'Reilly" <oreillymj@home.com> wrote in message
    news:3baf6380$1@news.devx.com...
    >
    > When I open VB, I don't see an OLE control for Excel charts. I have to embed
    > the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
    > object library doesn't seem to contain a Hwnd property.


    Ah, the terminology is confusing. "OLE control" can be a generic term for
    "ActiveX control", "OCX" etc. It is also one specific control, which has been
    included with Visual Basic since VB3. It's an intrinsic control, meaning you
    don't have to add it using the Components dialog.

    If you start up VB and look in the toolbox, you'll see an icon that just says
    "OLE". This is a control which can be used to host things like spreadsheets,
    etc. If you use this control to embed the spreadsheet in your form, then you'll
    have an hWnd property.

    The thing is, I have no idea if you can use this hWnd to do the drawing you
    want. But you can certainly try it out ;-)

    -- Matthew Solnit



  13. #13
    Matthew Solnit Guest

    Re: Drawing on a Excel chart using the API

    "Michael O'Reilly" <oreillymj@home.com> wrote in message
    news:3baf6380$1@news.devx.com...
    >
    > When I open VB, I don't see an OLE control for Excel charts. I have to embed
    > the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
    > object library doesn't seem to contain a Hwnd property.


    Ah, the terminology is confusing. "OLE control" can be a generic term for
    "ActiveX control", "OCX" etc. It is also one specific control, which has been
    included with Visual Basic since VB3. It's an intrinsic control, meaning you
    don't have to add it using the Components dialog.

    If you start up VB and look in the toolbox, you'll see an icon that just says
    "OLE". This is a control which can be used to host things like spreadsheets,
    etc. If you use this control to embed the spreadsheet in your form, then you'll
    have an hWnd property.

    The thing is, I have no idea if you can use this hWnd to do the drawing you
    want. But you can certainly try it out ;-)

    -- Matthew Solnit



  14. #14
    Michael O'Reilly Guest

    Re: Drawing on a Excel chart using the API


    Matthew,

    Oh' right, that what that OLE thingy is for. I've seen it sitting there but
    never used it, so I completely overlook it.

    I'll give it a try, but I did already use a PictureBox control as a container
    object to get a Hwnd and that didn't do anything for me.

    But thanks for your help

    Michael.



    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Michael O'Reilly" <oreillymj@home.com> wrote in message
    >news:3baf6380$1@news.devx.com...
    >>
    >> When I open VB, I don't see an OLE control for Excel charts. I have to

    embed
    >> the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
    >> object library doesn't seem to contain a Hwnd property.

    >
    >Ah, the terminology is confusing. "OLE control" can be a generic term for
    >"ActiveX control", "OCX" etc. It is also one specific control, which has

    been
    >included with Visual Basic since VB3. It's an intrinsic control, meaning

    you
    >don't have to add it using the Components dialog.
    >
    >If you start up VB and look in the toolbox, you'll see an icon that just

    says
    >"OLE". This is a control which can be used to host things like spreadsheets,
    >etc. If you use this control to embed the spreadsheet in your form, then

    you'll
    >have an hWnd property.
    >
    >The thing is, I have no idea if you can use this hWnd to do the drawing

    you
    >want. But you can certainly try it out ;-)
    >
    >-- Matthew Solnit
    >
    >



  15. #15
    Michael O'Reilly Guest

    Re: Drawing on a Excel chart using the API


    Matthew,

    Oh' right, that what that OLE thingy is for. I've seen it sitting there but
    never used it, so I completely overlook it.

    I'll give it a try, but I did already use a PictureBox control as a container
    object to get a Hwnd and that didn't do anything for me.

    But thanks for your help

    Michael.



    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Michael O'Reilly" <oreillymj@home.com> wrote in message
    >news:3baf6380$1@news.devx.com...
    >>
    >> When I open VB, I don't see an OLE control for Excel charts. I have to

    embed
    >> the Excel .exe to be able to draw an Excel graph in VB. Anyway the Excel
    >> object library doesn't seem to contain a Hwnd property.

    >
    >Ah, the terminology is confusing. "OLE control" can be a generic term for
    >"ActiveX control", "OCX" etc. It is also one specific control, which has

    been
    >included with Visual Basic since VB3. It's an intrinsic control, meaning

    you
    >don't have to add it using the Components dialog.
    >
    >If you start up VB and look in the toolbox, you'll see an icon that just

    says
    >"OLE". This is a control which can be used to host things like spreadsheets,
    >etc. If you use this control to embed the spreadsheet in your form, then

    you'll
    >have an hWnd property.
    >
    >The thing is, I have no idea if you can use this hWnd to do the drawing

    you
    >want. But you can certainly try it out ;-)
    >
    >-- Matthew Solnit
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center