Automatically save file as value in cell A1?

[samilynn]

Is it possible to automatically save a file (in a specified folder), as the value in cell A1?

I am trying to prevent the situation when a user enters important information in a file, but then forgets to save it.

So what if I automatically saved the file as the value in cell A1 (which would typically be a name) as soon as he types something in that cell, and then I would force an autosave every 1 minute or so?

Thanks for your help!!

Samantha

 

[DanteAmor]

Another option is to set the save every 1 minute:

 

[samilynn]

Another option is to set the save every 1 minute:


View attachment 115527

Yes, but what about the initial "Save"? This user will be working on a lot of files during this session, and I'd like to force the saving of the files to the value in a cell, if that makes sense.

 

[DanteAmor]

Try the following:

1. In the events of the sheet where you have the cell with the file name, put the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
    Call SavingFile
    
  End If
End Sub

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up


2. In a module, put the following code.

• Set "C:\work\files\" for your specific folder.
• Set "Sheet1" by the name of your sheet
• Set "00:01:00" by duration, for example "00:01:00" for one minute, "00:05:00" for every 5 minutes, etc.
• If you want autosave to end, simply delete cell A1.

Sub SavingFile()
  Application.DisplayAlerts = False
  If Sheets("Sheet1").Range("A1").Value = "" Then
    Exit Sub
  End If
  ThisWorkbook.SaveCopyAs "C:\trabajo\files\" & Sheets("Sheet1").Range("A1").Value & ".xlsm"
  Application.OnTime Now + TimeValue("00:01:00"), "SavingFile", , True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[samilynn]

Try the following:

1. In the events of the sheet where you have the cell with the file name, put the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
   
    Call SavingFile
   
  End If
End Sub

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up


2. In a module, put the following code.

• Set "C:\work\files\" for your specific folder.
• Set "Sheet1" by the name of your sheet
• Set "00:01:00" by duration, for example "00:01:00" for one minute, "00:05:00" for every 5 minutes, etc.
• If you want autosave to end, simply delete cell A1.

Sub SavingFile()
  Application.DisplayAlerts = False
  If Sheets("Sheet1").Range("A1").Value = "" Then
    Exit Sub
  End If
  ThisWorkbook.SaveCopyAs "C:\trabajo\files\" & Sheets("Sheet1").Range("A1").Value & ".xlsm"
  Application.OnTime Now + TimeValue("00:01:00"), "SavingFile", , True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Thank you so much, that is amazing!!!! Thank you thank you!

One more (last) question: can you think of a way that when the user is done entering data on the file, I can "force" a final "save" on it? Maybe with a button or something....so your code creates the files, with the filename that is in cell A1, which is AMAZING, but as the user keeps entering, it looks like's he's still working on the original file. I'd like to make sure he saves the file that's in the new folder when he is done entering. The only way I can think to do that is to change the timer in the code to 00:00:01, but wouldn't that cause performance issues, if it keeps saving every one second?

 

[DanteAmor]

I'd like to make sure he saves the file that's in the new folder when he is done entering.

Replace this line:

ThisWorkbook.SaveCopyAs "C:\trabajo\files\" & Sheets("Sheet1").Range("A1").Value & ".xlsm"

For this:

ThisWorkbook.SaveAs "C:\trabajo\files\" & Sheets("Sheet1").Range("A1").Value & ".xlsm"