how to close an excel file automated by vb

[Hema Suresh]

Hi all

I am going mad of this problem
Please someone help me

I used vb and excel to save a database

I used oXLBook.Save "To save the file"
oXLBook.Close "To close it properly
oXLApp.Quit " To close the Excel application
Set oXLApp=Nothing
and everything is perfect

My VB form has a Menubar which has "Open, Search, Switch, Exit" menu items

Switch and Exit dont have problems

But When I want to open an Excel sheet, I click Open menu item. The following is the code I used to open an excel file

*************** *************** ************

Dim TheYear As String
Dim TheDirectory As String
Dim TheExtension As String
Dim TheFileName As String
Dim FileCheck As String
ThePassword = "Password"

TheYear = Format(Now, "yyyy")
TheDirectory = "D:\Hema\Bo oks DB\"
TheExtension = ".xls"
TheFileName = TheDirectory + TheYear + TheExtension

Set oXLApp = New Excel.Applicati on

Set oXLBook = oXLApp.Workbook s.Open(TheFileN ame)

For Each oXLSheet In oXLBook.Workshe ets
oXLSheet.Protec t Password:=ThePa ssword
Next oXLSheet
oXLApp.Visible = True

*************** *************** *************** ********

Using this code, the Excel file is opened, and I can see the records

But I couldnt give code to close the Excel file explicitly such as oXLBook.Close because if I give that code to close the excel file explicitly, the excel file blinks and go off and it is not visible


After sometimes my Excel files are automatically changing to read-only mode. I doubt as I have not coded explicitly to close the excel file, I am getting this problem

I need my Excel file to be opened even I give the code oXLBook.Close

Or is there any other solution for this

Any help is truly helpfull

Thank u

[MikeTheBike]

Hi

I think if you add this line before you close the said workbook Excel will not close

If oXLApp.Workbook s.Count = 1 Then oXLApp.Workbook s.Add
oXLApp.Visible = True
oXLBook.Close True

I believe that automation assumes that if no workbooks are open you do not want Excel open (which usualy I don't) even if it is visible!

HTH


MTB