Quick boolean question

[Alpha03]

Below is a VB program I wrote to add records to an Excel file. Under Command3_Click() you will see that I have programmed the button to simply open the file to view it's contents. Now lets say I click that button, open the workbook, then close the workbook (manually, not through the VB program), and then click that same button to re-open the file. If I were to do this it gives me an error because by manually closing out the file when I first open it to view, it is not opened as necessary to view it the second time I click the Command3_Click() button. I tried inserting something like the following:

If objExcel.Workbooks.Open = False
objExcel.Workbooks.Open
End If

This, however, does not work, but now I'm sure you can see where I am going with this. How do I test to see if the workbook is opened? Any help? Thanks.

Dim objExcel As Object

Private Sub Command1_Click()
  If Text1.Text = "" Or Text2.Text = "" Or Combo3.Text = "" Or Text4.Text = "" Or Text5.Text = "" Or _
      Text6.Text = "" Or Text7.Text = "" Or Text8.Text = "" Or Text9.Text = "" Then
    MsgBox "Empty field(s).", , "Invalid Process"
    Exit Sub
  Else
    Dim numRecords As Integer
    numRecords = objExcel.Worksheets("Main").Range("B5").Value
    currentRow = numRecords + 7
    objExcel.Worksheets("Main").Range("A" & currentRow).EntireRow.Insert
    currentRow = currentRow + 1
    objExcel.Worksheets("Main").Range("A" & currentRow & ":" & "I" & currentRow).Copy
    currentRow = currentRow - 1
    objExcel.Worksheets("Main").Paste Destination:=objExcel.Worksheets("Main").Range("A" & currentRow)
    currentRow = currentRow + 1
    objExcel.Worksheets("Main").Range("A" & currentRow).Value = Text1.Text
    objExcel.Worksheets("Main").Range("B" & currentRow).Value = Text2.Text
    objExcel.Worksheets("Main").Range("C" & currentRow).Value = Combo3.Text
    objExcel.Worksheets("Main").Range("D" & currentRow).Value = Text4.Text
    objExcel.Worksheets("Main").Range("E" & currentRow).Value = Text5.Text
    objExcel.Worksheets("Main").Range("F" & currentRow).Value = Text6.Text
    objExcel.Worksheets("Main").Range("G" & currentRow).Value = Text7.Text
    objExcel.Worksheets("Main").Range("H" & currentRow).Value = Text8.Text
    objExcel.Worksheets("Main").Range("I" & currentRow).Value = Text9.Text
    Text1.Text = ""
    Text2.Text = ""
    Combo3.Text = ""
    Text4.Text = ""
    Text5.Text = ""
    Text6.Text = ""
    Text7.Text = ""
    Text8.Text = ""
    Text9.Text = ""
  End If
End Sub

Private Sub Command2_Click()
  objExcel.ActiveWorkbook.Save
  objExcel.Workbooks.Close
  Set objExcel = Nothing
  End
End Sub

Private Sub Command3_Click()
  objExcel.ActiveWorkbook.Save
  objExcel.Visible = True
  Set objExcel = Nothing
  End
End Sub

Private Sub Form_Load()
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Workbooks.Open ("C:\My Documents\XXX.xls")
End Sub

 

[mrmovie]

whats the error message???

i think the problem might be your

Set objExcel = Nothing

This is in the Command3_Click sub.
so you destroy the objExcel reference.

You create the objExcel in the Form_Load sub.
so if you destory and then try and re-use it you must re-create the objExcel, i.e. re-run the Form_Load.

or put the objExcel somewhere else so you can call it at will, or dont bother destorying the objExcel in the Cmd_click, have the destorying somewhere else??

 

[Alpha03]

Oh whoops...the code that I placed in my previous post was the current VB code that I have. The one I'm working with is below:

Dim objExcel As Object

Private Sub Command1_Click()
  If Text1.Text = "" Or Text2.Text = "" Or Combo3.Text = "" Or Text4.Text = "" Or Text5.Text = "" Or _
      Text6.Text = "" Or Text7.Text = "" Or Text8.Text = "" Or Text9.Text = "" Then
    MsgBox "Empty field(s).", , "Invalid Process"
    Exit Sub
  Else
    Dim numRecords As Integer
    numRecords = objExcel.Worksheets("Main").Range("B5").Value
    currentRow = numRecords + 7
    objExcel.Worksheets("Main").Range("A" & currentRow).EntireRow.Insert
    currentRow = currentRow + 1
    objExcel.Worksheets("Main").Range("A" & currentRow & ":" & "I" & currentRow).Copy
    currentRow = currentRow - 1
    objExcel.Worksheets("Main").Paste Destination:=objExcel.Worksheets("Main").Range("A" & currentRow)
    currentRow = currentRow + 1
    objExcel.Worksheets("Main").Range("A" & currentRow).Value = Text1.Text
    objExcel.Worksheets("Main").Range("B" & currentRow).Value = Text2.Text
    objExcel.Worksheets("Main").Range("C" & currentRow).Value = Combo3.Text
    objExcel.Worksheets("Main").Range("D" & currentRow).Value = Text4.Text
    objExcel.Worksheets("Main").Range("E" & currentRow).Value = Text5.Text
    objExcel.Worksheets("Main").Range("F" & currentRow).Value = Text6.Text
    objExcel.Worksheets("Main").Range("G" & currentRow).Value = Text7.Text
    objExcel.Worksheets("Main").Range("H" & currentRow).Value = Text8.Text
    objExcel.Worksheets("Main").Range("I" & currentRow).Value = Text9.Text
    Text1.Text = ""
    Text2.Text = ""
    Combo3.Text = ""
    Text4.Text = ""
    Text5.Text = ""
    Text6.Text = ""
    Text7.Text = ""
    Text8.Text = ""
    Text9.Text = ""
  End If
End Sub

Private Sub Command2_Click()
  objExcel.ActiveWorkbook.Save
  objExcel.Workbooks.Close
  Set objExcel = Nothing
  End
End Sub

Private Sub Command3_Click()
  objExcel.ActiveWorkbook.Save
  If objExcel.Workbooks.Open = False ' Does not work
    objExcel.Workbooks.Open          ' Does not work
  End If                             ' Does not Work
  objExcel.Visible = True
End Sub

Private Sub Form_Load()
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Workbooks.Open ("C:\My Documents\XXX.xls")
End Sub

Sorry for the mix-up.

 

[PHV]

Replace this:
objExcel.ActiveWorkbook.Save
If objExcel.Workbooks.Open = False
objExcel.Workbooks.Open
End If
By this:
If objExcel.Workbooks.Count = 0
objExcel.Workbooks.Open "C:\My Documents\XXX.xls"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244