ciri
asked on
DDE between Excel and Visual Basic
I want to connect a Visual Basic picture box with a Excel graphic via DDE. But I don't know which item's name is a graphic in Excel for the linkitem propertie. For example, if I'll want to connect Visual Basic with a Excel cell, i'll push RxCx like linkitem, where x is the cell number. But, with a graphic?? It's possible??
Thanks
Thanks
Why must it be done using DDE? Why not using OLE Automation?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
MDougan is right, this is the way to do it using DDE.
ASKER
Well, I want to do this connection in execution time. The idea is that the user don't execute Excel. My program sends the data to Excel, and then create a chart (for do it, i create an excel object). But how i receive this chart?. I thougth in DDE. It's possible?. Is there any other form?. Any form is good. With the excel object?.
Thanks
Thanks
ASKER
The code that i use to send the data is the following
Dim Excel As Object
Set Excel = CreateObject("excel.applic ation")
Excel.Visible = false
With Excel
workbooks.Add
range("a2").Select
activecell.formular1c1 = "north"
range("a3").Select
activecell.formular1c1 = "south"
range("a4").Select
activecell.formular1c1 = "east"
range("a5").Select
activecell.formular1c1 = "west"
range("b1").Select
activecell.formular1c1 = "spring"
range("c1").Select
activecell.formular1c1 = "summer"
range("d1").Select
activecell.formular1c1 = "fall"
range("e1").Select
activecell.formular1c1 = "winter"
range("b2").Select
activecell.formular1c1 = "6"
range("c2").Select
activecell.formular1c1 = "35"
range("d2").Select
activecell.formular1c1 = "46"
range("e2").Select
activecell.formular1c1 = "34"
range("e3").Select
activecell.formular1c1 = "24"
range("d3").Select
activecell.formular1c1 = "36"
range("c3").Select
activecell.formular1c1 = "43"
range("b3").Select
activecell.formular1c1 = "32"
range("a3").Select
activecell.formular1c1 = "6"
range("a3").Select
range("b2:e3").Select
selection.autofill destination:=.range("b2:e5 ")
range("b2:e5").Select
range("a1:e5").Select
calculate
charts.Add
End With
Maybe with a little change I can receive the chart.
Dim Excel As Object
Set Excel = CreateObject("excel.applic
Excel.Visible = false
With Excel
workbooks.Add
range("a2").Select
activecell.formular1c1 = "north"
range("a3").Select
activecell.formular1c1 = "south"
range("a4").Select
activecell.formular1c1 = "east"
range("a5").Select
activecell.formular1c1 = "west"
range("b1").Select
activecell.formular1c1 = "spring"
range("c1").Select
activecell.formular1c1 = "summer"
range("d1").Select
activecell.formular1c1 = "fall"
range("e1").Select
activecell.formular1c1 = "winter"
range("b2").Select
activecell.formular1c1 = "6"
range("c2").Select
activecell.formular1c1 = "35"
range("d2").Select
activecell.formular1c1 = "46"
range("e2").Select
activecell.formular1c1 = "34"
range("e3").Select
activecell.formular1c1 = "24"
range("d3").Select
activecell.formular1c1 = "36"
range("c3").Select
activecell.formular1c1 = "43"
range("b3").Select
activecell.formular1c1 = "32"
range("a3").Select
activecell.formular1c1 = "6"
range("a3").Select
range("b2:e3").Select
selection.autofill destination:=.range("b2:e5
range("b2:e5").Select
range("a1:e5").Select
calculate
charts.Add
End With
Maybe with a little change I can receive the chart.
Lots of ways to do it. If you want to use DDE then you could create a spreadsheet ahead of time that has sample data in the cells you want to chart, create a chart by highlighting those cells and clicking the chart wizard, copy the chart and paste-link it into your VB program. Then, you would use DDE to poke data from the user into the cells of the spreadsheet. If the link between the picture box and the chart is automatic, you'll see the chart/picture box update automatically. It will be up to you to start Excel (or use an instance that is currently running) and load the pre-defined spreadsheet.
Or, you can use OLE automation. In OLE automation you would create an object variable as an Excel.Workbook and/or Excel.Worksheet. The coding is tricky to include here but there is an example that does almost everything you need in the VB Samples directory called:
VB\samples\PGuide\GeoFacts
The only thing this example doesn't do is create a chart. However, a trick I use to see what the VB code is necessary for creating something in Excel is to go to Excel, turn the Macro recorder on, making sure the language is VBA. Go through the steps mentioned above to highlight a range of cells and use the chart wizard to create the chart. Then, stop the macro recorder. Take a look at the macro code that Excel generated and you can probably almost cut and paste it into your VB app.
Good Luck
MD
Or, you can use OLE automation. In OLE automation you would create an object variable as an Excel.Workbook and/or Excel.Worksheet. The coding is tricky to include here but there is an example that does almost everything you need in the VB Samples directory called:
VB\samples\PGuide\GeoFacts
The only thing this example doesn't do is create a chart. However, a trick I use to see what the VB code is necessary for creating something in Excel is to go to Excel, turn the Macro recorder on, making sure the language is VBA. Go through the steps mentioned above to highlight a range of cells and use the chart wizard to create the chart. Then, stop the macro recorder. Take a look at the macro code that Excel generated and you can probably almost cut and paste it into your VB app.
Good Luck
MD
Oh, once you create your chart in Excel, if you want to view it, then you can either make your Excel object visible or you could then set up a dde link to the chart you created, with a picture box on your VB form. Just make sure not to set the DDE link properties until after you've created the chart. The link item property would be pretty standard if you are creating the chart on sheet1, it would always be sheet1.chart1 etc.
Another possibility is to embed the excel object in your form. But Embeded OLE containers never have worked very well.
Finally, do you really need to use the Excel Chart? Why not use the chart that comes with VB? You could always populate this chart with data from Excel using either DDE or OLE (sounds like you're using OLE already)?
MD
Another possibility is to embed the excel object in your form. But Embeded OLE containers never have worked very well.
Finally, do you really need to use the Excel Chart? Why not use the chart that comes with VB? You could always populate this chart with data from Excel using either DDE or OLE (sounds like you're using OLE already)?
MD
ASKER
mdougan, your DDE option only is valid only if the set of data is always the same, i think?
Respect the OLE automation option, I have tried to execute, but a type mismatch error occurrs in the following line:
Set shtWorld = GetObject("world.xls")
I don't know why.
I like this second option, but I don't have enough time to prove it until next year. See you then.
Respect the OLE automation option, I have tried to execute, but a type mismatch error occurrs in the following line:
Set shtWorld = GetObject("world.xls")
I don't know why.
I like this second option, but I don't have enough time to prove it until next year. See you then.
Oh, once you create your chart in Excel, if you want to view it, then you can either make your Excel object visible or you could then set up a dde link to the chart you created, with a picture box on your VB form. Just make sure not to set the DDE link properties until after you've created the chart. The link item property would be pretty standard if you are creating the chart on sheet1, it would always be sheet1.chart1 etc.
Another possibility is to embed the excel object in your form. But Embeded OLE containers never have worked very well.
Finally, do you really need to use the Excel Chart? Why not use the chart that comes with VB? You could always populate this chart with data from Excel using either DDE or OLE (sounds like you're using OLE already)?
MD
Another possibility is to embed the excel object in your form. But Embeded OLE containers never have worked very well.
Finally, do you really need to use the Excel Chart? Why not use the chart that comes with VB? You could always populate this chart with data from Excel using either DDE or OLE (sounds like you're using OLE already)?
MD
Sorry, I reposted the previous comment by mistake. That is a bug in their sample code. That line should read:
Set shtWorld = GetObject(App.Path & "\world.xls")
Happy New Year.
MD
Set shtWorld = GetObject(App.Path & "\world.xls")
Happy New Year.
MD
ASKER
OK. Finally, I have used a DDE link to view the chart, but I create it with OLE. Thanks