VBA Save

[raccoon588]

I have a file that saves everyday at 1am. It is locates on a network drive in a specific folder. I cant figure out why it saves to the drive and saves to the local PCs documents.

Sub sveWrkBk()
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.OnTime TimeValue("01:00:00"), "sveWrkBk"
    Application.DisplayAlerts = True
End Sub

 

[mrshl9898]

It's saving to documents as you aren't declaring a location. You'll need to change Save to SaveAs

You can manually enter the file name and location, or use this method to keep the current location and name.

[TABLE="width: 615"]
<tbody>[TR]
[TD]Dim workbook_Name As Variant[/TD]
[/TR]
[TR]
[TD]workbook_Name = Application.GetSaveAsFilename[/TD]
[/TR]
[TR]
[TD]If workbook_Name <> False Then[/TD]
[/TR]
[TR]
[TD]ActiveWorkbook.SaveAs Filename:=workbook_Name[/TD]
[/TR]
[TR]
[TD]End If[/TD]
[/TR]
</tbody>[/TABLE]

https://powerspreadsheets.com/vba-save-workbook/

 

[raccoon588]

how would this fit into the code i already have?

 

[mrshl9898]

I would try a different method, only as I don't quite follow the logic applied here. (The code supplied also asks for the location first which I failed to realize.) So you want to replace this:

[COLOR=#333333]ActiveWorkbook.Save[/COLOR]

With:

workbook_Name = ActiveWorkbook.FullName
ActiveWorkbook.SaveAs Filename:=workbook_Name

Personally i'd enter this in "ThisWorkbook", so the macro doesn't need to be triggered.

Private Sub Workbook_Open()


Application.OnTime TimeValue("01:00:00"), "sveWrkBk"


End Sub

Then in a module:

Sub sveWrkBk()


Dim workbook_Name As Variant


    Application.DisplayAlerts = False
    workbook_Name = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs Filename:=workbook_Name
    Application.DisplayAlerts = True


End Sub