I am trying to create a backup copy of a current spreadsheet, and I want to
call it a backup copy and place the month in the title of the file.
Can anyone help me achieve this.
PR
I am trying to create a backup copy of a current spreadsheet, and I want to
call it a backup copy and place the month in the title of the file.
Can anyone help me achieve this.
PR
With ActiveWorkbook
.SavecopyAs Left(.Fullname,len(.FullName)-4) & "_backup_" & _
format(date,"mmm") & ".xls"
End With
--
Regards,
Tom Ogilvy
"PR" <[email protected]> wrote in message
news:[email protected]...
> I am trying to create a backup copy of a current spreadsheet, and I want
to
> call it a backup copy and place the month in the title of the file.
>
> Can anyone help me achieve this.
>
> PR
>
>
Tom,
thanks for this, how do I put into this part of the code:
ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=True
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> With ActiveWorkbook
> .SavecopyAs Left(.Fullname,len(.FullName)-4) & "_backup_" & _
> format(date,"mmm") & ".xls"
> End With
>
> --
> Regards,
> Tom Ogilvy
>
>
> "PR" <[email protected]> wrote in message
> news:[email protected]...
>> I am trying to create a backup copy of a current spreadsheet, and I want
> to
>> call it a backup copy and place the month in the title of the file.
>>
>> Can anyone help me achieve this.
>>
>> PR
>>
>>
>
>
change this:
ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=True
to
'Silently overwrite any existing file with this name
Application.DisplayAlerts = False
With ActiveWorkbook
.SavecopyAs "D:\Last Months.xls"
End With
Application.DisplayAlerts = True
--
Regards,
Tom Ogilvy
"PR" <[email protected]> wrote in message
news:[email protected]...
> Tom,
> thanks for this, how do I put into this part of the code:
>
> ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls", FileFormat:=
_
> xlExcel9795, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:= _
> False, CreateBackup:=True
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > With ActiveWorkbook
> > .SavecopyAs Left(.Fullname,len(.FullName)-4) & "_backup_" & _
> > format(date,"mmm") & ".xls"
> > End With
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "PR" <[email protected]> wrote in message
> > news:[email protected]...
> >> I am trying to create a backup copy of a current spreadsheet, and I
want
> > to
> >> call it a backup copy and place the month in the title of the file.
> >>
> >> Can anyone help me achieve this.
> >>
> >> PR
> >>
> >>
> >
> >
>
>
Tom,
I have just realised that I cannot save it just by the month, so I wish to
save the date and time, I have tried to use this:
.SaveCopyAs Left(.FullName, Len(.FullName) - 4) & "_backup_" &
Format(Now, "dd Mmm yy h:mm") & ".xls"
but I get an error, can you please tell me why.
Paul
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> change this:
>
> ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls", FileFormat:= _
> xlExcel9795, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:= _
> False, CreateBackup:=True
>
>
> to
>
> 'Silently overwrite any existing file with this name
> Application.DisplayAlerts = False
> With ActiveWorkbook
> .SavecopyAs "D:\Last Months.xls"
> End With
> Application.DisplayAlerts = True
>
> --
> Regards,
> Tom Ogilvy
>
>
> "PR" <[email protected]> wrote in message
> news:[email protected]...
>> Tom,
>> thanks for this, how do I put into this part of the code:
>>
>> ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls",
>> FileFormat:=
> _
>> xlExcel9795, Password:="", WriteResPassword:="",
>> ReadOnlyRecommended:= _
>> False, CreateBackup:=True
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > With ActiveWorkbook
>> > .SavecopyAs Left(.Fullname,len(.FullName)-4) & "_backup_" & _
>> > format(date,"mmm") & ".xls"
>> > End With
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "PR" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> I am trying to create a backup copy of a current spreadsheet, and I
> want
>> > to
>> >> call it a backup copy and place the month in the title of the file.
>> >>
>> >> Can anyone help me achieve this.
>> >>
>> >> PR
>> >>
>> >>
>> >
>> >
>>
>>
>
>
I tested it an the Colon is not allowed. If I change it to an underscore,
it works fine:
Sub ABC()
With ActiveWorkbook
sStr = Left(.FullName, Len(.FullName) - 4) & "_backup_" & _
Format(Now, "dd Mmm yy h_mm") & ".xls"
Debug.Print sStr
.SaveCopyAs sStr
End With
End Sub
C:\Data\AA_Sample_backup_08 Nov 05 17_05.xls
--
Regards,
Tom Ogilvy
"PR" <[email protected]> wrote in message
news:[email protected]...
> Tom,
> I have just realised that I cannot save it just by the month, so I wish to
> save the date and time, I have tried to use this:
> .SaveCopyAs Left(.FullName, Len(.FullName) - 4) & "_backup_" &
> Format(Now, "dd Mmm yy h:mm") & ".xls"
> but I get an error, can you please tell me why.
>
> Paul
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > change this:
> >
> > ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls", FileFormat:=
_
> > xlExcel9795, Password:="", WriteResPassword:="",
> > ReadOnlyRecommended:= _
> > False, CreateBackup:=True
> >
> >
> > to
> >
> > 'Silently overwrite any existing file with this name
> > Application.DisplayAlerts = False
> > With ActiveWorkbook
> > .SavecopyAs "D:\Last Months.xls"
> > End With
> > Application.DisplayAlerts = True
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "PR" <[email protected]> wrote in message
> > news:[email protected]...
> >> Tom,
> >> thanks for this, how do I put into this part of the code:
> >>
> >> ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls",
> >> FileFormat:=
> > _
> >> xlExcel9795, Password:="", WriteResPassword:="",
> >> ReadOnlyRecommended:= _
> >> False, CreateBackup:=True
> >>
> >> "Tom Ogilvy" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > With ActiveWorkbook
> >> > .SavecopyAs Left(.Fullname,len(.FullName)-4) & "_backup_" & _
> >> > format(date,"mmm") & ".xls"
> >> > End With
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> > "PR" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> I am trying to create a backup copy of a current spreadsheet, and I
> > want
> >> > to
> >> >> call it a backup copy and place the month in the title of the file.
> >> >>
> >> >> Can anyone help me achieve this.
> >> >>
> >> >> PR
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>
This is close to my problem: Ask user to backup want to savecopyas. need to
return to active sheet to update for futheruse. When useing savefile as I
lose active sheet. Need to get location for and file name from user.Trying
to use input box for location and file name could be dialog box for save
copyas? useing xl2000
any Help greatly appreciated.
Thanks Curt
"Tom Ogilvy" wrote:
> With ActiveWorkbook
> .SavecopyAs Left(.Fullname,len(.FullName)-4) & "_backup_" & _
> format(date,"mmm") & ".xls"
> End With
>
> --
> Regards,
> Tom Ogilvy
>
>
> "PR" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to create a backup copy of a current spreadsheet, and I want
> to
> > call it a backup copy and place the month in the title of the file.
> >
> > Can anyone help me achieve this.
> >
> > PR
> >
> >
>
>
>
All I need to is twek it a bit to get it
Thanks a bunch
Curt
"Curt" wrote:
> This is close to my problem: Ask user to backup want to savecopyas. need to
> return to active sheet to update for futheruse. When useing savefile as I
> lose active sheet. Need to get location for and file name from user.Trying
> to use input box for location and file name could be dialog box for save
> copyas? useing xl2000
> any Help greatly appreciated.
> Thanks Curt
>
> "Tom Ogilvy" wrote:
>
> > With ActiveWorkbook
> > .SavecopyAs Left(.Fullname,len(.FullName)-4) & "_backup_" & _
> > format(date,"mmm") & ".xls"
> > End With
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "PR" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am trying to create a backup copy of a current spreadsheet, and I want
> > to
> > > call it a backup copy and place the month in the title of the file.
> > >
> > > Can anyone help me achieve this.
> > >
> > > PR
> > >
> > >
> >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks