Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Is this possible?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a series of file names which have the naming convention mmmddblahblah.xls, whereby mmm refers to the first 3 letters of the month and dd refers to the day of that month. For example, the file for April 7 would be named apr07blahblah.xls.

    My question is: Is there a way I could write a macro to open up 7 sequential files (ie. April 27 - May 3) based upon the order of the months and their respective number of days, using the mmmdd part of their names?

    Thanks!
    StrangeLuck

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm sure it's possible somehow. One of the questions that you'll probably be asked is from which date we'd be starting? Would you want to be able to determine that each time or would it start with, for instance, yesterday?
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 11:20, Dreamboat wrote:
    I'm sure it's possible somehow. One of the questions that you'll probably be asked is from which date we'd be starting? Would you want to be able to determine that each time or would it start with, for instance, yesterday?
    This would be determined by the calendar weeks. Essentially, I want to have the user input the end date for the week and have the workbooks for the entire week open.

    Thanks,
    StrangeLuck

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Something like this would work (note:: there's some strange stuff in this, someone might want to debug this even more, but it does what you ask):


    Public Sub main()
    Dim oDate As Date
    Dim i As Single
    Dim sFileName As String

    oDate = #4/27/2002#

    For i = 1 To 7
    oDate = oDate + 1
    sFileName = MonthName(Month(oDate), True) & Format(Day(oDate), "dd") & "Blahblah.xls"
    MsgBox sFileName 'Replace this with a "open workbook" statement using sFileName
    Next
    End Sub


    The "strange stuff" is partly to do with the way Excel handles dates and partly to do with the "Format" statement. For some reason, the Format statement was giving my "26" for the first day.

    Anyway, I hope this helps.

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What are you storing in those files? Is it just some generic data in a particular format in each workbook? Have you ever considered storing the data in these files in a single Access database and using queries to load relevant data into a single workbook at runtime?

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 11:34, Mark O'Brien wrote:
    Something like this would work (note:: there's some strange stuff in this, someone might want to debug this even more, but it does what you ask):


    Public Sub main()
    Dim oDate As Date
    Dim i As Single
    Dim sFileName As String

    oDate = #4/27/2002#

    For i = 1 To 7
    oDate = oDate + 1
    sFileName = MonthName(Month(oDate), True) & Format(Day(oDate), "dd") & "Blahblah.xls"
    MsgBox sFileName 'Replace this with a "open workbook" statement using sFileName
    Next
    End Sub


    The "strange stuff" is partly to do with the way Excel handles dates and partly to do with the "Format" statement. For some reason, the Format statement was giving my "26" for the first day.

    Anyway, I hope this helps.
    It appears that I cannot use the "MonthName" function, which must be a Excel 2000/2002 option. I am using Excel 97, unfortunately. Can anyone help me further?

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 11:42, dhoffman wrote:
    What are you storing in those files? Is it just some generic data in a particular format in each workbook? Have you ever considered storing the data in these files in a single Access database and using queries to load relevant data into a single workbook at runtime?
    Thanks for your input, but I really need to keep these files in the format they currently are. It's work-related.

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, since you used a smiley, I've created a stunted Excel 97 version of the function copy and paste this into your module:

    Private Function MonthName97(ByVal iMonth As Integer)


    Select Case iMonth

    Case 1
    MonthName97 = "Jan"
    Case 2
    MonthName97 = "Feb"
    Case 3
    MonthName97 = "Mar"
    Case 4
    MonthName97 = "Apr"
    Case 5
    MonthName97 = "May"
    Case 6
    MonthName97 = "Jun"
    Case 7
    MonthName97 = "Jul"
    Case 8
    MonthName97 = "Aug"
    Case 9
    MonthName97 = "Sep"
    Case 10
    MonthName97 = "Oct"
    Case 11
    MonthName97 = "Nov"
    Case 12
    MonthName97 = "Dec"
    End Select

    End Function


    Then use this line of code (instead of the old one:



    sFileName = MonthName97(Month(oDate)) & Format(Day(oDate), "dd") & "Blahblah.xls"


    HTH

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 12:04, Mark O'Brien wrote:
    Well, since you used a smiley, I've created a stunted Excel 97 version of the function copy and paste this into your module:

    Private Function MonthName97(ByVal iMonth As Integer)


    Select Case iMonth

    Case 1
    MonthName97 = "Jan"
    Case 2
    MonthName97 = "Feb"
    Case 3
    MonthName97 = "Mar"
    Case 4
    MonthName97 = "Apr"
    Case 5
    MonthName97 = "May"
    Case 6
    MonthName97 = "Jun"
    Case 7
    MonthName97 = "Jul"
    Case 8
    MonthName97 = "Aug"
    Case 9
    MonthName97 = "Sep"
    Case 10
    MonthName97 = "Oct"
    Case 11
    MonthName97 = "Nov"
    Case 12
    MonthName97 = "Dec"
    End Select

    End Function


    Then use this line of code (instead of the old one:



    sFileName = MonthName97(Month(oDate)) & Format(Day(oDate), "dd") & "Blahblah.xls"


    HTH
    This would work, but I wanted to be able to have the user specify oDate everytime the macro runs.

    Thanks,
    StrangeLuck

  10. #10
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use this:


    oDate = InputBox("Enter a date, mm/dd/yyyy, e.g. 04/23/2002", "Date")


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
  •