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()
  If objExcel.Workbooks.Open = False ' Does Not Work
    objExcel.Workbooks.Open          ' Does Not Work
  End If                             ' Does Not Work
  objExcel.ActiveWorkbook.Save
  objExcel.Visible = True
End Sub

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

 

[mrmovie]

isnt this a repost?
i dont know of a IsOpen method or properties but would this work?

Dim sFileName As String
Dim bOpen As Boolean

sFileName = "XXX.xls"
bOpen = False

For Each aWorkBook In objExcel.Workbooks
If LCase(aWorkbook.Name) = LCase(sFileName) Then
bOpen = True
End If
Next

If bOpen = False Then
objExcel.Workbooks.Open ("C:\My Documents\" & sFileName)
End If

 

[Alpha03]

Na, that doesn't work. Thanks for your help though. Does anyone else know how to check to see if the file is open or closed?

 

[TonyJollans]

Hi Alpha03,

mrmovie's logic looks good. What exactly goes wrong?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Alpha03]

I might be misinterpreting what mrmovie's code is supposed to do. Can you explain it to me? Or better yet, if it isn't too much trouble, can you tell me exactly what code I would have to insert into my already existing program to achieve what I want. Keep in mind that I am working with an Excel file (one workbook) in which consists of a single worksheet. Thanks for all the help so far guys. Much appreciated.

 

[TonyJollans]

Hi Alpha03,

Excel does not have a simple way of checking if a Workbook is open. You can either try to access it and trap the error if it fails, or do as mrmovie suggests, and check all open Workbooks to see if any of them is the one you are interested in. You should be able to put his code, as is, into your routine to replace the failing code - obviously you have some more code which you haven't posted but just leave that alone - simply replace the three failing lines with the code mrmovie has posted.

The only thing to note, perhaps, is the file name literal he uses. They are the same as the ones you have posted (but you may have edited before posting - a wild guess based on the meaningles name)

The Excel application (pointed to by your objExcel variable) has a Workbooks Collection - in your case this collection may only have zero or one members but that doesn't matter, it is still a valid collection. The code loops through this, checking the name of each (converted to Lower Case) against the name of your workbook. If it finds a match, it sets the flag, bOpen, true - if not, it stays False. If the flag is false afterwards, your workbook is not open, so the code opens it again.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Alpha03]

I inserted the code given by mrmovie exactly as he gave it and it worked perfectly. I should've done that in the first place. It just didn't seem like it would even come close to accomplishing what I wanted, but it did exactly just that. Now that I know it works, I'm interested in knowing exactly why it works. "aWorkBook" is a user-defined variable if I'm interpreting this correctly. More specifically it is an integer. So far, so good? Now in the "For" loop, the word "Next" increases "aWorkBook" by 1 and repeats the code in the "For" loop. If I'm on the right track, where does the integer number come from? Does the program designate the first file opened as number 0? Or does this number come from the Excel file itself? Now since I'm working with one, and only one, workbook, could I just code the following:

If 0.Name = sFileName Then
  bOpen = True
End if

Would this work? I don't see why I would need a loop to test what seems to be multiple workbooks when I'm only working with one. I'm not being picky, I'm just trying to learn these things so I understand better Thanks for all the help fellas, much appreciated.

 

[mrmovie]

No sorry I should really have declared aWorkBook for you.
Because I didnt declare aWorkbook (leaves me a little short in another post i am going to harp on about today ;-)) excel creates it with type 'Variant' this means it is ready to hold any type of data.

In our case

For Each aWorkbook In objExcel.Workbooks

The important this is objExcel.Workbooks. If you have a look at the help info you will find this method(right word?) returns a 'Collection' of Workbook objects.

So, really we should have done the following to make things clearer and prob saved memory.

Dim aWorkbook As Workbook

So the For Each Loop simply loops through all return workbook objects in the Collection of workbooks returned.

I hope this makes things clearer for you.