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

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

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

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.

LVL 16

Fabrice LambertConsultingCommented: 2018-08-24

Hi,

your ProcessExcelFile function need to save and close the workbook before quiting, else it remain open:

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.

Lukáš StackeAuthor Commented: 2018-08-24

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()

Ok, I will remove the taskkill command what is at the end.

Additional note:

Set xlBook = xlApp.Workbooks.Open(sFileName, 0, True)

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)

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

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!

VBS automatically reopen Excel file

Who is Participating?