none
Trying to save and close on exit RRS feed

  • Question

  • Hi,

    I am trying to edit  a script i found online that saves excel files that a user has left open and then closes the excel application. here is what i have however it is not working, something isnt liked in the loop. I have tested this with echo:

    Dim objExcel
    Dim Workbooks  
    Err.Clear
    On Error Resume Next 

    Set objExcel = GetObject(, "Excel.Application") 
        If Err.Number <> 0 Then ExcelWasNotRunning = True
            Err.Clear    ' Clear Err object in case error occurred.

        If ExcelWasNotRunning = True Then 
    wscript.echo "no excel running"
            objExcel.Application.Quit

        Else 

           ' Open your document and ensure its visible and activate after openning 

           objExcel.Application.Visible = True 
           objExcel.Activate


            Set oWS = WScript.CreateObject("WScript.Shell")

        'Get the %userprofile% in a variable, or else it won't be recognized
          ' userProfile = oWS.ExpandEnvironmentStrings( "%userprofile%" )

        Dim objShell
        Dim userName
           Dim FSO
           Dim Folder

          Set FSO = CreateObject("Scripting.FileSystemObject")

            Set objShell= CreateObject("WScript.Network")
            userName = objShell.UserName

            If NOT FSO.FolderExists("c:\users" & userName & "\Desktop\Docs-You-Left-Open") Then

                FSO.CreateFolder("c:\users\" & userName & "\Desktop\Docs-You-Left-Open")
    wscript.sleep 1000 
            End If

            Do while objExcel.Workbooks.Count <> 0
                For Each Workbooks in objExcel.Workbooks 
                    Workbooks.SaveAs("c:\users\" & userName & "\Desktop\Docs-You-Left-Open\" & Workbooks.Name & Timer() & ".xlsx") 
                    Workbooks.Close
    wscript.sleep 100 
            Next 

        Loop
            
        End If 




        Set objExcel = Nothing 
    objExcel.Quit
    objExcel.Application.Quit


    Set KillShell = WScript.CreateObject("WScript.Shell")
    KillResult = KillShell.run ("cscript.exe Taskkill /IM excel.exe /F", 0, True)

    If KillShell <> 0 then 
    Wscript.Echo "couldnt kill excel" 
    End if

    wscript.sleep 1000 
    KillShell.run "cscript.exe Taskkill /IM excel.exe /F", 0, True



    • Edited by Vishay Krishan Wednesday, January 11, 2017 11:52 AM change in code
    Wednesday, January 11, 2017 10:35 AM

Answers

  • Sorry but this is not a free script repair forum. Perhaps you can ask the author to help you.

    I suggest removing the "On Error Resume Next" and fixing the reported errors.


    \_(ツ)_/

    • Marked as answer by Vishay Krishan Wednesday, January 11, 2017 12:26 PM
    Wednesday, January 11, 2017 11:50 AM

All replies

  • Sorry but this is not a free script repair forum. Perhaps you can ask the author to help you.

    I suggest removing the "On Error Resume Next" and fixing the reported errors.


    \_(ツ)_/

    • Marked as answer by Vishay Krishan Wednesday, January 11, 2017 12:26 PM
    Wednesday, January 11, 2017 11:50 AM
  • From what I can see this code never worked as it is full of errors.


    \_(ツ)_/

    Wednesday, January 11, 2017 11:53 AM
  • ok, thanks
    Wednesday, January 11, 2017 12:25 PM
  • Not the most elegant of answers but read up some guides on get objects and came up with this solution instead:

    OPTION EXPLICIT
    DIM strComputer,strProcess, objShell, FSO, userName, objExcel, objWorksheet, objWorkbook, Workbooks, oShell, RunTaskKill, objExcel1, objWorkbook1
    SET objShell = CreateObject("Wscript.Shell")
    
    strComputer = objShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
    
    strProcess = "excel.exe"
    
    
    
    
    ' Function to check if a process is running
    FUNCTION isProcessRunning(BYVAL strComputer,BYVAL strProcessName)
    
        DIM objWMIService, strWMIQuery
    
        strWMIQuery = "Select * from Win32_Process where name like '" & strProcessName & "'"
    
        SET objWMIService = GETOBJECT("winmgmts:" _
            & "{impersonationLevel=impersonate}!\\" _ 
                & strComputer & "\root\cimv2") 
    
    
        IF objWMIService.ExecQuery(strWMIQuery).Count > 0 THEN
            isProcessRunning = TRUE
        ELSE
            isProcessRunning = FALSE
        END IF
    END FUNCTION
    
    
    IF isProcessRunning(strComputer,strProcess) THEN
    Set objShell= CreateObject("WScript.Network")
    userName = objShell.UserName
        Set FSO = CreateObject("Scripting.FileSystemObject")
    
            IF FSO.FolderExists("c:\users\" & userName & "\Desktop\Docs-You-Left-Open") Then
                    wscript.echo "folder already exists"
                    wscript.sleep 500
                ELSE
                FSO.CreateFolder("c:\users\" & userName & "\Desktop\Docs-You-Left-Open")
                wscript.sleep 500
                END IF
                wscript.sleep 1000
    
    Set objExcel = GetObject(, "Excel.Application")
    Set objExcel1 = CreateObject("Excel.Application")
    
    objExcel.Application.Visible = True  
    objExcel1.Application.Visible = True  
    objExcel.AutoRecover.Enabled = False
    objExcel1.AutoRecover.Enabled = False
                           Set  objWorkbook = objExcel.Workbooks
      Set  objWorkbook1 = objExcel.Workbooks
      Set objExcel1 = objExcel
      Set  objWorkbook1 = objWorkbook
                            Do while objExcel1.Workbooks.Count <> 0
                            For Each Workbooks in objExcel1.Workbooks 
                            Workbooks.SaveAs("c:\users\" & userName & "\Desktop\Docs-You-Left-Open\" & Workbooks.Name & Timer() & ".xlsx") 
                            Workbooks.Close
                            wscript.sleep 100 
                            Next 
    
                            Loop
    
    
                            wscript.sleep 1000 
    
    
                                Set oShell = CreateObject ("WScript.Shell")
    
                                 oShell.run "taskkill /f /im excel.exe",0,true
                                    Set oShell = Nothing
                                    wscript.quit
            ELSE
        wscript.echo strProcess & " is NOT running on computer '" & strComputer & "'"
    END IF
    
    wscript.quit


    Wednesday, January 11, 2017 2:10 PM