Autosave Macro not working in certain workbooks

[njohnn]

Hi, hoping someone can help me

I am using the below code for an 'Autosave' button which saves a copy of a spreadsheet and adds a date stamp. This is used on a checklist form which needs to be completed by maintenance engineers. Once they have completed the form, they click the autosave button and a copy of the completed form is saved showing that their maintenance checks have been completed on a given date.

I am trying to add this button on to other forms they use as they have all said it has made life a bit easier simply being able to autosave these forms and we have an electronic library of all the maintenance forms saved. The trouble I am having is that the button can be added to some forms successfully, but other forms will not run the macro. I have a feeling it might be something to do with the length of the filename but am not 100% sure on this. The filename is just under 200 characters long.

The code being used is:

Sub AUTOSAVE()
Dim nom As String
nom = Day(Date) & "-" & Month(Date) & "-" & Year(Date) & "_" & ActiveWorkbook.Name
ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & nom
rep = MsgBox("You database has been saved : " & Name, vbYes + vbInformation, "Copy of spreadsheet")
End Sub


If I try to run this macro on certain worksheets I get runtime error 1004. if I hit 'debug' it shows this line of the code in yellow:

ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & nom

Any ideas on how I can make this work on all forms please.

Many thanks.

[Fettertiger]

Hi,

I assume you are running the macros on unsaved files. activeworkbook.Path will remain empty before saving which will cause the error.

One solution would be to let the macro do an ordinary save in case "activeworkbook.Path" is empty. Alternatively you could use a default path if that happens.

Here is the solution with a save:

Please Login or Register  to view this content.

Theo

[njohnn]

Hi Theo,

Thanks for your reply. Unfortunately I have tried the solution but I get the same error message.

All the spreadsheets I am trying to install this 'Autosave' button on are saved on the server. The autosave button just saves a date stamped copy of them within the same folder on the server.

I have managed to get the button to work on the problem spreadsheets if I save them locally to my own pc but they then stop working again if I put them back in their original location on the server, this is why I am thinking the problem might be related to the file path.

Any other ideas which might help?

I've attached a copy of one of the spreadsheets so you can see an example.

Thanks again.

Nick