VBA Excel Code, excel.exe will not close

Question

I have been trying to get this bit of code working for 3 days, and I can't figure it out.

This code is running in an external application and calling the Microsoft Excel 14 Library.

I am merging all CSV files in a folder in to one xlsx file then add a graph in each Worksheet to correspond to the data that is in there. But When I add the graph I can't get Excel.exe to close, causing issues when I try to run the code again.

I can run the code fine one time, then I get a Run-Time error 91 "Object Variable or with Block variable not Set. If I debug the code, it complains about ActiveSheet.Columns("A:F").AutoFit.

I believe this is because Excel did not close properly. I can still see Excel.exe in the task manager I need to end the task to get it to work again. I am extremely new to VBA, So I might be missing the proper way to close an object.

*Ok here is a little bit smaller code.

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

'Setup workbooks
Dim wB As Excel.Workbook
Dim wBM As Excel.Workbook

'Csv files folder
Dim CSVfolder As String
CSVfolder = "C:\Dynamics\HistDataReport\12-10-14"

'Master Excel file path
Dim mF As String
mF = "C:\Dynamics\HistDataReport\NewWS.xlsx" 'Where your master file is

'open the master file
Set wBM = xlApp.Workbooks.Open(mF)

'search and open the client files
Dim fname As String
fname = Dir(CSVfolder & "\*.csv")
Do While fname <> ""
   'open the client file
   Set wB = xlApp.Workbooks.Open(CSVfolder & "\" & fname)
   'copy the first sheet from client file to master file
   wB.Sheets(1).Copy After:=wBM.Sheets(wBM.Sheets.Count)
   'Add Graph
   ActiveSheet.Columns("A:F").AutoFit
   ActiveSheet.Range("B1:B673").Select
   Charts.Add
   ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
   'save master file
   wBM.Save
   'close client file
   wB.Close False
   'move to next client file
   fname = Dir()
Loop

xlApp.Visible = True
Set xlApp = Nothing

If I comment all of that out that is works. But I don't get my graph that I need.

I got this code from here Stackoverflow Code

Here is the original Code that I am going to use when I figure out this problem

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim wsht As Excel.Worksheet
Dim strFileName As String
Dim lastRow As Integer


Set xl = New Excel.Application
xl.DisplayAlerts = False
Set wbk = xl.Workbooks.Add("C:\Dynamics\HistDataReport\NewWS.xlsx")


wbk.SaveAs FILEPATH & Filename2
wbk.Close
Set xl = Nothing


Dim wB As Excel.Workbook
Dim wBM As Excel.Workbook
Dim myChart, As Chart, cht As ChartObject
Dim rngChart As Range, destinationSheet As String

'Csv files folder
Dim CSVfolder As String
CSVfolder = FILEPATH
'Master Excel file path
Dim mF As String

'mF = Application.CurrentProject.path & "\Master.xlsx" 'Where your master file is

 mF = FILEPATH & Filename2
'open the master file
 Set wBM = xlApp.Workbooks.Open(mF)
'search and open the client files
 Dim fname, wS As String
 fname = Dir(CSVfolder & "\*.csv")

 Do While fname <> ""
'open the client file
 Set wB = xlApp.Workbooks.Open(CSVfolder & "\" & fname)
'copy the first sheet from client file to master file
 wB.Sheets(1).Copy After:=wBM.Sheets(wBM.Sheets.Count)
 ActiveSheet.Columns("A:F").AutoFit
 destinationSheet = ActiveSheet.Name
 Set myChart = Charts.Add
 Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:=destinationSheet)
 myChart.SetSourceData Source:=ActiveSheet.Range("B1:B673"), PlotBy:=xlColumns
 myChart.ChartType = xlLine
 ActiveSheet.ChartObjects(1).Activate
 Set cht = ActiveChart.Parent
 Set rngChart = ActiveSheet.Range("H2:Q15")
 cht.left = rngChart.left
 cht.Top = rngChart.Top
 cht.Width = rngChart.Width
 cht.Height = rngChart.Height
 myChart.HasTitle = True
 myChart.ChartTitle.Text = "Week"
 myChart.HasLegend = False
 ActiveSheet.Range("A2").Select


 wBM.Save
 wB.Close False
'move to next client file
 fname = Dir()
 Loop
 wB.Close
 wBM.Close
 Set xlApp = Nothing

Any help would be appreciated!!

Answer 1

I believe you need both the xlApp.Quit AND the set xlApp= Nothing.

I have some code that works with hidden excel workbooks and I have this piece of code at the start before I do anything, just in case a previous iteration of the code is still there.

    'Switch off the error handling and the display alerts to avoid any error messages if the old workbook has
      'never been opened and the hidden instance does not exist
    Application.DisplayAlerts = False
   On Error Resume Next
        book.Close SaveChanges:=False
        app.Quit
        Set app = Nothing
    Application.DisplayAlerts = True