Task Manager process Excel.exe still running 2

[scottian]

Using the code below the script loops through an Excel file and copys a ref number and pastes it to a cell further along the row (I'm just testing at the minute). Once its finished I try to open the spreadsheet but i get error 'File is already open exclusively by me do i want open it as read only'. Then if i try to run the code again another error appears saying i cant save the file do i want to save a copy? I check the Task Manager processes and Excel.exe is still open. So im thinking perhaps i need another line of code that closes the spreadsheet, am i right?

'========================================================
Sub Main()
Dim xlApp As Object
Dim xlWB As Object
Dim xlFile As String
Dim CurrCell As string
Dim CurrRow As integer
Dim iTotal as string

iTotal = 0
xlFile = "C:\Test.xls"
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open(xlFile)

For CurrRow = 2 To xlWB.Sheets(1).UsedRange.Rows.Count
CurrCell = xlWB.Sheets(1).Cells(CurrRow, 1)
xlWB.Sheets(1).Cells(CurrRow, 14) = CurrCell
iTotal + CurrCell
next
displayalerts = false
xlWB.save
End Sub
'========================================================

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[pinkgecko]

I would think you need to end with something like
xlwb.Close(SaveChanges:=False) in order to clear it out of memory. Otherwise, you already have an instance of the workbook loaded into memory, even though your current program ends.

[blue]If you're not part of the solution, there's good money to be made in prolonging the problem.[/blue]

 

[WinblowsME]

Yes, you need to add a line to close the workbook, or what PinkGecko said.

Sub Main()
   Dim xlApp As Object, xlWB As Object, xlSheet As Object
   Dim xlFile As String, i As Long
   
   xlFile = "C:\Test.xls"
     
   Set xlApp = CreateObject("Excel.Application")
   Set xlWB = xlApp.Workbooks.Open(xlFile)
   Set xlSheet = xlWB.Sheets(1)
         
   For i = 1 To xlSheet.UsedRange.Rows.Count
      xlSheet.Cells(i, 14) = xlSheet.Cells(i, 1)
   Next i
   
   xlApp.DisplayAlerts = False
   xlWB.Save
   [COLOR=red]xlWB.Close[/color]
      
   Set xlSheet = Nothing
   Set xlWB = Nothing
   Set xlApp = Nothing
End Sub

 

[scottian]

Thank you very much pinkgecko and WinblowsME , thats what i get for thinking it was harder than it actually is.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks