Use eternal .xls for data source in vba

[NaughtyPine]

I need to target an external .xls to create a chart in my project. I have:
- - - - - - - - - - -
With ActiveChart
.ChartType = xlPieExploded
.ChartWizard _
Source:=Sheets(Data).Range(myrange), _
Gallery:=xlLine, Format:=4, PlotBy:=xlRows, _
CategoryLabels:=1, SeriesLabels:=4, HasLegend:=1, _
Title:="", CategoryTitle:="", ValueTitle:="", ExtraTitle:=""
.PlotVisibleOnly = True
End With
- - - - - - - - - - -

But need 'Sheets(Data)' to link to another file so something like 'Sheets([filename.xls]!Data)''

Can anyone help?

 

[SkipVought]

Hi,

I have not played wiht this yet...

With ActiveChart
        .ChartType = xlPieExploded
            .ChartWizard _
            Source:=[b]Workbooks("filename.xls").[/b]Sheets("Data").Range(myrange), _
              Gallery:=xlLine, Format:=4, PlotBy:=xlRows, _
              CategoryLabels:=1, SeriesLabels:=4, HasLegend:=1, _
              Title:="", CategoryTitle:="", ValueTitle:="", ExtraTitle:=""
            .PlotVisibleOnly = True
        End With

Skip,

_[red][/red]

 

[NaughtyPine]

This is not working

Any more ideas?

 

[SkipVought]

Is that other workbook OEPN?


You ought to be able macro record linking to that source in the Chart, and observe the code.

Skip,

_[red][/red]

 

[NaughtyPine]

The recorded macro doesn't help.

 

[SkipVought]

Please post the recorded code that did not help.

Skip,

_[red][/red]

 

[SkipVought]

it will look something like this...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/15/2006 by Skip
'

'
    ActiveChart.SetSourceData Source:=Workbooks( _
        "30-60-90_mach_ALL_2006-08-09.xls").Sheets("0-30").Range("A2:E14"), PlotBy:= _
        xlColumns
End Sub

Skip,

_[red][/red]

 

[NaughtyPine]

Charts.Add
ActiveChart.ChartType = xlRadarMarkers
ActiveChart.SetSourceData Source:=Workbooks("spiderdata.xml").Sheets("Data"). _
Range("A1:K20"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasTitle = False
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom


But i need to use chartWizard and it will not accept Workbooks("spiderdata.xml").

 

[SkipVought]

And WHY do you need the Chart Wizard?

Skip,

_[red][/red]

 

[NaughtyPine]

I found a different solution.

Thanks for the help.

 

[stevexff]

Are you going to tell us what it is? Others might find it useful...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[NaughtyPine]

Oh yeah,

I simply made my file open the other file and copy the cell data into a worksheet in my project. Then i just called the data.

-------------
Dim wb As Workbook
Set wb = Workbooks.Open"filename.xls", True, True)
' open the source workbook, read only
With ThisWorkbook.Worksheets("Data")
' read data from the source workbook
.Range("A1:K1000").Formula = wb.Worksheets("Data").Range("A1:K1000").Formula
End With

wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory

 

[SkipVought]

You could PROBABLY do the same thing without any code at all, using MS Query, via Data/Get External Data.

Skip,

_[red][/red]