Excel file resource not being released

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
365, 2019
I process a number of Excel Named Cells and input them into a Word document.
The code runs from Word.

The code below works properly the first time for multiple Excel Named Cells.

The second time I run the code, the code fails opening the Excel Workbook because the Workbook is locked for reading.
(Even this has me stumped because I never write to the Excel file.)

If I close Word and run the code it again, it works fine ONE time.

I believe I am doing everything to release the Excel resources back to Windows but it doesn't appear to work.

Can I buy a clue?

VBA Code:
'Excel is Not Open
    Set ExcelApp = CreateObject("Excel.Application")
    Set ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelDocumentPath)  

'lot's of code here that works fine the first time.

    On Error Resume Next 'just to be safe
    ExcelWorkBook.FileClose
   Set ExcelWorkBook = Nothing
    
    ExcelApp.Application.Quit
    Set ExcelApp = Nothing
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,677
Hi,
Replace this: ExcelWorkBook.FileClose
With that: ExcelWorkBook.Close False
 

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
365, 2019
Thanks ZVI for trying, but that change does not fix the problem.

This really should not be that big a problem, I've coded in this area for decades and have never seen an issue so illusive.
 

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
365, 2019
These are various combinations I have used...
VBA Code:
ActiveWorkbook.Close SaveChanges:=False

    'Close ExcelWorkBook        'fails
    'ExcelWorkBook.Close True   'fails
    'ExcelWorkBook.Close False  'fails
    'ExcelWorkBook.FileClose    'fails object not supported
    
    Set ExcelWorkBook = Nothing
    ExcelApp.Application.Quit
    
    'ExcelApp.Application = Nothing 'fails
    
    Set ExcelApp = Nothing
 

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
365, 2019
Finally got it to work, removed anything related to the Excel application.

ZVI - Thanks for looking, your input sent me down a path that ultimately fixed the problem.

VBA Code:
'REMOVED ANYTHING RELATED TO THE EXCEL APP
'REMOVED  'Set ExcelApp = CreateObject("Excel.Application")
'MODIFIED 'Set ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelDocumentPath)  'This Excel Document is NOT Open
    
    'This Excel Document is NOT Open
    Set ExcelWorkBook = Workbooks.Open(ExcelDocumentPath)    'note: ExcelApp.  removed
    
    'This VBA code in this Word Document and this Document is open
    Set WordDoc = GetObject(WordDocumentPath)
     
    'SOME CODE THAT WORKS HERE
              
    ActiveWorkbook.Close SaveChanges:=False
    Set ExcelWorkBook = Nothing
      
'REMOVED ANYTHING RELATED TO THE EXCEL APP
'REMOVED  'ExcelApp.Application.Quit
'REMOVED  'ExcelApp.Application = Nothing
'REMOVED  'Set ExcelApp = Nothing
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,677
RayFrye, glad to know you've solved the problem! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,592
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top