I need to save a copy of a file each day the macro runs to a network
location. What is an easy way to code it to append the date to the front of
the file name so each time the file is rerun it won't write over the previous
file?
I need to save a copy of a file each day the macro runs to a network
location. What is an easy way to code it to append the date to the front of
the file name so each time the file is rerun it won't write over the previous
file?
With Activeworkbook
.SaveAs Filename:= Format(Date,"yyyy mm dd") & " - " & .Name
End With
--
HTH
RP
(remove nothere from the email address if mailing direct)
"SKelley" <[email protected]> wrote in message
news:[email protected]...
> I need to save a copy of a file each day the macro runs to a network
> location. What is an easy way to code it to append the date to the front
of
> the file name so each time the file is rerun it won't write over the
previous
> file?
This code is perfect so long as you only run it once a day... You probably
want to add in an On Error... something like...
On Error Goto LongFileName
With Activeworkbook
.SaveAs Filename:= Format(Date,"yyyy mm dd") & " - " & .Name
End With
on error goto 0
.... any more code you want here...
Exit Sub
LongFileName:
With Activeworkbook
.SaveAs Filename:= Format(Date,"yyyy mm dd") & " - " &
Format(Time,"hh:mm:ss") & " - " & .Name
End With
resume next
end sub
Or something like that. It appends the time to the date to make a unique
file name. This code is untested but it should work. If I know Bob he
probably has a better way but that is how I would do it...
HTH
"Bob Phillips" wrote:
>
> With Activeworkbook
> .SaveAs Filename:= Format(Date,"yyyy mm dd") & " - " & .Name
> End With
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "SKelley" <[email protected]> wrote in message
> news:[email protected]...
> > I need to save a copy of a file each day the macro runs to a network
> > location. What is an easy way to code it to append the date to the front
> of
> > the file name so each time the file is rerun it won't write over the
> previous
> > file?
>
>
>
Thanks for your help
"Jim Thomlinson" wrote:
> This code is perfect so long as you only run it once a day... You probably
> want to add in an On Error... something like...
>
> On Error Goto LongFileName
> With Activeworkbook
> .SaveAs Filename:= Format(Date,"yyyy mm dd") & " - " & .Name
> End With
> on error goto 0
>
> ... any more code you want here...
>
> Exit Sub
> LongFileName:
> With Activeworkbook
> .SaveAs Filename:= Format(Date,"yyyy mm dd") & " - " &
> Format(Time,"hh:mm:ss") & " - " & .Name
> End With
> resume next
> end sub
>
> Or something like that. It appends the time to the date to make a unique
> file name. This code is untested but it should work. If I know Bob he
> probably has a better way but that is how I would do it...
>
> HTH
>
> "Bob Phillips" wrote:
>
> >
> > With Activeworkbook
> > .SaveAs Filename:= Format(Date,"yyyy mm dd") & " - " & .Name
> > End With
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "SKelley" <[email protected]> wrote in message
> > news:[email protected]...
> > > I need to save a copy of a file each day the macro runs to a network
> > > location. What is an easy way to code it to append the date to the front
> > of
> > > the file name so each time the file is rerun it won't write over the
> > previous
> > > file?
> >
> >
> >
I've got it to append the date to the filename now but I'm having trouble
with it saving the file on the network drives. Any idea on how to get it to
do both functions. Append date to filename and save in a new location. Thanks
"SKelley" wrote:
> I need to save a copy of a file each day the macro runs to a network
> location. What is an easy way to code it to append the date to the front of
> the file name so each time the file is rerun it won't write over the previous
> file?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks