Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
As far as I can tell, this *should* embed my chart in the Active worksheet:
xlapp.Workbooks(strXlsFile).ActiveChart.Location Whe= _ xlLocationAsObject, Name:=strSheetName (where strSheetName is the name of the target worksheet) But when I put this code in a loop, the chart is created in it's *own sheet*: xlapp.Workbooks(strXlsFile).Charts.Add.Name = strSheetName xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlCat egory, xlPrimary).HasTitle = False xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlVal ue, xlPrimary).HasTitle = False xlapp.Workbooks(strXlsFile).ActiveChart.HasLegend = False xlapp.Workbooks(strXlsFile).ActiveChart.HasTitle = False Am I missing something obvious, or do I need to do something different when using automation? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
(a) What code does XL generate when you use its macro recorder?
ActiveChart.Location Whe=xlLocationAsObject, Name:="2000_pr4a" Of course, 2000_pr4a = strSheetName (b) If you already have a worksheet named strSheetName, how do you expect xlapp.Workbooks(strXlsFile).Charts.Add.Name = strSheetName to work? Perhaps that's my problem. I've tried using this: xlapp.Workbooks(strXlsFile).Charts.Add xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName But the charts still appear in their own sheet - Chart1, Chart2, Chart3, etc. As far as I can tell, this *should* embed my chart in the Active worksheet: xlapp.Workbooks(strXlsFile).ActiveChart.Location Whe= _ xlLocationAsObject, Name:=strSheetName (where strSheetName is the name of the target worksheet) But when I put this code in a loop, the chart is created in it's *own sheet*: xlapp.Workbooks(strXlsFile).Charts.Add.Name = strSheetName xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlCat egory, xlPrimary).HasTitle = False xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlVal ue, xlPrimary).HasTitle = False xlapp.Workbooks(strXlsFile).ActiveChart.HasLegend = False xlapp.Workbooks(strXlsFile).ActiveChart.HasTitle = False Am I missing something obvious, or do I need to do something different when using automation? Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
I've been experimenting with the macro recorder and that is a big help, to
be sure. I just tried this again, and this time no chart appears in the workbook. xlapp.Workbooks(strXlsFile).Charts.Add xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName As far as I can tell, this is correct code. Please correct me if I'm wrong. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
Check the response to your other discussion on the same subject titled
'Chart location and source...' Among other tips, it contains information about which two lines generated by the macro recorder have to be swapped to make the code work. As I continue to mention, the macro recorder is a good starting point. However, it is not perfect. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I've been experimenting with the macro recorder and that is a big help, to be sure. I just tried this again, and this time no chart appears in the workbook. xlapp.Workbooks(strXlsFile).Charts.Add xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName As far as I can tell, this is correct code. Please correct me if I'm wrong. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
Check the response to your other discussion on the same subject titled
'Chart location and source...' Among other tips, it contains information about which two lines generated by the macro recorder have to be swapped to make the code work. As I continue to mention, the macro recorder is a good starting point. However, it is not perfect. Apparently you don't know what you're talking about. In the previous post the order was Add.Location, type, source. Switching type and source would be Add.Location, source, type. The macro recorded yields type, source, location. If your previous bad advice had worked, I would not sill be posting about this probelm. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
I think you misjudge Tushar. Perhaps there is a misunderstanding in what he
has said added to some frustration on your part. But Tushar does know a lot more about charting than I do - he is one of the mainstays over in Excel.charting. this worked for me: Sub ABCD() Dim j As Long, strXlsFile As String Dim strSheetName As String, xlapp As Application strXlsFile = "Book3" strSheetName = "Sheet2" j = 1 Set xlapp = Application xlapp.Workbooks(strXlsFile).Charts.Add xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + _ 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Location _ Whe=xlLocationAsObject, Name:=strSheetName End Sub You don't get a chart because your range doesn't point to any data - using that j+1 to indicate the data sheet would be the source of the problem as my best guess - it is pointing to the wrong sheet probably. for example, when you get the blank chart, right click on your blank chart and select source data, then click in the source box and then navigate to the proper sheet and highlight your data area and hit enter and the chart will appear. Anyway, when the reference pointed to the data, the above code worked fine. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "deko" wrote in message om... Check the response to your other discussion on the same subject titled 'Chart location and source...' Among other tips, it contains information about which two lines generated by the macro recorder have to be swapped to make the code work. As I continue to mention, the macro recorder is a good starting point. However, it is not perfect. Apparently you don't know what you're talking about. In the previous post the order was Add.Location, type, source. Switching type and source would be Add.Location, source, type. The macro recorded yields type, source, location. If your previous bad advice had worked, I would not sill be posting about this probelm. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
this worked for me:
Sub ABCD() Dim j As Long, strXlsFile As String Dim strSheetName As String, xlapp As Application strXlsFile = "Book3" strSheetName = "Sheet2" j = 1 Set xlapp = Application xlapp.Workbooks(strXlsFile).Charts.Add xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + _ 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Location _ Whe=xlLocationAsObject, Name:=strSheetName End Sub Hi and thanks for the reply. I tried the exact same thing but no luck. I'm wondering if I need to exit the outer For Loop before making changes to the chart. Perhaps I need to specify a cell in the worksheet location, in addition the the worksheet name? You don't get a chart because your range doesn't point to any data - using that j+1 to indicate the data sheet would be the source of the problem as my best guess - it is pointing to the wrong sheet probably. I'll try a test with hardcoded parameters and see what happens. for example, when you get the blank chart, right click on your blank chart and select source data, then click in the source box and then navigate to the proper sheet and highlight your data area and hit enter and the chart will appear. But I don't get a blank chart - I don't get any chart at all. The funny thing is the code (the snippet above) does not throw any exceptions - it seems to run fine. But when I open the workbook - no charts. Is there a "visible" switch or something? I've repeated the chart creation process with the macro recorder on and reviewed the code - this is what I get: Sub Macro1() Columns("A:C").Select Charts.Add ActiveChart.ChartType = xlLineMarkersStacked ActiveChart.SetSourceData Source:=Sheets("2000_pr1a").Range("A1:C45"), _ PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="2000_pr1a" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveChart.HasLegend = False ActiveChart.HasDataTable = False End Sub The order of events is Add, Type, Range, Location, Other Stuff - the same as in the code snippet above. Nevertheless, as I mentioned, the result is no chart at all. Anyway, I'll keep playing around with it. Thanks again for the help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
Hi Tom,
Thanks for the support. :) The key to creating a chart is knowing that XL will make a 'best guess' attempt at what the chart should contain based on the contents of the current region. If it contains data XL will create a chart based on its interpretation of that data. If it doesn't, XL will create a chart with one series containing one data point that has a value of 1. What that means is that the Charts.Add() statement creates not an empty chart but one with one or more series. Now, if one were to change the chart type to something incompatible with the current data selection, XL will raise an error. So, to play safe what one needs to do is (1) delete all existing series, (2) set the source range, and then (3) set the type. Alternatively, create a chartobject directly in the worksheet. I don't like this approach because one has to specify the dimensions of the chartobject. Creating a chart and moving it to a worksheet causes XL to set the chartobject's dimensions based on...experientially, I've concluded it centers the chart and makes it 1/2 the height and 1/2 the width of the visible worksheet. Option Explicit Sub ABCD() Dim j As Long, strXlsFile As String Dim strSheetName As String, xlapp As Application Dim x As Chart, i As Integer strXlsFile = "Book1" strSheetName = "Sheet2" j = 1 Set xlapp = Application With xlapp.Workbooks(strXlsFile) Set x = .Charts.Add() For i = x.SeriesCollection.Count To 1 Step -1 x.SeriesCollection(i).Delete Next i x.SetSourceData _ Source:= _ .Worksheets(j + 1).Range("A1:C" & _ .Worksheets(j + 1).Range("C1").End(xlDown).Row), _ PlotBy:=xlColumns x.ChartType = xlBubble3DEffect Set x = x.Location(Whe=xlLocationAsObject, Name:=strSheetName) End With End Sub In the above code, if the chart is created with an empty currentregion (or a currentregion data structure that is incompatible with a bubble chart), and if the charttype is set before the setsourcedata statement, XL will raise an error. Also note that set x=x.location() bit. When the chart location changes, the object to which x was pointing disappeared. So, x must be 're-established.' -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I think you misjudge Tushar. Perhaps there is a misunderstanding in what he has said added to some frustration on your part. But Tushar does know a lot more about charting than I do - he is one of the mainstays over in Excel.charting. this worked for me: Sub ABCD() Dim j As Long, strXlsFile As String Dim strSheetName As String, xlapp As Application strXlsFile = "Book3" strSheetName = "Sheet2" j = 1 Set xlapp = Application xlapp.Workbooks(strXlsFile).Charts.Add xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + _ 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Location _ Whe=xlLocationAsObject, Name:=strSheetName End Sub You don't get a chart because your range doesn't point to any data - using that j+1 to indicate the data sheet would be the source of the problem as my best guess - it is pointing to the wrong sheet probably. for example, when you get the blank chart, right click on your blank chart and select source data, then click in the source box and then navigate to the proper sheet and highlight your data area and hit enter and the chart will appear. Anyway, when the reference pointed to the data, the above code worked fine. -- Regards, Tom Ogilvy |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to embed chart in active worksheet with VBA?
this worked for me:
Did you run this from an Access 2000 module? Sub ABCD() Dim j As Long, strXlsFile As String Dim strSheetName As String, xlapp As Application strXlsFile = "Book3" strSheetName = "Sheet2" j = 1 Set xlapp = Application xlapp.Workbooks(strXlsFile).Charts.Add xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceD ata Source:= _ xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _ xlapp.Workbooks(strXlsFile).Worksheets(j + _ 1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns xlapp.Workbooks(strXlsFile).ActiveChart.Location _ Whe=xlLocationAsObject, Name:=strSheetName End Sub Anyway, when the reference pointed to the data, the above code worked fine. hmm. still not working for me... :( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can you embed a worksheet within the cell of another worksheet? | Excel Worksheet Functions | |||
How to embed expression in data labels on a chart | Charts and Charting in Excel | |||
how do I embed files to tabs in a worksheet? | Excel Discussion (Misc queries) | |||
Embed Picture of Chart | Charts and Charting in Excel | |||
Why can't I embed a button on a chart in Excel 2003? | Charts and Charting in Excel |