<bold> Opening Excel files using VB, cont. </bold>

[mike777]

My last question, "Open Excel file using VB" received an EXCELLENT answer from NicholasUK. Thank you, NicholasUK.
Continuing, I would like to know how to make sure that only one instance of Excel is open when the command button to open the new Excel file is clicked. OK, this is confusing.
Let me try this: I have a VB form where the user picks one of 19 Excel files to open. The user can open one file, then toggle back to the form and open another. If they do this, I want the first one opened to be closed automatically, prompting for a save, etc.
I can use
myExcel.workbooks("myfile.xls&quot.close
but the problem is that I can't give the name of the open file, since it could be any one of the 19 choices.
Thanks for your help.
Mike Kemp
kempmike65@aol.com [sig][/sig]

 

[dsi]

Try using the Name property.

Option Explicit
Private Sub Command1_Click()
Dim xlApp As Object, xlWB As Workbook, sTmp As String
Set xlApp = GetObject(, "Excel.Application&quot
For Each xlWB In xlApp.Workbooks
sTmp = sTmp & xlWB.Name & vbCrLf
Next
MsgBox sTmp
End Sub
[sig][/sig]

 

[dsi]

I just saw your first post. Use the above with the myExcel object in the solution from nicholasuk. [sig][/sig]