VBS Script to Save Open Excel Workbook

[rcolving]

I have a customer that has an Excel Workbook that they never close. His people do not click save. So if the PC happens to update and reboot they lose all data input. I am trying to create a VBS script that I can schedule with task manager to save the workbook at days end. I have this script but get errors: Line 6, Object required: 'objExcel.ActiveWorkbook' Code: 800A01A8. Can anyone help with this?

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks

objExcel.Application.Visible = True

objExcel.ActiveWorkbook.Save ("C:\user\randy\documents\VBS Save Test.xlsx")

objExcel.Application.Quit
WScript.Quit

 

[John_w]

Try this:

Option Explicit

Const xlOpenXMLWorkbook = 51

Dim Excel

Set Excel = Nothing
On Error Resume Next
Set Excel = GetObject(, "Excel.Application")
On Error GoTo 0
If Not Excel Is Nothing Then
    If Not Excel.ActiveWorkbook Is Nothing Then
        If Excel.ActiveWorkbook.Path = "" Then
            'Workbook never saved before
            Excel.DisplayAlerts = False 'in case file already exists
            Excel.ActiveWorkbook.SaveAs "C:\user\randy\documents\VBS Save Test.xlsx", xlOpenXMLWorkbook
            Excel.DisplayAlerts = True
        Else
            'Save existing file
            Excel.ActiveWorkbook.Save
        End If
        Excel.ActiveWorkbook.Close False
    End If
    Excel.Quit
    Set Excel = Nothing
    WScript.Sleep 2000  'allow time for Excel.exe process to terminate
End If

 

[rcolving]

Try this:

Option Explicit

Const xlOpenXMLWorkbook = 51

Dim Excel

Set Excel = Nothing
On Error Resume Next
Set Excel = GetObject(, "Excel.Application")
On Error GoTo 0
If Not Excel Is Nothing Then
    If Not Excel.ActiveWorkbook Is Nothing Then
        If Excel.ActiveWorkbook.Path = "" Then
            'Workbook never saved before
            Excel.DisplayAlerts = False 'in case file already exists
            Excel.ActiveWorkbook.SaveAs "C:\user\randy\documents\VBS Save Test.xlsx", xlOpenXMLWorkbook
            Excel.DisplayAlerts = True
        Else
            'Save existing file
            Excel.ActiveWorkbook.Save
        End If
        Excel.ActiveWorkbook.Close False
    End If
    Excel.Quit
    Set Excel = Nothing
    WScript.Sleep 2000  'allow time for Excel.exe process to terminate
End If

I tried this and got the following error.
Script: C:\Users\Randy\Docuents\VBSSaveFile.vbs
Line: 12
Char: 5
Error: Call was rejected by callee.
Code: 80010001
Source: (null)

 

[John_w]

Which is line 12 then?

Make sure this path is correct (it's usually Users, not User):

Excel.ActiveWorkbook.SaveAs "C:\user\randy\documents\VBS Save Test.xlsx", xlOpenXMLWorkbook