insert an Excel graph into a Richtextbox?
Author |
Message |
Bob O`Bo #1 / 25
|
insert an Excel graph into a Richtextbox?
A client has a need for something I've just never done. It doesn't seem like it should be too difficult, but I'm unsure of where to start. I've not done much at all with Excel. I have some data, in a string, in the form of [a portion of] a CSV file, and I want to instantiate an Excel worksheet, populate it with that data, graph an X/Y scatter diagram, an then somehow (maybe via the clipboard?) insert that chart into a RichTextBox. Suggestions, links, and/or samples would be greatly appreciated. Bob --
|
Tue, 14 Dec 2010 16:01:20 GMT |
|
|
VB M #2 / 25
|
insert an Excel graph into a Richtextbox?
released on Fri, 27 Jun 2008 01:01:20 -0700 bearing the following fruit: Quote: >A client has a need for something I've just never done. >It doesn't seem like it should be too difficult, but I'm unsure >of where to start. I've not done much at all with Excel. >I have some data, in a string, in the form of [a portion of] a CSV file, >and I want to instantiate an Excel worksheet, populate it with that data, >graph an X/Y scatter diagram, an then somehow (maybe via the clipboard?) >insert that chart into a RichTextBox. >Suggestions, links, and/or samples would be greatly appreciated.
I don't see why the clipboard shouldn't work since you can do it manually. I'm gonna play around and see if I can do it programmatically without the clipboard though. -- Jan Hyde https://mvp.support.microsoft.com/profile/Jan.Hyde
|
Tue, 14 Dec 2010 17:25:30 GMT |
|
|
Hennin #3 / 25
|
insert an Excel graph into a Richtextbox?
Quote: >A client has a need for something I've just never done. > It doesn't seem like it should be too difficult, but I'm unsure > of where to start. I've not done much at all with Excel. > I have some data, in a string, in the form of [a portion of] a CSV file, > and I want to instantiate an Excel worksheet, populate it with that data, > graph an X/Y scatter diagram, an then somehow (maybe via the clipboard?) > insert that chart into a RichTextBox. > Suggestions, links, and/or samples would be greatly appreciated. > Bob > --
If the data is a series of values, maybe the MSChart control can do it without Excel. The documentation though is terrible... /Henning
|
Tue, 14 Dec 2010 19:08:24 GMT |
|
|
VB M #4 / 25
|
insert an Excel graph into a Richtextbox?
released on Fri, 27 Jun 2008 13:08:24 +0200 bearing the following fruit: Quote:
>>A client has a need for something I've just never done. >> It doesn't seem like it should be too difficult, but I'm unsure >> of where to start. I've not done much at all with Excel. >> I have some data, in a string, in the form of [a portion of] a CSV file, >> and I want to instantiate an Excel worksheet, populate it with that data, >> graph an X/Y scatter diagram, an then somehow (maybe via the clipboard?) >> insert that chart into a RichTextBox. >> Suggestions, links, and/or samples would be greatly appreciated. >> Bob >> -- >If the data is a series of values, maybe the MSChart control can do it >without Excel. >The documentation though is terrible...
MSChart control sucks in every way possible ;-) I've failed to do it in code thus far but I know very little about the richtextbox and don't have the help files on this PC. -- Jan Hyde https://mvp.support.microsoft.com/profile/Jan.Hyde
|
Tue, 14 Dec 2010 19:55:50 GMT |
|
|
Bob O`Bo #5 / 25
|
insert an Excel graph into a Richtextbox?
Quote:
> released on Fri, 27 Jun 2008 01:01:20 -0700 bearing the > following fruit: >> A client has a need for something I've just never done. >> It doesn't seem like it should be too difficult, but I'm unsure >> of where to start. I've not done much at all with Excel. >> I have some data, in a string, in the form of [a portion of] a CSV file, >> and I want to instantiate an Excel worksheet, populate it with that data, >> graph an X/Y scatter diagram, an then somehow (maybe via the clipboard?) >> insert that chart into a RichTextBox. >> Suggestions, links, and/or samples would be greatly appreciated. > I don't see why the clipboard shouldn't work since you can > do it manually.
Huh? I am not in any way objecting to the clipboard, that was a *suggestion* Quote: > I'm gonna play around and see if I can do it > programmatically without the clipboard though.
I don't have any reason to care one way or the other. People _who know Excel_ can do it manually, yes. But as I said, I just don't know where to start to get this done with VB. Watching an Excel expert whip through it manually ... cool. But not helpful. I don't need to discuss alternatives, because no one is going to care. Any way which works ... works. Bob --
|
Tue, 14 Dec 2010 23:33:00 GMT |
|
|
RB Smissaer #6 / 25
|
insert an Excel graph into a Richtextbox?
Here is an example that works with an image control: http://www.j-walk.com/ss/excel/tips/tip66.htm RBS
Quote: >A client has a need for something I've just never done. > It doesn't seem like it should be too difficult, but I'm unsure > of where to start. I've not done much at all with Excel. > I have some data, in a string, in the form of [a portion of] a CSV file, > and I want to instantiate an Excel worksheet, populate it with that data, > graph an X/Y scatter diagram, an then somehow (maybe via the clipboard?) > insert that chart into a RichTextBox. > Suggestions, links, and/or samples would be greatly appreciated. > Bob > --
|
Wed, 15 Dec 2010 00:46:51 GMT |
|
|
Bob O`Bo #7 / 25
|
insert an Excel graph into a Richtextbox?
Quote:
> Here is an example that works with an image control: > http://www.j-walk.com/ss/excel/tips/tip66.htm
Thanks, but... Again: That is Excel. I do not know Excel. "[step] 1. Create your chart or charts as usual." There is no "spare time" between now and Monday (project deadline) for me to learn enough Excel. What I need is the steps it takes to *drive* that process from VB and add the chart into an RTB. I have not interacted significantly with Excel from VB before. It _does_ look like having Excel save the chart to a file is an alternative to using the clipboard, but I have no objections to using the clipboard, and preference FOR whatever just works. Bob --
|
Wed, 15 Dec 2010 01:00:37 GMT |
|
|
RB Smissaer #8 / 25
|
insert an Excel graph into a Richtextbox?
Start the macro recorder in Excel and create a chart. This is from the Excel menubar: Insert, Chart and just follow the wizard. You will need to have some data in the sheet to make the chart from, say a 2 column range with dates in column 1 and values in column 2. Stop the macro recorder and look at the produced code. I take it you know how to start Excel from VB. Then make sure all Excel code is fully referenced to your oXL object and also make sure all objects are set to Nothing after the job is done. RBS
Quote:
>> Here is an example that works with an image control: >> http://www.j-walk.com/ss/excel/tips/tip66.htm > Thanks, but... > Again: That is Excel. I do not know Excel. > "[step] 1. Create your chart or charts as usual." > There is no "spare time" between now and Monday > (project deadline) for me to learn enough Excel. > What I need is the steps it takes to *drive* that process > from VB and add the chart into an RTB. I have not interacted > significantly with Excel from VB before. > It _does_ look like having Excel save the chart to a file > is an alternative to using the clipboard, but I have no > objections to using the clipboard, and preference FOR > whatever just works. > Bob > --
|
Wed, 15 Dec 2010 01:15:59 GMT |
|
|
RB Smissaer #9 / 25
|
insert an Excel graph into a Richtextbox?
I forgot to say that maybe a RichText is not the best option here. Firstly, because there are security problems with the RT control in Excel and secondly I am not sure it can accept an image file. RBS
Quote:
>> Here is an example that works with an image control: >> http://www.j-walk.com/ss/excel/tips/tip66.htm > Thanks, but... > Again: That is Excel. I do not know Excel. > "[step] 1. Create your chart or charts as usual." > There is no "spare time" between now and Monday > (project deadline) for me to learn enough Excel. > What I need is the steps it takes to *drive* that process > from VB and add the chart into an RTB. I have not interacted > significantly with Excel from VB before. > It _does_ look like having Excel save the chart to a file > is an alternative to using the clipboard, but I have no > objections to using the clipboard, and preference FOR > whatever just works. > Bob > --
|
Wed, 15 Dec 2010 01:18:47 GMT |
|
|
Bob O`Bo #10 / 25
|
insert an Excel graph into a Richtextbox?
Quote:
> I forgot to say that maybe a RichText is not the best option here. > Firstly, because there are security problems > with the RT control in Excel and secondly I am not sure > it can accept an image file.
There aren't any choices in that regard at this stage in the game. Since I can use paint to create a jpg and successfully paste that into my RTB, I don't think there are any worries there. I just need a greater degree of "hand holding" regarding driving the Excel object. I can look at the VBA code generated by recording a macro (having an Excel expert create the chart), and somehow that just doesn't help. I have my data in a VB string. Bob --
|
Wed, 15 Dec 2010 02:01:11 GMT |
|
|
RB Smissaer #11 / 25
|
insert an Excel graph into a Richtextbox?
OK, here is some code to get you going. This is in a plain .exe file with nil else than this code in a Module. All late binding, so no project reference to Excel. Option Explicit Private Const xlLineMarkers As Long = 65 Private Const xlColumns As Long = 2 Private Const xlLocationAsNewSheet As Long = 1 Sub Main() Dim oXL As Object Dim oWorkbook As Object Dim oSheet As Object Dim oChart As Object Set oXL = CreateObject("Excel.Application") Set oWorkbook = oXL.WorkBooks.Add Set oSheet = oXL.WorkSheets.Add Set oChart = oXL.Charts.Add 'not sure if .SetSourceData can accept anything else than a worksheet range With oSheet .cells(1) = "date" .cells(2, 1) = "01/02/2008" .cells(3, 1) = "01/03/2008" .cells(4, 1) = "01/04/2008" .cells(5, 1) = "01/05/2008" .cells(2) = "value" .cells(2, 2) = 2 .cells(3, 2) = 3 .cells(4, 2) = 3 .cells(5, 2) = 2 End With With oChart .ChartType = xlLineMarkers .SetSourceData Source:=oSheet.Range("A1:B5"), PlotBy:=xlColumns .Location Where:=xlLocationAsNewSheet .Export FileName:="C:\ChartTest.gif", FilterName:="GIF" End With 'to avoid the message about saving the workbook oXL.DisplayAlerts = False 'make sure to clear all Excel objects Set oChart = Nothing Set oSheet = Nothing Set oWorkbook = Nothing oXL.Quit Set oXL = Nothing End Sub RBS
Quote:
>> I forgot to say that maybe a RichText is not the best option here. >> Firstly, because there are security problems >> with the RT control in Excel and secondly I am not sure >> it can accept an image file. > There aren't any choices in that regard at this stage in the game. > Since I can use paint to create a jpg and successfully paste that > into my RTB, I don't think there are any worries there. > I just need a greater degree of "hand holding" regarding driving > the Excel object. I can look at the VBA code generated by recording > a macro (having an Excel expert create the chart), and somehow > that just doesn't help. I have my data in a VB string. > Bob > --
|
Wed, 15 Dec 2010 03:21:49 GMT |
|
|
RB Smissaer #12 / 25
|
insert an Excel graph into a Richtextbox?
Here all the essential code that is needed. Add a Form and a RTB. Option Explicit Private Const xlLineMarkers As Long = 65 Private Const xlXYScatter As Long = -4169 Private Const xlColumns As Long = 2 Private Const xlLocationAsNewSheet As Long = 1 Private Declare Function SendMessage Lib "user32" _ Alias "SendMessageA" _ (ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ lParam As Any) As Long Private Const WM_PASTE As Long = 770 Private Const WM_LBUTTONDOWN As Long = 513 Private Const WM_LBUTTONUP As Long = 514 Sub Main() Dim oXL As Object Dim oWorkbook As Object Dim oSheet As Object Dim oChart As Object Set oXL = CreateObject("Excel.Application") Set oWorkbook = oXL.WorkBooks.Add Set oSheet = oXL.WorkSheets.Add Set oChart = oXL.Charts.Add 'not sure if .SetSourceData can accept anything else than a worksheet range With oSheet .cells(1) = "date" .cells(2, 1) = "01/02/2008" .cells(3, 1) = "01/03/2008" .cells(4, 1) = "01/04/2008" .cells(5, 1) = "01/05/2008" .cells(2) = "value" .cells(2, 2) = 2 .cells(3, 2) = 3 .cells(4, 2) = 3 .cells(5, 2) = 2 End With With oChart .ChartType = xlXYScatter .SetSourceData Source:=oSheet.Range("A1:B5"), PlotBy:=xlColumns .Location Where:=xlLocationAsNewSheet 'size the chart With .PlotArea .Height = 200 .Width = 300 End With '.Export FileName:="C:\ChartTest.gif", FilterName:="GIF" .ChartArea.Copy End With 'to avoid the message about saving the workbook oXL.DisplayAlerts = False 'make sure to clear all Excel objects Set oChart = Nothing Set oSheet = Nothing Set oWorkbook = Nothing oXL.Quit Set oXL = Nothing Load Form1 Form1.Show 'paste the clipboard image to the RTF SendMessage Form1.RichTextBox1.hwnd, WM_PASTE, 0, 0 'to size and position the image; must be simpler way to do this SendMessage Form1.RichTextBox1.hwnd, WM_LBUTTONDOWN, 0, 0 SendMessage Form1.RichTextBox1.hwnd, WM_LBUTTONUP, 0, 0 'InsertPictureInRichTextBox Form1.RichTextBox1, LoadPicture("C:\ChartTest.gif") End Sub Sub InsertPictureInRichTextBox(RTB As RichTextBox, Picture As StdPicture) Clipboard.Clear Clipboard.SetData Picture SendMessage RTB.hwnd, WM_PASTE, 0, 0 End Sub You will need to further fill in the Excel code to specify the chart and the only way to do that is play with the Excel macro recorder, but that should be no problem. Not sure if there is a way to get the chart in the RTB without the clipboard. I take it there is no problem to fill the Excel range with your data in your string. RBS
Quote:
>> I forgot to say that maybe a RichText is not the best option here. >> Firstly, because there are security problems >> with the RT control in Excel and secondly I am not sure >> it can accept an image file. > There aren't any choices in that regard at this stage in the game. > Since I can use paint to create a jpg and successfully paste that > into my RTB, I don't think there are any worries there. > I just need a greater degree of "hand holding" regarding driving > the Excel object. I can look at the VBA code generated by recording > a macro (having an Excel expert create the chart), and somehow > that just doesn't help. I have my data in a VB string. > Bob > --
|
Wed, 15 Dec 2010 14:42:33 GMT |
|
|
Peter #13 / 25
|
insert an Excel graph into a Richtextbox?
I didn't know you could simply paste an Excel chart (image) into an RTB. There is another series of API's to extract the chart from the clipboard into any control with a picture property, handy if it also has an AutoSize property. Following is along similar lines to RBS's but with some different Excel methods, namely - Data directly into series formulas (see the limitation note in the comments, probably better in cells but just for ideas) - Create an embedded chart, easier to size than a chart sheet - for better re-scalability of the pasted chart and other things, use cht.CopyPicture arg's vs cht.ChartArea.Copy ' in a Form with an RTB ' keep clicking the form Option Explicit Private Const xlLineMarkers As Long = 65 Private Const xlXYScatter As Long = -4169 Private Const xlColumns As Long = 2 Private Const xlLocationAsNewSheet As Long = 1 Private Const xlPicture As Long = -4147 Private Const xlScreen As Long = 1 Private Const xlBitmap As Long = 2 Private Declare Function SendMessage Lib "user32" _ Alias "SendMessageA" _ (ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ lParam As Any) As Long Private Const WM_PASTE As Long = 770 Private Const WM_LBUTTONDOWN As Long = 513 Private Const WM_LBUTTONUP As Long = 514 Dim moXL As Object, moCht As Chart Sub ChartToRTB(Optional w As Single, Optional h As Single) Dim vArr, x As Long Dim oWB As Object, oWS As Object Dim oSR As Object 'normally better always create a new Excel instance ' but for developing ... On Error Resume Next ' lazy just for this post If moXL Is Nothing Then Set moXL = GetObject(, "excel.application") ' If moXL Is Nothing Then Set moXL = CreateObject("excel.application") moXL.Visible = True ' for testing End If If moXL Is Nothing Then ' ????????? MsgBox Err.Description Exit Sub End If End If ' Set oWB = moXL.Workbooks(sName) ' If oWB Is Nothing Then ' Set oWB = moXL.Workbooks.Open(sPath) ' End If Set oWB = moXL.ActiveWorkbook ' for testing If oWB Is Nothing Then Set oWB = moXL.Workbooks.Add End If Set oWS = oWB.Worksheets(1) Set moCht = oWS.ChartObjects(1).Chart If moCht Is Nothing Then If w = 0 Then w = 270 ' points If h = 0 Then h = 180 Set moCht = oWS.ChartObjects.Add(10#, 10#, w, h).Chart moCht.ChartType = xlXYScatter Else If w Then moCht.Parent.Width = w If h Then moCht.Parent.Height = h End If Set oSR = moCht.SeriesCollection(1) If oSR Is Nothing Then Set oSR = moCht.SeriesCollection.NewSeries oSR.Name = "Apples" End If oSR.XValues = SomeArray(5) ' * see note oSR.Values = SomeArray(10) Set oSR = Nothing Set oSR = moCht.SeriesCollection(2) If oSR Is Nothing Then Set oSR = moCht.SeriesCollection.NewSeries oSR.Name = "Pears" End If oSR.XValues = SomeArray(5) oSR.Values = SomeArray(10) With moCht.Axes(xlCategory, xlPrimary) .HasTitle = True .AxisTitle.Text = "Ripeness" End With With moCht.Axes(xlValue, xlPrimary) .HasTitle = True .AxisTitle.Text = "Size" End With moCht.HasTitle = True moCht.ChartTitle.Text = "Orchard" moCht.HasLegend = True moCht.PlotArea.Fill.ForeColor.SchemeColor = 2 moCht.CopyPicture xlScreen, xlPicture, xlScreen SendMessage Form1.RichTextBox1.hwnd, WM_PASTE, 0, 0 ' * Applying an array directly to sr.Values writes values ' into the series formula, no need for cells ' BUT will fail if len("{1,2,3.1,123.456}"} > 255 ' (ie len all the values + comma's + curly brackets > 255 ' within one "section" of the 4-part series formula ' Normal way is with series data in cells, record an Excel nacro ' record an Excel nacro to get the syntax End Sub Function SomeArray(x As Long) Dim i& Dim vArr ReDim vArr(1 To 10) For i = 1 To 10 vArr(i) = Int(Rnd() * x) + 1 Next SomeArray = vArr End Function Private Sub Form_Click() ChartToRTB End Sub Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) On Error Resume Next ' If Not moXL Is Nothing Then ' moXL.DisplayAlerts = False ' ' careful all workbooks'll close w/out save ' moXL.Quit ' End If End Sub Above really is for ideas only and would need a lot more. For testing, In Excel select the chart, Shift-Edit, Copy Picture. Try the various options then Ctrl-V into the RTB. Also record a macro to format the chart as required, new series etc to get all the basic syntax (select & activate stuff will need to be removed), most of which can be adapted into DoChart above. Regards, Peter T
|
Thu, 16 Dec 2010 00:18:11 GMT |
|
|
Bob O`Bo #14 / 25
|
insert an Excel graph into a Richtextbox?
Thanks very much for the examples - both of you. With a little bit of luck in fighting the /other/ wildfires this project faces, I may be able to install some of it later today. I had already experienced a small measure of success with RB's code yesterday, but I'm pretty sure that the combination of ideas from both of you has put me on a track that can reach my goal. Bob --
|
Thu, 16 Dec 2010 01:44:56 GMT |
|
|
Peter #15 / 25
|
insert an Excel graph into a Richtextbox?
Looks like I forgot to remove an Early binding reference set to Excel, just tried the code again without one and had to include the following (in the code below) Private Const xlCategory As Long = 1 Private Const xlPrimary As Long = 1 Private Const xlValue As Long = 2 Dim moXL As Object, moCht As Object ' Excel.Chart Sorry about that but it looks like you (Bob) managed to sort that out and are now well under way. Regards, Peter T Quote:
> ' in a Form with an RTB > ' keep clicking the form > Option Explicit > Private Const xlLineMarkers As Long = 65 > Private Const xlXYScatter As Long = -4169 > Private Const xlColumns As Long = 2 > Private Const xlLocationAsNewSheet As Long = 1 > Private Const xlPicture As Long = -4147 > Private Const xlScreen As Long = 1 > Private Const xlBitmap As Long = 2 > Private Declare Function SendMessage Lib "user32" _ > Alias "SendMessageA" _ > (ByVal hwnd As Long, _ > ByVal wMsg As Long, _ > ByVal wParam As Long, _ > lParam As Any) As Long > Private Const WM_PASTE As Long = 770 > Private Const WM_LBUTTONDOWN As Long = 513 > Private Const WM_LBUTTONUP As Long = 514 > Dim moXL As Object, moCht As Chart > Sub ChartToRTB(Optional w As Single, Optional h As Single) > Dim vArr, x As Long > Dim oWB As Object, oWS As Object > Dim oSR As Object > 'normally better always create a new Excel instance > ' but for developing ... > On Error Resume Next ' lazy just for this post > If moXL Is Nothing Then > Set moXL = GetObject(, "excel.application") > ' > If moXL Is Nothing Then > Set moXL = CreateObject("excel.application") > moXL.Visible = True ' for testing > End If > If moXL Is Nothing Then > ' ????????? > MsgBox Err.Description > Exit Sub > End If > End If > ' Set oWB = moXL.Workbooks(sName) > ' If oWB Is Nothing Then > ' Set oWB = moXL.Workbooks.Open(sPath) > ' End If > Set oWB = moXL.ActiveWorkbook ' for testing > If oWB Is Nothing Then > Set oWB = moXL.Workbooks.Add > End If > Set oWS = oWB.Worksheets(1) > Set moCht = oWS.ChartObjects(1).Chart > If moCht Is Nothing Then > If w = 0 Then w = 270 ' points > If h = 0 Then h = 180 > Set moCht = oWS.ChartObjects.Add(10#, 10#, w, h).Chart > moCht.ChartType = xlXYScatter > Else > If w Then moCht.Parent.Width = w > If h Then moCht.Parent.Height = h > End If > Set oSR = moCht.SeriesCollection(1) > If oSR Is Nothing Then > Set oSR = moCht.SeriesCollection.NewSeries > oSR.Name = "Apples" > End If > oSR.XValues = SomeArray(5) ' * see note > oSR.Values = SomeArray(10) > Set oSR = Nothing > Set oSR = moCht.SeriesCollection(2) > If oSR Is Nothing Then > Set oSR = moCht.SeriesCollection.NewSeries > oSR.Name = "Pears" > End If > oSR.XValues = SomeArray(5) > oSR.Values = SomeArray(10) > With moCht.Axes(xlCategory, xlPrimary) > .HasTitle = True > .AxisTitle.Text = "Ripeness" > End With > With moCht.Axes(xlValue, xlPrimary) > .HasTitle = True > .AxisTitle.Text = "Size" > End With > moCht.HasTitle = True > moCht.ChartTitle.Text = "Orchard" > moCht.HasLegend = True > moCht.PlotArea.Fill.ForeColor.SchemeColor = 2 > moCht.CopyPicture xlScreen, xlPicture, xlScreen > SendMessage Form1.RichTextBox1.hwnd, WM_PASTE, 0, 0 > ' * Applying an array directly to sr.Values writes values > ' into the series formula, no need for cells > ' BUT will fail if len("{1,2,3.1,123.456}"} > 255 > ' (ie len all the values + comma's + curly brackets > 255 > ' within one "section" of the 4-part series formula > ' Normal way is with series data in cells, record an Excel nacro > ' record an Excel nacro to get the syntax > End Sub > Function SomeArray(x As Long) > Dim i& > Dim vArr > ReDim vArr(1 To 10) > For i = 1 To 10 > vArr(i) = Int(Rnd() * x) + 1 > Next > SomeArray = vArr > End Function > Private Sub Form_Click() > ChartToRTB > End Sub > Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) > On Error Resume Next > ' If Not moXL Is Nothing Then > ' moXL.DisplayAlerts = False > ' ' careful all workbooks'll close w/out save > ' moXL.Quit > ' End If > End Sub
|
Thu, 16 Dec 2010 17:57:37 GMT |
|
|
Page 1 of 2
|
[ 25 post ] |
|
Go to page:
[1]
[2] |
|