Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Be REALLY grateful for some quick advice with this one.....

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    If I want to set a macro to save a file as a certain date, how do I do it?

    At the moment, the macro copies a block of data, pastes it into a template, then I want it to save that template as last week's date. For instance, the file template is called Weekly Report Template, when the macro saves it I need it be saved as:

    Weekly Report - 18th-24th February 2002

    as that is the period that the report covers. However, of course NEXT week it will have to save it as:

    Weekly Report - 25th Feb-3rd March 2002

    and so on....

    Can anyone help me?



  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-26 06:10, Audiojoe wrote:

    If I want to set a macro to save a file as a certain date, how do I do it?

    At the moment, the macro copies a block of data, pastes it into a template, then I want it to save that template as last week's date. For instance, the file template is called Weekly Report Template, when the macro saves it I need it be saved as:

    Weekly Report - 18th-24th February 2002

    as that is the period that the report covers. However, of course NEXT week it will have to save it as:

    Weekly Report - 25th Feb-3rd March 2002

    and so on....

    Can anyone help me?


    What is the relationship between the date you run the macro and the dates of the material being saved? For example, for
    Weekly Report - 18th-24th February 2002
    will the save take place on Feb 25th? Feb 26th? Some varied date but always during M-F after the week being saved?

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, try this.

    Put this UDF somewhere in the module.

    Code:
    Function LastWeek(Dt As Date) As String
    Dim T1 As Long
    Dim T2 As Long
    T1 = Dt - (WeekDay(Dt, vbMonday) - 1)
    T2 = T1 + 6
    
    LastWeek = Format(T1, IIF(Month(T1) <> Month(T2),"dd mmm","dd")) & "-" & Format(T2, "dd mmmm yyyy")
    End Function
    This function accepts a DATE as a parameter, and returns the WEEK of that date (Mon - Sun) trying to resemble your format.

    Then, you could use it like this:

    ActiveWorkbook.SaveAs "Weekly Report " & LastWeek(Date-7) & ".xls"

    I think that covers it.

    _________________
    Regards,

    Juan Pablo G.
    MrExcel.com Consulting

    [ This Message was edited by: Juan Pablo G. on 2002-02-26 06:36 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It will always be on the day after the week has finished, for example:

    18th-24th Feb 2002

    would have been saved on the 25th

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-26 06:36, Audiojoe wrote:
    It will always be on the day after the week has finished, for example:

    18th-24th Feb 2002

    would have been saved on the 25th
    Look at my reply, I think that should solve your problem.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for getting back to me so quickly, I'll get it tried out

    Regards

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, I'm getting a little lost now, what is wrong with this? I guess it's glaring but I'm new to this racket...


    Sub savingcopiedscripts()

    ' savingcopiedscripts Macro
    ' Macro recorded 26/02/02 by aroche

    Function Lastweek(Dt As Date) As String
    Dim t1 As Long
    Dim t2 As Long
    t1 = Dt - (WeekDay(Dt, vbMonday) - 1)
    t2 = t1 + 6

    Last week = Format(t1, IIf(Month(t1) <> Month(t2), "dd mmm", "dd")) & "-"

    ChDir "H:Stats"
    ActiveWorkbook.SaveAs FileName:="H:Stats", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

    End Function

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, does anyone know how on earth I can fix the above?


  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone?

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you really need to post every 3 seconds if no one has answered ?

    The problem is that you're using a function inside of a Sub. Put it like this:

    Function LastWeek....
    ....
    End Function

    Sub YourSub()

    .....

    End Sub
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •