[RESOLVED] Saving an Excel file using VB6

[nightheart]

Hi, I do have a goup of questions which I need help to resolve:
1- The code below creates a pie chart using Excel and it displays fine, but with no legend at all, while the legend is displayed on the excel sheet?
2- Is there any possibility to run that code without the need to save the excel file? If there is not, is it possible to save the file without asking the user to confirm the save or the replace?
3- I want to delete the excel file created during the chart creation.
4- for the deleteion i want to make the file path as a variable, is it possible to do that.

1.  Dim exapp As Excel.Application
2.  Dim wb As New Excel.Workbook
3.  Dim ws As Excel.Worksheet
4.
5.  Set exapp = CreateObject("Excel.Application")
6.  Set wb = exapp.Workbooks.Add
7.  Set ws = wb.Worksheets("Sheet1")
8.  ws.Name = "Data"
9.
10. exapp.Visible = True
11. With exapp
12. exapp.Range("A1").CopyFromRecordset rsc
13. .Charts.Add
14.    .ActiveChart.ChartType = xlPie
15.    .ActiveChart.SetSourceData Source:=Sheets("Data").Range("A1:A16"), 16.  PlotBy:=xlColumns
17.    .ActiveChart.SeriesCollection(1).XValues = "=Data!R1C2:R16C2"
18.    .ActiveChart.Location Where:=xlLocationAsNewSheet
19.
20.    .ActiveChart.PlotArea.Select
21.    .Selection.Width = 400
22.    .Selection.Height = 400
23.    
24.    
25.    
26.    End With
27.
28.    exapp.ActiveWorkbook.Save
29.        If exapp.ActiveWorkbook.Saved = True Then
30.        frmAllChart.Show
31.        frmAllChart.OLE1.CreateEmbed exapp.ActiveWorkbook.Path_
32.               & "\" & exapp.ActiveWorkbook.Name
33.        frmAllChart.OLE1.Refresh
34.        End If
35.        
36.    exapp.Quit
37.    
38.    
39.  End Sub

[westconn1]

activeworkbook.fullname will return the complete path/filename

to delete the workbook when you are finished

myfile = activeworkbook.fullname 'before closing workbook
kill myfile' after closing workbook

i don't see any reason why you need to save the workbook at all
ThisWorkbook.Close SaveChanges:=False

[nightheart]

Thank you very much westconn1 for the reply, it helped so much.
Still one point missing which is the legend of the chart it do appear on the excel sheet but don't in the VB form.

[westconn1]

the legend is on the worksheet not the chart sheet?

i have no idea why this would be

This example turns on the legend for Chart1 and then sets the legend font color to blue.

Charts("Chart1").HasLegend = True
Charts("Chart1").Legend.Font.ColorIndex = 5

Remarks

The chart legend isn’t visible unless the HasLegend property is True. If this property is False, properties and methods of the Legend object will fail.

[nightheart]

I did add the part you advised and changed the selection to ChartArea instead of PlotArea, but no success yet.

With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "All Cases"
        .HasLegend = True
        .Legend.Font.ColorIndex = 5
    End With

.ActiveChart.ChartArea.Select

[nightheart]

It was only the OLE object need to be resized in order to display everything. Thank your very much westconn1.

[zeezee]

Hey NightHeart, something I want to say about your code.
You have used Active Chart and Workbook in your code. I also used this before Si_the_Geek pointed out its not good to use them. See the following

http://www.vbforums.com/showthread.php?t=391665
http://www.vbforums.com/showthread.php?t=485465 Post # 5

So I suggest you change your code to avoid them

[westconn1]

You have used Active Chart and Workbook in your code. I also used this before Si_the_Geek pointed out its not good to use them. See the following

i totally agree should not use select, selection,active or activate, unless there is a specific reason for doing so

address all ranges and sheets by name or setting them as ranges or worksheets as appropriate

[nightheart]

Thank you very much