Spliting variables

Posted by John on February 01, 2002 5:42 AM

I am trying to create a backup file.

I use Application.GetSaveAsFilename to get the filename
I then saveas, and open the original. The problem occurs when I try to close down the backup file. I have the full filepath name but all I need for the close function is thelast bit e.g. book1.xls.

How can I get this from the full path variable?

Code is below if anyone wants to see it...

Cheers

John

Sub backup()

Application.ScreenUpdating = False

On Error GoTo ErrorHandler ' Enable error-handling routine.

'Save the current workbook as the BPA Backup
ActiveWorkbook.Save

fname = Application.GetSaveAsFilename

ActiveWorkbook.SaveAs Filename:=fname, _
FileFormat:=xlExcel7, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

MsgBox (fname)

Workbooks.Open(Filename:="Business Plan Reporting Tool TestTwo.xls").RunAutoMacros Which:=xlAutoOpen

Workbooks(fname).Close SaveChanges:=False

Application.ScreenUpdating = True

Exit Sub ' Exit to avoid handler.

ErrorHandler: ' Error-handling routine.
MsgBox ("An error ocurred during the back up process. Please Try Again.")
Exit Sub
End Sub

Posted by JohnG on February 01, 2002 5:58 AM

How about changing
Workbooks(fname).Close SaveChanges:=False
to
Workbooks(activeworkbook.name).Close SaveChanges:=False
I am assuming the one just saved is still the active workbook MsgBox (fname)

Posted by John on February 01, 2002 6:12 AM

John, Thanks but that still doesn't work since I have had to open the original one (which then becomes the activeworkbook!). If I close that workbook, I am left with no workbooks and everything stops!!

Any ideas?

John

MsgBox (fname)

Posted by JohnG on February 01, 2002 6:21 AM

Try this one then
Sub IsEmptyChk()
Dim Floop As Integer
Dim FWBName As String
Dim Fname As String

Fname = Application.GetSaveAsFilename

For Floop = Len(Fname) To 1 Step -1
If Mid(Fname, Floop, 1) = "\" Then Exit For
FWBName = Mid(Fname, Floop, 1) & FWBName
Next Floop
FWBName = FWBName & "xls"
MsgBox FWBName
End Sub MsgBox (fname)

Posted by JL on February 01, 2002 6:23 AM

Thanks John that works fine.

JL Try this one then MsgBox (fname)