Problems with VB6 and excel

[williamrojas78]

Hi

I am developing this application where i have to create a report in excel; in other words i am writing to an excel file from VB. The application writes the contents of a grid into the excel file. The first time I run it it works fine, but the second time i get the next error: "Variable not set or with block without end with". I don;t have a with block where the program crashes, and all my variables are released at the end of the procedure. Does anybody know what is going on? has anybody had this problem before?

Thanks in advance.

Here is the code:

Dim oXL As excel.Application
      
      Dim oWB As excel.Workbook
      Dim oSheet As excel.Worksheet

      Dim bolExist As Boolean

      Dim strCell_Value As String
      Dim i As Integer
      
      Dim num_cells As Integer
      
      
    Set oSheet = Nothing
    Set oWB = Nothing
    Set oXL = Nothing
      
       
        ' Start Excel and get Application object.
        Set oXL = CreateObject("Excel.Application")
        oXL.Visible = True
        
        ' Get a new workbook.
        Set oWB = oXL.Workbooks.Add
        oWB.SaveAs (file_name)    ' save it
        
        
         

      Set oSheet = oWB.ActiveSheet
      oSheet.Name = "Event_Log"
        
        ' Add data from grid to the table
       
        For J = 1 To frmGraphs.MSFlexGrid1.Rows - 1
                                    
                
                ' Operator
                strCell_Value = frmGraphs.MSFlexGrid1.TextMatrix(J, 5)
                oSheet.Cells(J + 1, 1).Value = strCell_Value
                
                ' Date
                strCell_Value = frmGraphs.MSFlexGrid1.TextMatrix(J, 3)
                oSheet.Cells(J + 1, 2).Value = strCell_Value
                
                ' Comments
                strCell_Value = frmGraphs.MSFlexGrid1.TextMatrix(J, 4)
                oSheet.Cells(J + 1, 3).Value = strCell_Value
                
                ' Gross Weight
                strCell_Value = frmGraphs.MSFlexGrid1.TextMatrix(J, 2)
                oSheet.Cells(J + 1, 4).Value = strCell_Value
                
                'i = i + 1   ' increase the cell (row) number
                
        
        Next J
        
        
        oSheet.Range("A1").Activate       ' program crashes here
        oSheet.Rows(i & ":" & i).Insert Shift:=xlDown
       
        oSheet.Range("A" & i & ":" & "D" & i).Select

    oSheet.Range("A1").Select
    oWB.Save
          
    ' Make sure Excel is visible and give the user control
    ' of Microsoft Excel's lifetime.
    oXL.Visible = True
    oXL.UserControl = True
           
    'oXL.Workbooks.Close
    'oXL.Quit
    
    'release the variables
        
    Set oSheet = Nothing
    Set oWB = Nothing
    Set oXL = Nothing

[Comatose]

Yup.

You set the objects back to nothing (destroying them) before you ever use them in the code:

Dim oXL As excel.Application
      
      Dim oWB As excel.Workbook
      Dim oSheet As excel.Worksheet

      Dim bolExist As Boolean

      Dim strCell_Value As String
      Dim i As Integer
      
      Dim num_cells As Integer
      
      
    Set oSheet = Nothing
    Set oWB = Nothing
    Set oXL = Nothing

You need to use them to do the reading or writing to excel BEFORE setting them to nothing. ;)

[williamrojas78]

Thanks for your reply,

I know; I did that because I thought the objects were not being released properly at the end of the procedure, so I wanted to make sure they were "Nothing" before i started working with them. The result is the same if i delete those three lines.

[williamrojas78]

Okay

What i had to do is to replace all the "with selection" by "oSheet.range(xx)" and that took care of the problem, i don't know why.

Regards