0

We have tried to fix these problem for a couple of days and we posted on another forums but still no answer, may be you guys can help us here.

I am having a problem with excel running after the Userform is closed, I think what I have supposed to end the application but it doesn't, it keeps excel running in the background and I have to end the process with task manager. I created a vba file to copy a file to the temp folder and run a macro which opens excel and the userform. Any help is appreciate it.

This is the vba script that copy and opens the macro:

    Dim FSO 
    Set FSO = CreateObject("Scripting.FileSystemObject") 
    FSO.CopyFile "C:\pdfv6.xlsm", "C:\Windows\Temp\" 
    Set objExcel = CreateObject("Excel.Application") 
    objExcel.Application.Run "'C:\Windows\Temp\pdfv6.xlsm'!module1.macro1" 
    objExcel.DisplayAlerts = False 
    Set objExcel = Nothing
    objExcel.Application.Quit

And this is part of the vba module:

    Sub Macro1() 
    Application.Visible = False 
    UserForm1.Show 
    End Sub
    Set objExcel = Nothing

And this is the Sub that deals with closing the Userform and the application.

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 
If CloseMode = 0 Then 
    Unload Me
    ThisWorkbook.Close savechanges = False 
    Application.Quit 
    End 
  End If 
  End Sub
Community
  • 1
  • 1
Lisa Betancourt
  • 45
  • 7
  • 1
    You are having Excel create a new instance of itself? Why are you doing that? – braX Oct 27 '17 at 17:40
  • Have you ever tried out an Add-On? (XLAM file) It seems like that would be a more logical approach. – braX Oct 27 '17 at 17:46
  • @brax This excel macro will be use by several people in a network enviroment and we were having issues with read only documents etc. We don't want to use an addon, we want to use a shortcut on everybody's computers. – Lisa Betancourt Oct 27 '17 at 18:09
  • 1
    Why is the Set objExcel = Nothing after your End Sub? Might want to try GetObject instead of CreateObject. – mooseman Oct 27 '17 at 18:11
  • What method are you using to close everything ? Is it from the UserForm ? If so, then it will never work right. Where is the macro started from initially ? To me, it looks like you are trying to close excel on itself from a script which has to end before closing, which I'm not sure you can do. Closing from another workbook is simple. – Mitch Oct 27 '17 at 18:13
  • 1
    What @Mitch said. The problem stems from implementing application logic in a form's code-behind, as I explain in the blog post linked above. The form has no business calling `Application.Quit`, `ThisWorkbook.Close`, or `End`ing execution, and `Unload Me` is bug-prone at best. – Mathieu Guindon Oct 27 '17 at 18:18
  • A network environment is the ideal use of an Addon. You have a read-only copy of the XLAM file on the network that everyone uses, and a full version for the developer to update and copy over the read-only file when there are changes, and everyone is then updated. You are going to have lots of problems doing it the way you are doing it. – braX Oct 27 '17 at 18:18

1 Answers1

2

I found the problem. I was using Application.Quit twice Once in the vbs script and then when I was closing the form. I got rid of the one in the vbs script and everything is fine now. thank you for your help.

Lisa Betancourt
  • 45
  • 7
  • Also you can't Set objExcel = Nothing before objExcel.Application.Quit... you'll get an error... – stefan Oct 27 '17 at 18:43
  • Stubbornly holding on to a flawed architecture is going to crumble, one day or another. Take the time to heed the advice you were given, and do things *right*. Code that "works" isn't necessarily *good code*. – Mathieu Guindon Oct 27 '17 at 18:58