How do I stop Excel from running in the background?

[Chandni Kapoor]

Can you please help me. I tried to save my excel file to the defined location. My code is working fine (Excel file copied to the define location successfully) but whenever i tried to open excel file. it gives an error (file is locked for editing). somehow VBA Excel File staying open in the background. I need to go to the taskbar & End task to open file. Still not able to find Where I am lagging. Please give your valuable feedback. Thanks in advance.

VBA code given below

​

Dim xlWb As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = false

Set xlWb = xlApp.Workbooks.open("C:\Users\Public\LUM\DATALOG\Template\Report.xlsm")

newxlName = "C:\Users\Public\LUM\DATALOG\Report\Report_" & "_GENERATED_ON_" & Format(Now(), "dd_mm_yyyy")& "_" & Format(TimeValue(NOW()),"hh_nn_ss") & ".xlsm"

xlWb.saveas (newxlName)

xlApp.Workbooks.Saved = True
xlApp.Workbooks.Close

xlApp.quit
Set xlApp = Nothing

 

[Domenic]

Here are a few things that might help...

1) If you're running the code within Excel, there's no need to create a new instance of the application. So you can simply have the following...

Set xlWb = Application.Workbooks.Open("C:\Users\Public\LUM\DATALOG\Template\Report.xlsm")

2) When saving your workbook, try specifying the file format...

xlWb.SaveAs Filename:=newxlName, FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled

3) The Saved property is the property of the Workbook object, not the Workbooks object. So it should be xlWb.Saved = True. However, since you've saved the workbook in the previous line of code, the Saved property for the workbook is already set to True. So there's no need for this line of code.

4) The Close property is also the property of the Workbook object, not the Workbooks object. So it should be xlWb.Close.

In any case, if you're running the code within Excel, here's another way...

vba.FileCopy "C:\Users\Public\LUM\DATALOG\Template\Report.xlsm", "C:\Users\Public\LUM\DATALOG\Report\Report_" & "_GENERATED_ON_" & Format(Now(), "dd_mm_yyyy")& "_" & Format(TimeValue(NOW()),"hh_nn_ss") & ".xlsm"

Hope this helps!

 

[Jaafar Tribak]

Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing