Excel file resource not being released

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
125
Office Version
  1. 365
  2. 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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
Hi,
Replace this: ExcelWorkBook.FileClose
With that: ExcelWorkBook.Close False
 

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
125
Office Version
  1. 365
  2. 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
125
Office Version
  1. 365
  2. 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
125
Office Version
  1. 365
  2. 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,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
RayFrye, glad to know you've solved the problem! :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,718
Messages
5,833,289
Members
430,202
Latest member
Faizal5zl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top