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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,714
Office Version
2016, 2010, 2007
Platform
Windows
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,714
Office Version
2016, 2010, 2007
Platform
Windows
RayFrye, glad to know you've solved the problem! :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,014
Messages
5,508,802
Members
408,695
Latest member
MarcelCohen

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top