Updateing OLE object VB6???

[aa9gg]

Hi....
My program determines a persons age and depending on the age loads the appropriate Excel graph. I initially inserted an Excel graph on my form as an OLE object. The problem is the original graph is the only one that displays.
I tried changing OLE1.SourceDoc but still always displays the original inserted graph. Here's what I have.....

Private Sub Form_Load()
    Dim Rpath As String
    Dim age_test As Integer
    
    age_test = Val(Patient_Info2.C_Age.Text)
    
    If ((age_test >= 6) And (age_test <= 10)) Then
      Rpath = "csv_1000_E_6_10.xls"
      End If
      
    If ((age_test >= 11) And (age_test <= 19)) Then
      Rpath = "csv_1000_E_11_55.xls"
      End If
    
    If ((age_test >= 20) And (age_test <= 55)) Then
      Rpath = "csv_1000_E_20_55.xls"
      End If
    
    If ((age_test >= 56) And (age_test <= 75)) Then
      Rpath = "csv_1000_E_56_75.xls"
      End If
    
    chk_age.Caption = age_test
    prog.Caption = Rpath  '  path is always correct based on the age
    OLE1.SourceDoc = Rpath    ' doesn't seem to work!!!!
    
    Set xlwbook = xl.Workbooks.Open(Rpath)
    Set xlsheet = xlwbook.Sheets.Item(2)

    'Text1(0).Text = xlsheet.Cells(5, 2) ' row 2 col 1
    'Text1(1).Text = xlsheet.Cells(5, 3) ' row 2 col 2
    'Text1(2).Text = xlsheet.Cells(5, 4) ' row 2 col 2
    'Text1(3).Text = xlsheet.Cells(5, 5) ' row 2 col 2
  
'    xlsheet.Cells(5, 2) = 1.7
'    xlsheet.Cells(5, 3) = 2
'    xlsheet.Cells(5, 4) = 1.6
'    xlsheet.Cells(5, 5) = 1.3
    
    xlsheet.Cells(5, 2) = Val(ETESTFACE.Zone1_Result) ' row 2 col 1
    xlsheet.Cells(5, 3) = Val(ETESTFACE.Zone2_Result) ' row 2 col 2
    xlsheet.Cells(5, 4) = Val(ETESTFACE.Zone3_Result) ' row 2 col 2
    xlsheet.Cells(5, 5) = Val(ETESTFACE.Zone4_Result) ' row 2 col 2

    Right_A.Caption = Val(ETESTFACE.Zone1_Result)
    Right_B.Caption = Val(ETESTFACE.Zone2_Result)
    Right_C.Caption = Val(ETESTFACE.Zone3_Result)
    Right_D.Caption = Val(ETESTFACE.Zone4_Result)
    
    xlwbook.Save
    OLE1.Update  ' <-- gives me a No Object Error!

End Sub

....what am I doing wrong???

thanks

[aa9gg]

Found it....

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

This ALMOST works correctly. For some reason even though the data IS put into Excel, the graph does'nt update correctly until you call the routine a 2nd time!!! Any ideas???

[si_the_geek]

Please do not post the same question to multiple threads (other thread [thread=http://www.vbforums.com/showthread.php?t=573027]here[/thread]).

This thread is now closed.