VBS automatically reopen Excel file

Hello Guys,
I have a script which opens all excel files in folder and run a macro in all of them. Unfortunately it keeps asking me that the excel file is already opened and if I would like to reopen it. I wanted to kill the process, but it doesn't really work as it should. Even when I manually kill the process and run the script it is asking me again, if i would like to reopen already open file. The problem with opening of excel occurs in  Sub ProcessExcelFile(sFileName).
Could you please advise what should be changed in bellow code? Thank you.

here is the code:

Const sRootFolder = "C:\test\"

Dim oFSO, oFile 
Dim xlApp, xlBook, objWorkbook 

Start
 oShell.Run "taskkill /f /im EXCEL.EXE", , True



Sub Start()
     Initialize
     ProcessFilesInFolder sRootFolder
     Finish
 End Sub



Sub ProcessFilesInFolder(sFolder)
     ' Process the files in this folder
     For Each oFile In oFSO.GetFolder(sFolder).Files
         If IsExcelFile(oFile) Then ProcessExcelFile oFile.Path
     Next
 End Sub



Sub Initialize()
     Set oFSO = CreateObject("Scripting.FileSystemObject")
     Set xlApp = CreateObject("Excel.Application")   
 End Sub



Sub Finish()
     xlApp.Quit
     Set xlBook = Nothing 
     Set xlApp = Nothing    
     Set oFSO = Nothing
 End Sub

Function IsExcelFile(oFile)
     IsExcelFile = (InStr(1, oFSO.GetExtensionName(oFile), "xls", vbTextCompare) > 0) And (Left(oFile.Name, 1) <> "~")
 End Function

Sub ProcessExcelFile(sFileName)   
     Set xlBook = xlApp.Workbooks.Open(sFileName, 0, True) 
     Set objWorkbook = xlApp.Workbooks.Open(sFileName)     
     xlApp.Run "import_test"
  
 End Sub 
  
 Sub Save()
 xlBook.Save
 xlBook.Close
 xlApp.Quit

oShell.Run "taskkill /f /im EXCEL.EXE", , True
  WScript.Echo "Finished."
 End Sub

Open in new window

Lukáš StackeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Hi,

your ProcessExcelFile function need to save and close the workbook before quiting, else it remain open: 
Sub ProcessExcelFile(sFileName)   
     Set xlBook = xlApp.Workbooks.Open(sFileName, 0, True) 
     Set objWorkbook = xlApp.Workbooks.Open(sFileName)     
     xlApp.Run "import_test"
     xlBook.Save
    xlBook.Close
    Set xlBook = Nothing
 End Sub

Open in new window

0
Fabrice LambertFabrice LambertCommented:
Side note:
Make use of parameters with your function / procedures, to avoid global variables (global variables sux, for many reasons).

And you should not need to kill Excel once the script is done, else that mean you have a bug somewhere that should be addressed.
0
Lukáš StackeAuthor Commented:
Hi,
Thank you for your advise. Firstly I wanted to have it as you said, but bellow code gives me error: Class doesn't support Automation: xlBook.Save, so that is why I created another function- Sub Save()
Sub ProcessExcelFile(sFileName)   
    Set xlBook = xlApp.Workbooks.Open(sFileName, 0, True) 
    Set objWorkbook = xlApp.Workbooks.Open(sFileName)     
    xlApp.Run "import_sap3"
	xlBook.Save
	xlBook.Close
	xlApp.Quit
	Set xlBook = Nothing
	WScript.Echo "Finished."
End Sub

Open in new window


Ok, I will remove the taskkill command what is at the end.
0
Fabrice LambertFabrice LambertCommented:
Additional note: 
Set xlBook = xlApp.Workbooks.Open(sFileName, 0, True)

Open in new window

The workbook is opened in read only mode, ehence why the Save method fail.

And the following line is useless (opening the workbook twice): 
Set objWorkbook = xlApp.Workbooks.Open(sFileName)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lukáš StackeAuthor Commented:
Corrected the last function ProcessExcelFile, removed the line bellow
Set objWorkbook = xlApp.Workbooks.Open(sFileName)

and updated the last attribute from True to False Set xlBook = xlApp.Workbooks.Open(sFileName, 0, False)

seems to be working as it should,
Thank you for your quick help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.