Link to home
Start Free TrialLog in
Avatar of SeeDk
SeeDk

asked on

Create macro that is run through Task Scheduler and saves all open Excel spreadsheets?

One user is constantly experiencing Excel crashes. Have even switched PCs for the user but problem persists. Excel Autosave also does not work as intended.
So I am wondering how to set up automatic saving of an excel file through tasks scheduler.
Ideally, I would configure the task to run every minute. Googling online I saw the recommended way to do this is with a VB macro but did not find one which would work to save ALL open spreadsheets.
Has anyone does this sort of thing before?
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

A better solution - find out why Excel continues to crash, and fix that. If it's following the user from PC to PC, then it would have to be either (a) the workbook (assuming they're opening the same workbook on different PCs) or (b) their user login. Without seeing the workbook it'd be hard to give suggestions, but to test the user profile you could create a new one, and have the user log in with that, and see if the problem persists.
Avatar of SeeDk
SeeDk

ASKER

Yes, that would be ideal but I don't know what the fix is - I've tried all the usual fixes and have done a lot of searching on it. It is probably user error.

a) It happens with different workbooks.
b) Can you go into more detail on why this would matter? He just got a new PC which also means a new local user profile.
The user profile controls a LOT of different things, and it "follows" the user to different machines on the network (assuming they're logging into that second machine with the same user credentials, of course). It is not at all uncommon to see corrupt/faulty user profiles cause troubles with Office documents, since the Office program is very much "user configured".

If it happens with different workbooks, however, the culprit would seem to be in Excel. Have you tried uninstalling/reinstalling Office on the machine?
Avatar of SeeDk

ASKER

I did Office repairs at first and then did  reinstall none worked.
Also, he has started using a different PC which was restored from a clean system image. So that should rule out hardware issues and most OS issues. It may be he is unknowingly triggering some bug in Excel.
So that should rule out hardware issues and most OS issues.
Most definitely NOT. A restored image can be just as troublesome as a standard install.

Is there anything unusual about the workbooks? Are they very large, lots of complex functions, etc etc?

While there are certainly bugs in software, Excel is a VERY mature product, and unless you're doing something very strange with Excel I'd be hard pressed to think "bug". More likely the install of Excel/Office, or Windows, is at fault.

What OS are they using?
Avatar of SeeDk

ASKER

They are not large (recent issue is 4mb) but they are complex. Lots of formulas, sheets with large quantities columns/rows/data. It is the norm here for people to work with these kinds of files.
 Windows 7 64 bit. Office is 32 bit.
Due to a custom plug-in - we can't upgrade to Office 64 bit. This plug-in is used by nearly the whole firm with no issue.
I don't think upgrading to 64-bit is the answer anyway.

If other users open the same workbook, do they experience the same issue?
Here is some code that you should put in one workbook only to perform the autosaves on every open and previously saved workbook. If you store that workbook in the user's XLSTART folder, it will open whenever Excel launches.
'Put all this code in the ThisWorkbook code pane
Private Sub Workbook_Open()
dNextRun = Now + TimeSerial(0, 1, 0)
Application.OnTime dNextRun, "SaveMyButt"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dNextRun, "SaveMyButt", Schedule:=False
End Sub

Open in new window

'Put this code in a regular module sheet
Public dNextRun As Double

Sub SaveMyButt()
Dim wb As Workbook
For Each wb In Workbooks
    If Right(LCase(wb.Save, 5)) Like ".xls?" Then wb.Save
Next
dNextRun = Now + TimeSerial(0, 1, 0)
Application.OnTime dNextRun, "SaveMyButt"
End Sub

Open in new window

Avatar of SeeDk

ASKER

@Scott - no its only him. There are other people working on these files and don't have issues. It's also happened across multiple files.

@byundt Thanks, loving the name in the vbcode! Will test this out.
Avatar of SeeDk

ASKER

When that VB code run it shows this message:

Compile error:
Expected Function or Variable

and it highlights this part of the module code:
-> Sub SaveMyButt()
My goof!
'Put all this code in the ThisWorkbook code pane
Private Sub Workbook_Open()
dNextRun = Now + TimeSerial(0, 1, 0)
Application.OnTime dNextRun, "SaveMyButt"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dNextRun, "SaveMyButt", Schedule:=False
On Error GoTo 0
End Sub

Open in new window

'Put this code in a regular module sheet
'Put this code in a regular module sheet
Public dNextRun As Double

Sub SaveMyButt()
Dim wb As Workbook
Application.ScreenUpdating = False
For Each wb In Workbooks
    If Right(LCase(wb.Name), 5) Like ".xls?" Then wb.Save
Next
dNextRun = Now + TimeSerial(0, 1, 0)
Application.OnTime dNextRun, "SaveMyButt"
End Sub

Open in new window

SaveAllFilesEveryMinuteQ29023441.xlsm
Avatar of SeeDk

ASKER

Awesome this is working!
Is there way to exclude certain spreadsheets from being saved?
This is also saving Personal.xls and another macro which we've set to automatically open with every spreadsheet.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SeeDk

ASKER

Thanks, this works beautifully!