Thread: Dim Statement

Leith gave me this piece of code a couple of weeks ago which is brilliant. Is there a way to write a Dim statement to automate this part of the macro.

    FileName = Dir(FilePath & "\*MAY FAB EST*.xls")

The macro is fine like this but it is only good for the month of May. I have added MonthID in the filepath below to direct it to the May, June etc Sub Directories.

FilePath = "J:\Weekly Sales Summaries\D&C\Fab Report\CRAFT FAB 2011\" + MonthID + ""

I was thinking of something like this but I can't seem to get it to slot into the first piece of code.

If MonthID = 6 Then 
MonthID1 = *JUN FAB EXT*.XLS 
End If

But when I put MonthID1 into the code as below, it doesn't work. Is it because of the wildcards?

    FileName = Dir(FilePath & "\" & MonthID1)

Thanks

Full Macro below

    FilePath = "J:\Weekly Sales Summaries\D&C\Fab Report\CRAFT FAB 2011\" + MonthID + ""

    FileName = Dir(FilePath & "\*MAY FAB EST*.xls")

      Set RegExp = CreateObject("VBScript.RegExp")
      RegExp.Pattern = "(.*)(\d{6})(.+)"

      Do While FileName <> ""
        If RegExp.Test(FileName) Then
           fnDate = RegExp.Replace(FileName, "$2")
             D = Left(fnDate, 2)
             M = Mid(fnDate, 3, 2)
             Y = Right(fnDate, 2)
           fnDate = DateSerial(Y, M, D)

           If fnDate > LastDate Then
              LastDate = fnDate
              LastFile = FileName
           End If
        End If

        FileName = Dir()
      Loop

    If LastFile <> "" Then
       Workbooks.Open FilePath & "\" & LastFile
       NewDate = Format(CDate(LastDate) + 7, "ddmmyy")
       NewName = RegExp.Replace(LastFile, "$1" & NewDate & "$3")
       ActiveWorkbook.SaveCopyAs FilePath & "\" & NewName
       ActiveWorkbook.Close SaveChanges:=False
       Workbooks.Open FilePath & "\" & NewName
    End If

Maybe something like:

Filename = Dir(FilePath & "\*" & Format(DateSerial(1900, MonthID, 1), "MMM") & " FAB EST*.xls)

Dom

If you name every file according it's week, there's no need to create separate folders for every month.
Using the ISO-standard (yyyymmdd) makes your code more robust (besides the curious Excel behaviour handling dates).
Shortly: 'structuring precedes coding'
The latest file can be found using a loop from the present day backwards. Using separate folders for each month complicates the code unnecessary and adds no functionality.
There's no need to open a workbook to produce it's copy.

sub snb()
  c00 = "J:\Weekly Sales Summaries\D&C\Fab Report\CRAFT FAB\"
  x=0

  do
    c01 = Dir(c00 & format(date-x,"yyyymmdd") & " FAB EST.xls")
    x=x+1
  loop until c01<>""  

  copyfile c00 & c01,c00 & format(cdate(left(c01,8)+7),"yyyymmdd") & mid(c01,9)
End Sub

Originally Posted by timbo1957

Thanks Dom,

When I run that Filename = " FAB EST.xls" it misses off the "JUN" in front of it.

Had you assigned a value to MonthID?

Dom

Dim strFileName As String
Dim strFilePath As String
Dim lngMonthID As Long

strFilePath = "H:"
lngMonthID = 6

'just to show the file name and path
MsgBox strFilePath & "\*" & Format(DateSerial(1900, lngMonthID, 1), "MMM") & " FAB EST*.xls"

strFileName = Dir(strFilePath & "\*" & Format(DateSerial(1900, lngMonthID, 1), "MMM") & " FAB EST*.xls")

Dom

One thing I noticed is that the original filename was "*JUN FAB EXT*.XLS" and the filename in your last post was "*JUN FAB EST*.xls"

EST should be EXT, or vice versa.

What is the date format you want your filename to end with? To me, as an American 060611 means "June 06, 2011". 130611 just makes no sense at all.

Regardless, I would reevaluate your date scheme you use to name your files. What you want is not readability, but sortability. If you name your files in the format ddmmyy, it will sort so that the first of each and every month comes before the second of each and every month. If you name them mmddyy, then this means all your January files, of each and every year, will be before your February files. This can make finding specific files take longer than necessary.

Really, when naming files with dates, it is best to go with yyyymmdd. That way, they sort from oldest to newest very neatly. The reason you want this neat sorting is because functions like DIR() look at filenames in ascending order. But also, if you are using wildcards, it will return the closest match. This can cause headaches for your coding to try and figure out in what order your file will appear and how to arrange your wildcard search string.

For instance, you said you tried this code first:

fnDate = DateSerial(Y, M, D)

, but your filename was 060611
When DIR() tries to find the first match, it takes the string 110606 and goes down the filelist. It cannot find an exact match, so it finds the closest match. 300511 is much closer to 110606 than 060611.

So you changed the code to:

fnDate = DateSerial(D, M, Y)

and got your file 060611. But what would have happened if somehow you got your days mixed up and there was no 060611 file, but there was a 060610 file? Of course it would have given you 060610. But you and I know there is a whole year's difference between the two. Wouldn't you rather search for 110606 and get 110605?