saving to network drive

[hopr37]

I have a module that saves to a network drive whenever I open the workbook. The problem is the code gives me an error when I try to open the saved file. the code is from a forum post on a different website

Private Sub Workbook_Open()
'Sub SaveToLocations()
ActiveWorkbook.SaveCopyAs "\\svr-files\files\pub_data\HTC Outside Plant\HTC Cable Count\backup" + ActiveWorkbook.Name
ActiveWorkbook.Save
End Sub

It saves it to the backup folder with the name "test.xlsm- That works fine

when I open the backup file I get a debug error. I'm Assuming because it's trying to make a backup in the backup folder with a file I have opened.
is there a way to stop this error?

 

[hotabae]

Could you append "(backup)" to the end of your path?

Make it look like this:

Private Sub Workbook_Open()
'Sub SaveToLocations()
ActiveWorkbook.SaveCopyAs "\\svr-files\files\pub_data\HTC Outside Plant\HTC Cable Count\[COLOR=#ff0000]backup\[/COLOR]" + ActiveWorkbook.Name + "(backup)"
ActiveWorkbook.Save
End Sub

I also just assumed there was a " \ " after backup. Please correct me if I'm wrong.

Giving your backups a slightly different name than your original file should fix that.

 

[hopr37]

yes there should have been a " \ "
and that worked. Thank you.
Now
...
is there a way to backup the file but give it a different name each time it's saved?

 

[hotabae]

yes there should have been a " \ "
and that worked. Thank you.
Now
...
is there a way to backup the file but give it a different name each time it's saved?

I use something like this with mine. (It only creates a new file each day)

ActiveWorkbook.SaveCopyAs "\\svr-files\files\pub_data\HTC OutsidePlant\HTC Cable Count\backup\" & ActiveWorkbook.Name & "(" & DatePart("m",DATE) & "_" & DatePart("d",DATE) & "_" & DatePart("yyyy",Date) & ")"

Edit: FYI - would save as filename = "test.xlsm(4_25_2018)"

 

[hopr37]

would that work each time the workbook was closed?
tried that and I get a debug error

 

[hotabae]

would that work each time the workbook was closed?
tried that and I get a debug error

It looks like I misplaced a space in "HTC Outside Plant". That will cause issues if you copy/pasted.

It will save a new copy as a backup whenever you open the file. That is because you placed it in WorkBook_Open()

I would recommend still saving the "master" version as you make changes (if you are in development) and saving before you exit the "master"


Edit: Misspoke - should read "It will save a new copy the first time it is opened on any given day. Any time after the first, it will overwrite the backup"

 

[hopr37]

Thanks for the help. Really appreciated.
yep. That does work but I was hoping that everytime it closed ( or opened) it would just append something to the end so that it would not overwrite the existing backup. Something like:
test.xlsm(4_25_2018)
test.xlsm(4_25_2018-1)
test.xlsm(4_25_2018-2)

 

[hotabae]

So, there is definitely a way to do that,

Dim currentPath As String
Dim duplicateCount As Long
duplicateCount = 0
Dim saveSuccess As Boolean
saveSuccess = False

currentPath = "\\svr-files\files\pub_data\HTC Outside Plant\HTC Cable Count\backup\" & ActiveWorkBook.Name & "(" & DatePart("m",DATE) & "_" & DatePart("d",DATE) & "_" & DatePart("yyyy",Date) & ")"

Do while saveSuccess = False

If Dir(currentPath) <> "" Then

    ActiveWorkbook.SaveCopyAs currentPath
    saveSuccess = True

Else

    duplicateCount = duplicateCount + 1
    currentPath = currentPath & "-" & duplicateCount

End If
Loop

 

[hopr37]

Thank you for the reply. I will have to check this a little later and tell you how it goes.
It's very much appreciated

 

[hotabae]

Ooh, so I definitely have the If Loop Backwards in my earlier post. The code should read:

[COLOR=#222222][FONT=Verdana][COLOR=#333333][FONT=monospace]If Dir(currentPath) <> "" Then

[LEFT][COLOR=#333333][FONT=monospace]    duplicateCount = duplicateCount + 1[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]    currentPath = currentPath & "-" & duplicateCount[/FONT][/COLOR][/LEFT]

Else

[LEFT][COLOR=#333333][FONT=monospace]  ActiveWorkbook.SaveCopyAs currentPath[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]    saveSuccess = True[/FONT][/COLOR][/LEFT]

End If[/FONT][/COLOR][/FONT][/COLOR]

There is still the issue of the extension getting messed up though. All the stuff getting added to the end is actually getting added to the extension (Oops!)


EDIT: This might do it (though I haven't tested)

[COLOR=#222222][FONT=Verdana]Dim currentPath As String
Dim duplicateCount As Long
duplicateCount = 0
Dim saveSuccess As Boolean
saveSuccess = False

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]currentPath = "\\svr-files\files\pub_data\HTC Outside Plant\HTC Cable Count\backup\"  & "(" & DatePart("m",DATE) & "_" & DatePart("d",DATE) & "_" & DatePart("yyyy",Date) & ")" & ActiveWorkBook.Name

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Do while saveSuccess = False

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If Dir(currentPath) <> "" Then

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    duplicateCount = duplicateCount + 1
    currentPath = duplicateCount & "-" & currentPath

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Else

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    ActiveWorkbook.SaveCopyAs currentPath
    saveSuccess = True

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End If

Loop[/FONT][/COLOR]