Results 1 to 15 of 15

Calculating the prior month given the current month

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Calculating the prior month given the current month

    Hello everyone. I am currently coding a project which requires information from a prior month. What I have is a message box which prompts the user to enter the date: dd-mmm-YYYY format. That value is then stored as a variable deontoed fDate. I would like to create a sub macro which takes fDate and"subtracts a month" and create a file which will be named after that, for instance if the current month is 30-jan-2010, then a file will be created and named 31-dec-2009. My line of thought was "fdate -1", but that returns an error. Please see the code below, specificaly Sub CreateFiles (). Any help is greatly appreciated.


    Option Explicit
    
        Dim fDate  As String
        Dim fPath  As String
    
    ____________________________________________________
        
    Sub CreateFolder()
    
        Dim Fldr   As String
        Dim ErrBuf As String
    
        fDate = Application.InputBox("Enter a date in the format shown:", "Date to add...", Format(Date, "DD-MMM-YYYY"))
        If fDate = "False" Then Exit Sub
    
        fPath = "L:\"
    
        On Error GoTo ErrorHandler
            
            Fldr = fPath & fDate & "_157"
            MkDir Fldr
            
            Fldr = fPath & fDate & "_157\" & "157_Reports"
            MkDir Fldr
            
            Fldr = fPath & fDate & "_157\" & "Support_Summaries"
            MkDir Fldr
    
            Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Roll_forward_wTA"
            MkDir Fldr
            
            Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Roll_forward_12m"
            MkDir Fldr
            
            Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Terminated"
            MkDir Fldr
            
            Fldr = fPath & fDate & "_157\" & "157_Reports\" & "L3_IDA"
            MkDir Fldr
            
            Fldr = fPath & fDate & "_157\" & "157_Reports\" & "IBRD_L3"
            MkDir Fldr
            
            Fldr = fPath & fDate & "_157\" & "105_Reports"
            MkDir Fldr
        
        If Len(ErrBuf) > 0 Then MsgBox "The following folders already existed:" & vbLf & vbLf & ErrBuf
        
        Exit Sub
        
    ErrorHandler:
        ErrBuf = ErrBuf & vbLf & Fldr
        Resume Next
        
    End Sub
    
    _____________________________________________________
    
    
    Sub MoveFilesFolder2Folder()
        Dim fso
        Dim sfol As String
        Dim dfol As String
    
        sfol = "L:\157_Support_Summaries"
        dfol = fPath & fDate & "_157\" & "Support_Summaries"
    
        Set fso = CreateObject("Scripting.FileSystemObject")
            
            On Error Resume Next
            
            If Not fso.FolderExists(sfol) Then
            MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
    
            ElseIf Not fso.FolderExists(dfol) Then
            MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
            
            Else
            fso.CopyFile (sfol & "\*.*"), dfol ' Change "\*.*" to "\*.xls" to move Excel Files only
            
            End If
    
            If Err.Number = 53 Then MsgBox "File not found"
    End Sub
    
    ______________________________________________________
    
    Sub CreateFiles()
    
        Dim sPath1      As String
        Dim sPath2      As String
        Const sFileOut1 As String = "105_version1.xlsm"
        Const sFileOut2 As String = "CDS.xlsm"
        
        sPath1 = fPath & fDate & "_157\105_Reports\"
        sPath2 = fPath & fDate & "_157\Support_Summaries\"
        
        Workbooks.Add
        With ActiveSheet
            .Name = fDate & "_105_v1"
            .Parent.SaveAs Filename:=sPath1 & sFileOut1, _
                           FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                           CreateBackup:=False
            .Parent.Close
        End With
        
        Workbooks.Add
        With ActiveSheet
            .Name = fDate - 1 & "_CDS" <------------------------------------- Error
            .Parent.SaveAs Filename:=sPath2 & sFileOut2, _
                           FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                           CreateBackup:=False
            .Parent.Close
        End With
        
    End Sub
    Last edited by AnthonyWB; 04-15-2010 at 03:11 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1