Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Word 2003 - need VBA FileName time-stamp help

    Hello...

    I am macro beginner and digging with this macro since last few days. I would really appreciate any help.

    The macro is working absolutely fine but the "Filename" with different time stamp causing issue when it come to delete the old version.

    Attached word file has three buttons:

    1)First button labeled, “Create” simply create a checklist as version 1 with current date and time as file name and SaveAs the file to “Temp Folder”
    2)Second button labeled, “Modify File” perform two jobs:
    • SaveAs the same checklist from step one as version 2 with current date and time as file name to the same folder as in step 1 (i.e. “Temp Folder”). It works fine.

    • Delete old file meaning delete version 1 which was created in step one. And this is causing a issue, error message pop up stating “File not found” because for example; if I perform 1st step now the file will SaveAs as “TempJan_27_2012_A.Team_Day01_00_12_PM Rev1” then I perform 2nd step the file will SaveAs as “TempJan_27_2012_A.Team_Day01_05_10_PM Rev2” and looking for “TempJan_27_2012_A.Team_Day01_05_10_PM Rev1” to delete instead of“TempJan_27_2012_A.Team_Day01_00_12_PM Rev1”

    3)Third button labeled, “Final Save” (I have not work on it yet) will do the basically same jobs as second button but will SaveAs the file to “Completed folder” and will delete version 2 (which is created in step 2) from “temp folder.”

    Can some one please help me? I know if I will keep something else as a file name instead of current date and time it will solve my problem but I can’t keep anything else than the current time and date. Any solution? Any alternate way to meet my goal? A waiting for someone reply!!!

    Thank you in advance!!!!!!!!!
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Megan,

    Welcome as a new poster.

    Here's a couple of things I noticed.
    sPath = "P:\M's DOC\Rounds\Temp Rounds/Temp"
    Should Be:
    sPath = "P:\M's DOC\Rounds\Temp Rounds\Temp\"

    There are 2 other places you make this same mistake..at least you're consistent.

    The lack of the final "\" is why your Kill & Save statements do not work because the filename is concatenated directly on the path name w/o the separator.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply. I tired it but didn't help.

  5. #4
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File SaveAs with Current Date/Time

    I have a code that SaveAs the word file with current date and time stamp. It works perfect. However I want to change my code which let the user to modify that same file later on and also let re-save with current date and time. I would appreciate any help as I am not at all good with the VBA. Thank you!

    Code:
    Private Sub CommandButton1_Click()
      Dim sFileName As String
        Dim sPath As String
        CommandButton1.Enabled = False
        sFileName = Format(DateValue(Now()), "mmm_dd_yyyy") & "_" & Team.Value & "_" & Shift.Value & _
        Format(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())), "hh_mm_ss_AM/PM")
        If Len(Dir("\\P:\M's DOC\Rounds\Completed Rounds\" & Format(DateValue(Now()), "mmm_yyyy"), vbDirectory)) = 0 Then
            MkDir "\\P:\M's DOC\Rounds\Completed Rounds\" & Format(DateValue(Now()), "mmm_yyyy")
        End If
        sPath = "\\P:\M's DOC\Rounds\Completed Rounds\" & Format(DateValue(Now()), "mmm_yyyy")
        sFileName = sFileName & ".doc"
        ActiveDocument.SaveAs FileName:=sPath & "\" & sFileName, FileFormat:=xlNormal, ReadOnlyRecommended:=False
        MsgBox "Checklist has been saved"
        ThisDocument.Close SaveChanges:=False
    End Sub

Posting Permissions

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