Don't want backup copy in XLSTART

[Andyjim]

I have the following macro assigned to a button to allow the user to make a
backup copy in the current folder (example: Backup_Username.xls). Works OK,
but sometimes (I haven't figured out under what circumstances) a copy turns
up in XLSTART as well. And then of course that copy opens next time the user
launches his file, which is a nuisance. Don't know how to prevent that
happening.

Sub Backupbutton()
'Creates backup copy of user's file, in same folder

Dim usrfile As Workbook
Dim backname As String
Set usrfile = ActiveWorkbook
path = ActiveWorkbook.path
backname = path & "\" & "Backup_" & usrfile.Name
usrfile.SaveCopyAs filename:=backname

End Sub

 

[Dave Peterson]

Maybe you could check to see if XLStart is part of the Path.

Dim myPath As String ' I wouldn't use a variable named Path.
myPath = ActiveWorkbook.Path
If InStr(1, myPath, "\xlstart", vbTextCompare) = 0 Then
'do the save
Else
'skip it
End If

 

[Andyjim]

Thanks Dave,
I changed the code thus:

Dim usrfile As Workbook
Dim backname As String
Set usrfile = ActiveWorkbook
Dim p As String
p = ActiveWorkbook.path
backname = p & "\" & "Backup_" & usrfile.Name

If InStr(1, p, "xlstart", vbTextCompare) = 0 Then
usrfile.SaveCopyAs filename:=backname
Else
'do nothing
End If

It hasn't put a copy in XLSTART since, but then neither has another file
using the previous code. I still have no clue under what circumstances it
does put a copy in XLSTART (it's been inconsistent best I could tell), so
unfortunately I don't know whether it's really fixed or not. Best I can do is
go with it until proven wrong. If you have ideas why a copy should ever show
up in XLSTART, I'd at least feel better...

 

[Dave Peterson]

It looked like you had a file by the name of UserName.xls in that XLStart folder
(from the original post, anyway).

 

[Andyjim]

No, just meant that as an example of the format of the resulting filename. I
will not know what name the user gives the file.

 

[Dave Peterson]

If you know the name of the backup file that you found in XLStart, then it
should be easy to check to see if the original file is in that same XLStart
folder.

If you're saying that there wasn't a file in the XLStart folder, but there was
still a backup_xxxx.xls created, then I don't have a guess.

 

[Andyjim]

OK, I see what you were driving at. No, normally the only file in XLSTART is
PERSONAL.XLS. But these backup copies have just been showing up there
sometimes. Just wish I knew why. Maybe it's an OS thing. So far, though, it
hasn't happened again since inserting your code suggestion. Maybe that fixed
it.
Hmm, maybe it's BECAUSE personal.xls is there that it sometimes puts them
there. But why not consistently then? Heck, I don't know. I assume
personal.xls has to be there. Don't know where else if not there.

 

[Dave Peterson]

And you're positive that the backup files that are created are based on files
that are not located in the XLStart folder???

I don't see anything in your code that would cause that.

But I would change
p = ActiveWorkbook.path
to
p = usrfil.path

But that shouldn't make any difference.

 

[Andyjim]

The files the backups are based on are definitely not in XLXTART. Not even in
the C: drive. The ONLY file present in XLSTART (except when these backup
files show up) is personal.xls. No other file whatsoever is there. Yes, I'm
quite sure it's not my code that is causing them to be put there. I think
it's something in Excel, or in the OS (Windows 2000). But I thought by
generating an exact path for the backups it would ensure that's where, and
only where, they would go. But they still sometimes have showed up in
XLSTART. Beats me.

 

[Dave Peterson]

Maybe toss some msgbox lines into your code that display the name and path of
the usrfil. It may help you the next time it happens.