VBA Can't open available Excel file

You might have XL open already?

Try:

On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
Set xl = CreateObject("Excel.Application")

Also, I ...
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object

But not sure if that would make the difference.

HTH

The problem was the "xl.Quit" line. I do not know why. Yes Excel is opened by my procedure code and I thought I was closing it too. It seems to work over and over now w/o having to restart and in some cases re-start the computer to release the files that get locked.

I am struggling with managing the files that this app opens and closes.
Do you know how to properly manage this. ie: I have a word mail merge vba app that opens closes many files. How can I open and close them while the app runs so the person can open these files and have a look at them w/o quitting the app?

I know there is a document object. It has an index. ie: document(1), document(2) etc..
I seem to have no control over these. If I have a template open I want to use it to create my "save as" copy with the merged data in it then I want to close the template. But I end up closing everything including the very app itself which is embedded in a mail merge document.

I'm using statements like this:

Documents.Open FileName:=FileName etc..
ActiveDocument.MailMerge.OpenDataSource Name:=

ActiveDocument.MailMerge.DataSource.Close

I have similar code, though admit that I've only modified and studied it.  I did find that when I used it in my New_Document sub that it would routinely crash.  I removed it from there and can call it 50 or so times in a heartbeat without fail.  Open and close every time.

Here is how my files are closed:

If bstartapp = True Then
    xlbook.Save
    xlbook.Close
    xlapp.Quit
End If

Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

End Sub

Select allOpen in new window

Note that bstartapp is a booleen that is set to true if the app was not already open.

For debugging purposes I also put in a counter to tell me how many sheets were open, it yielded some surprising results.  The counter would return a +ve number (21 to be precise) which would be determined from debug.print and the hit a Stop line.

If I then typed Set xl = CreateObject("Excel.Application") into the immediate window everything would correct itself... it was unituitive.  In the end my frustrations seemed to be related to word starting up a new document and there not being sufficient opportunity to pass control back to the OS.  I tried a sprinkling of DoEvents but that did not help.  Eventually, I removed the call to this sub from the startup and found another way to call it.

HTH

Is it true that any file that the app opens and closes during run time cannot be accessed outside of the app till the app shuts down?

I don't think that is a true statement.  

I just opened my XL file (using VBA late binding) extracted some info and inserted into word. Left word open with a bunch of VBA still running and then manually opened XL without issue.

last question:

I have the following: it uses a word mail merge template and saves a copy that has the data from the source excel file. I save it and close in the code. I leave the app running. But this copy is locked by the app still. The source file is released. The template is released. The resulting document is not released. Any resulting document is not released till the app closes. Can I release resulting docs while the app is running?

What you may need to know is that the app is running in behind a mail merge doc itself. It never gets changed its just used as the template for a mail merge.

ChangeFileOpenDirectory UserForm1.txtPathToMergedDocs.Text
    ActiveDocument.SaveAs2 FileName:=FileToSave, _
        FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
        AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
        :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14
    ActiveWindow.ActivePane.VerticalPercentScrolled = 0
           
    ActiveDocument.Close (False) 'should close the newly created file. yes it does.
    Documents(FileName).Close (False) 'should close the template used. YES it does.

Just a guess but perhaps you need to set the document to nothing in order to release?

In your project do you have your code in behind a mail merge document? Or is it in a word doc that just has a form built that employs/manipulates other Word docs only.
How do you make your Word VBA form available to your user?
That code I showed you in the last post was taken via the macro recorder so I don't think "set object to nothing" would be recognized.

For me the data source file, excel is fine. Its the docs that get created from it that are locked while the app runs. I'll take one last crack at it before I close this issue out with you.

Thanks again for your help.

My code is not part of a mail merge as such. I use it to write constants to vba - like a configuration file. User would not be aware that it does anything. Basically I am only working with 1 doc at a time.  The xl file data is associated with the document template. I can open and close documents without any issues.

I am on my phone so struggle to get an example showing how to set something to nothing.