From: Farhan on
Hi all,

I am new to macro and using macro recoder to create macro. On one file which
I recieve every week from material department. I need to reformat and create
pivot table. I use macro recorder and everything is working good. However I
need that when my recorded macro end it create a file with the same name with
the date and time it was create. I need every week a new file and I dont want
to replace the one. Please let me know how I can create a new file with the
date and time everyweek when I run my macro. Thanks
--
Farhan
From: Per Jessen on
Hi Farhan

This should do it, just insert your path in the MyPath variable:

Dim MyPath As String
Dim fName As String
MyPath = "C:\Temp\" 'Change to suit
fName = "MyFile_" & Format(Now, "mmddyy_hh:mm")
ActiveWorkbook.SaveAs Filename:=MyPath & fName

Best regards,
Per

"Farhan" <hmfarhan(a)hotmail.com> skrev i meddelelsen
news:5691DB10-33BF-40D2-B826-9D496A83EE9F(a)microsoft.com...
> Hi all,
>
> I am new to macro and using macro recoder to create macro. On one file
> which
> I recieve every week from material department. I need to reformat and
> create
> pivot table. I use macro recorder and everything is working good. However
> I
> need that when my recorded macro end it create a file with the same name
> with
> the date and time it was create. I need every week a new file and I dont
> want
> to replace the one. Please let me know how I can create a new file with
> the
> date and time everyweek when I run my macro. Thanks
> --
> Farhan

From: Dave Peterson on
But don't include the colon in the time portion of the format() argument.

"mmddyy_hh:mm"

I'd use:
"mmddyy_hhmmss"



Per Jessen wrote:
>
> Hi Farhan
>
> This should do it, just insert your path in the MyPath variable:
>
> Dim MyPath As String
> Dim fName As String
> MyPath = "C:\Temp\" 'Change to suit
> fName = "MyFile_" & Format(Now, "mmddyy_hh:mm")
> ActiveWorkbook.SaveAs Filename:=MyPath & fName
>
> Best regards,
> Per
>
> "Farhan" <hmfarhan(a)hotmail.com> skrev i meddelelsen
> news:5691DB10-33BF-40D2-B826-9D496A83EE9F(a)microsoft.com...
> > Hi all,
> >
> > I am new to macro and using macro recoder to create macro. On one file
> > which
> > I recieve every week from material department. I need to reformat and
> > create
> > pivot table. I use macro recorder and everything is working good. However
> > I
> > need that when my recorded macro end it create a file with the same name
> > with
> > the date and time it was create. I need every week a new file and I dont
> > want
> > to replace the one. Please let me know how I can create a new file with
> > the
> > date and time everyweek when I run my macro. Thanks
> > --
> > Farhan

--

Dave Peterson
From: Farhan on
Thanks friends , I will try it on Monday and let you know.
--
Farhan


"Dave Peterson" wrote:

> But don't include the colon in the time portion of the format() argument.
>
> "mmddyy_hh:mm"
>
> I'd use:
> "mmddyy_hhmmss"
>
>
>
> Per Jessen wrote:
> >
> > Hi Farhan
> >
> > This should do it, just insert your path in the MyPath variable:
> >
> > Dim MyPath As String
> > Dim fName As String
> > MyPath = "C:\Temp\" 'Change to suit
> > fName = "MyFile_" & Format(Now, "mmddyy_hh:mm")
> > ActiveWorkbook.SaveAs Filename:=MyPath & fName
> >
> > Best regards,
> > Per
> >
> > "Farhan" <hmfarhan(a)hotmail.com> skrev i meddelelsen
> > news:5691DB10-33BF-40D2-B826-9D496A83EE9F(a)microsoft.com...
> > > Hi all,
> > >
> > > I am new to macro and using macro recoder to create macro. On one file
> > > which
> > > I recieve every week from material department. I need to reformat and
> > > create
> > > pivot table. I use macro recorder and everything is working good. However
> > > I
> > > need that when my recorded macro end it create a file with the same name
> > > with
> > > the date and time it was create. I need every week a new file and I dont
> > > want
> > > to replace the one. Please let me know how I can create a new file with
> > > the
> > > date and time everyweek when I run my macro. Thanks
> > > --
> > > Farhan
>
> --
>
> Dave Peterson
> .
>