[RESOLVED] Excel and vb6

[aa9gg]

Hi...
I have a pre-made graph (2 stacked area series and 1 line X/Y series) that I need to put into my VB application. I have the OLE object placed on the form and see the graph, but I need to replace the line X/Y series data with my dynamic application data. The 2 stacked area data series are to remain the same. How do I do this??? Note there are only 4 data points.

Thanks....

Paul Mateer, AA9GG

[aa9gg]

....ok....found it/figured it out....almost!:

Dim xl As New Excel.Application
Dim xlwbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet


Private Sub Form_Load()
    Dim Rpath As String
    
      Rpath = "C:\Program Files\VV\CSV1000\Database\csv_1000_E_6_10.xls"
    
    OLE1.SourceDoc = Rpath
    OLE1.CreateEmbed Rpath
    
    Set xlwbook = xl.Workbooks.Open(Rpath)
    Set xlsheet = xlwbook.Sheets.Item(2)
   
     xlsheet.Cells(5, 2) = Val(Patient_Info2.OS_NG(0)) ' row 5 col 2
     xlsheet.Cells(5, 3) = Val(Patient_Info2.OS_NG(1)) ' row 5 col 3
     xlsheet.Cells(5, 4) = Val(Patient_Info2.OS_NG(2)) ' row 5 col 4
     xlsheet.Cells(5, 5) = Val(Patient_Info2.OS_NG(3)) ' row 5 col 5
    
     xlsheet.Cells(6, 2) = Val(Patient_Info2.OD_NG(0)) ' row 6 col 2
     xlsheet.Cells(6, 3) = Val(Patient_Info2.OD_NG(1)) ' row 6 col 3
     xlsheet.Cells(6, 4) = Val(Patient_Info2.OD_NG(2)) ' row 6 col 4
     xlsheet.Cells(6, 5) = Val(Patient_Info2.OD_NG(3)) ' row 6 col 5
        
    xlwbook.Save
    OLE1.Update

End Sub

Private Sub Form_Unload(Cancel As Integer)
'don't forget to do this or you'll not be able to open
'book1.xls again, untill you restart you pc.
    
    xl.ActiveWorkbook.Close True, Path
    xl.Quit
   
    Set xlwbook = Nothing
    Set xl = Nothing
    Unload Me
End Sub

Only problem is it doesn't update the graph the first time. In other words when I run the routine, it DOES put the data into Access, but the graph OLE object doesn't update. If I immediately run the routine again the graph is correct. Anyone know how to fix this?

[si_the_geek]

Rather than closing the workbook (or rather a random workbook which might be the right one) in Form_Unload, close it when you have finished with it (ie: where you currently save it). The method you currently use is not quite right, see my Excel Tutorial (link in my signature) for the valid methods.

Unless you have other code you aren't showing us that works with Excel, you should also close the application at the same time, and move the variables in to the main routine.

Note that there is no need (ever) to have Unload Me inside Form_Unload, as that event is fired when the form is already unloading. You should only use it elsewhere, when you want to initiate the unloading of the form via code.

I would recommend reading the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum)


In addition to the changes above to correct your usage of Excel, I would recommend only assigning the file to the OLE after you have made your changes (I also doubt you need all 3 lines, but haven't used the OLE control for many years).

This is how I would have the code:

Private Sub Form_Load()
Dim xl As Excel.Application
Dim xlwbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim Rpath As String
    
    Rpath = "C:\Program Files\VV\CSV1000\Database\csv_1000_E_6_10.xls"
    
    Set xl = New Excel.Application
    Set xlwbook = xl.Workbooks.Open(Rpath)
    Set xlsheet = xlwbook.Sheets.Item(2)
   
     xlsheet.Cells(5, 2) = Val(Patient_Info2.OS_NG(0)) ' row 5 col 2
     xlsheet.Cells(5, 3) = Val(Patient_Info2.OS_NG(1)) ' row 5 col 3
     xlsheet.Cells(5, 4) = Val(Patient_Info2.OS_NG(2)) ' row 5 col 4
     xlsheet.Cells(5, 5) = Val(Patient_Info2.OS_NG(3)) ' row 5 col 5
    
     xlsheet.Cells(6, 2) = Val(Patient_Info2.OD_NG(0)) ' row 6 col 2
     xlsheet.Cells(6, 3) = Val(Patient_Info2.OD_NG(1)) ' row 6 col 3
     xlsheet.Cells(6, 4) = Val(Patient_Info2.OD_NG(2)) ' row 6 col 4
     xlsheet.Cells(6, 5) = Val(Patient_Info2.OD_NG(3)) ' row 6 col 5

    Set xlsheet = Nothing
    xlwbook.Close SaveChanges:=True
    Set xlwbook = Nothing
    xl.Quit
    Set xl = Nothing

    OLE1.SourceDoc = Rpath
    OLE1.CreateEmbed Rpath    
    OLE1.Update

End Sub

[aa9gg]

Hi si_the_geek....sorry about the multiple thread

Anyway... your code works GREAT!!!!!!!!!!!THANK YOU!!!!! I'm glad somebody knows how to do this stuff.