Solved

Create File

Posted on 2011-09-08
2
198 Views
Last Modified: 2012-05-12

Hi,

I have some code that saves a file in the second most recent month

It is doing that perfecty but will name the file Sep11 in the August fiolder that it finds

Can i have this amended so it will name it Aug11

Thanks
Seamus
0
Comment
Question by:Seamus2626
2 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 36501550
Function Date_FileName(pPath As String, pFilePrefix As String) As String
Dim DayOfWeek As Integer, DayDiff As Integer, CharDate As String, MonthNo As Integer, CharYear As String, CharMonth  As String
Dim Result As String
    
    DayOfWeek = Weekday(Date)                                   ' Sunday is 1, Monday is 2, Tuesday is 3 etc
    If DayOfWeek = 2 Then                                       ' If a monday then we need the date as of friday, otherwise previous day
        DayDiff = 3
    Else
        If DayOfWeek = 1 Then
          DayDiff = 2                                           ' If a Sunday then we need the date as of friday, otherwise previous day
        Else
          DayDiff = 1                                           ' otherwise it should be set to the previous day
        End If
    End If
    CharDate = Format(DateAdd("m", -1, Date) , "mmyy")               ' Get the character date formwatted how we want it

    CharMonth = Format(DateAdd("m", -1, Date), "mmm yyyy")
    Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
    Date_FileName = Result
End Function

Open in new window

Function Date_FileName(pPath As String, pFilePrefix As String) As String
Dim DayOfWeek As Integer, DayDiff As Integer, CharDate As String, MonthNo As Integer, CharYear As String, CharMonth  As String
Dim Result As String
    
    DayOfWeek = Weekday(Date)                                   ' Sunday is 1, Monday is 2, Tuesday is 3 etc
    If DayOfWeek = 2 Then                                       ' If a monday then we need the date as of friday, otherwise previous day
        DayDiff = 3
    Else
        If DayOfWeek = 1 Then
          DayDiff = 2                                           ' If a Sunday then we need the date as of friday, otherwise previous day
        Else
          DayDiff = 1                                           ' otherwise it should be set to the previous day
        End If
    End If
    CharDate = Format(DateAdd("m", -1, Date)- DayDiff , "mmyy")               ' Get the character date formwatted how we want it

    CharMonth = Format(DateAdd("m", -1, Date), "mmm yyyy")
    Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
    Date_FileName = Result
End Function

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 36501579
Excellent! Thank you very much pratima_mcs

Cheers,
Seamus
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question