# Solved: Close Excel/csv



## draceplace (Jun 8, 2001)

The code below does what I want it do except it leaves files open in as EXCEL.exe in the task manager. Please advise what I need to add to get this annoying problem to go away.

I adapted the code below to work on a project. You simply drop the xls file on to of the .vbs and It finds the tab 'Participation' and save it as .csv with a modified file name. The problem is it leaves the file open and I have to go into Task Manager and end the EXCEL.exe process before I can open and process the new csv file. I did this for the 12 files I needed but I want to clean it up now. 

I commented out some lines from the origional to get it to work.
If I uncomment this line 
' oExcel.Close False, , False 
Error: Object doesn't support this porperty or method 
'oExcel.Close' 

I'm guessing because it .csv now?? but it still creates the file. and you

'---------------------------------------------------------------
'-- draceplace 09/03/2010----------------------------------------
'--This script is nice and simple way of getting a specific tab and converting xls 
'to csv simply drag and drop the files onto the vbs to use.
'--This finds the Participation tab and save .csv for next process
'--strFile MUST = the folder you want your file to land in.
'--The new csv uses the take the date from the file name "SNAP Reports 01-10.xls"
'and names it 01-10_Participation.csv 
' ---------------------------------------------------------------
Set objArgs = WScript.Arguments
For I = 0 to objArgs.Count - 1

FullName = objArgs(I)
FileName = Left(objArgs(I), InstrRev(objArgs(I), ".") )

Set oExcel = CreateObject("Excel.application")
set oSheet = oExcel.Workbooks.Open(FullName)
'MUST be slash and the end of strFiles
strFiles = "\\serverxx\Annual Report SFY2011\SNAP_Coupons\"

' Get the date part from the file name and remove period
FileName1 = Right(FileName,6)
FileName1 = Replace(FileName1,".","_") 
oExcel.application.visible=false
oExcel.application.displayalerts=false
'Wscript.Echo FileName

'With oExcel.Workbooks.Open(oFile, 0, True, , , , True, , , , False, , False) 
For Each oSheet In oExcel.Worksheets 
'wscript.echo osheet.name
If osheet.name = "Participation" Then
oSheet.SaveAs strFiles & FileName1 & oSheet.Name & _
"." & "csv", 6
End If
Next 
' oExcel.Close False, , False 
'End With 
' objExcelBook.SaveAs FileName & "csv", 23

oExcel.Application.Quit
oExcel.Quit 

Set objExcel = Nothing
set objExcelBook = Nothing

Next


----------



## Squashman (Apr 4, 2003)

I had starting writing something very similar in PowerShell but never got around to finishing it. I did some rudimentary testing and for the most part the logic is very similar but never had any issues closing excel.


----------



## draceplace (Jun 8, 2001)

I change the file extention to xls and got the same result. so my .csv theory is out...

Also I feel like I'm not really creating a csv here (just renaming excel) but in my next step where I retrieve the data I want it works fine.

I thought the oExcel.Application.Quit
would close all Exel as well


----------



## Squashman (Apr 4, 2003)

In Powershell it is very similar.
Set a = CreateObject("Excel.Application")
$a.Quit()


----------



## draceplace (Jun 8, 2001)

I think it was as simple as this change!!

Changed
Set objExcel = Nothing
set objExcelBook = Nothing
to this
Set oExcel = Nothing
set oSheet = Nothing


----------



## draceplace (Jun 8, 2001)

Thanks for the support Squashman.

I'm closing this one! 

If anyone reads this thread and want to re-use the code change the last two lines and point to your files and tab names.

You can save as xls if your just wanting to extract a tab or remove the "If sheet =" statement and extract them all.


----------



## Squashman (Apr 4, 2003)

computerlaptop said:


> Hi
> I need to write a VBA code which will read the CSV files from a folder by opening the CSV files , then copy the contents of csv file into another EXCEL template already present in the same xls template ..
> 
> The XLS template has tabs named like "UAT2D" and i want the csv file data to paste in the particular xls tab which is blank initially .
> ...


You need to start your own thread.


----------

