Make (save) a file by using macro recorder.
in [Programming]
|
From: Farhan on 28 May 2010 22:41 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 29 May 2010 02:35 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 29 May 2010 07:32 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 29 May 2010 13:02 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 > . >
|
Pages: 1 Prev: Need VBA Code to Select text boxes in spreadsheet Next: Using a modeless form to show status |