Saving templates to workbooks using a macro
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Saving templates to workbooks using a macro

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    With my booking system I am creating, a template is opened at the beginning of each month and needs to be then saved as a workbook with the name "BkingSystem-March-02" I was wondering if anyone knew of a macro, or how I could create one, which automatically saves the workbook when activated with the above title, but changes due to the month and year which is it saved in. If anyone could help I would greatly appreciate this, Bex X

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Put the following code in the code for your Workbook:

    Private Sub Workbook_Open()
    ThisWorkbook.SaveAs Filename:="BkingSystem-" & Month(Date) & "-" & Year(Date)
    End Sub

    Does this work for you or do you want the Month name to appear?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok I tried that and it saved as "BkingSystem --1999.xls"

    Any ideas on how I can sort this?

    BEX

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this instead:
    "BkingSystem-" & Month(Now) & "-" & Year(Now)

    What version of excel are you running?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try,

    Private Sub Workbook_Open()
    Dim MyDate
    Dim MyMonth
    MyDate = Date
    MyMonth = Month(MyDate)
    ThisWorkbook.SaveAs Filename:="BkingSystem-" & MonthName(MyMonth) & "-" & Year(Date)
    End Sub

    Hope this helps. JSW

    P.S. The code go's in the "ThisWorkBook" module!

    Note: I tested the code and it saved the file as: BkingSystem-March-2002.xls in the active directory?

    [ This Message was edited by: Joe Was on 2002-03-20 14:53 ]

    [ This Message was edited by: Joe Was on 2002-03-20 14:57 ]

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Howdy Bex. You may want to verify your computer system's date and time. Double click on the clock on the bottom right of your screen, in the windows task bar and make sure it's current (assuming your using Windows).

    Food for thought

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-20 14:50 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've tried it on both Windows 98 and Windows XP and the same problem occurs, any ideas,

    BEX
    xx

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What version of excel are you running, and like Nate is implying, your system time and date are completely wrong.

  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've tried it on both Windows 98 and Windows XP and the same problem occurs, any ideas,

    BEX
    xx

  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you all very much for ur help, Joe's method seems to work fine! Cheers! BEX
    xx

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
  •  

 

 
DMCA.com