Date - Driving me mad!

[bposaner]

Hi, i'm getting all stuck and cant find my way out HELP!
I am reading in files 1-31 using a counter and loop, which are days of the month. Before doing the business I need to exclude weekends and I'm trying to build a date and dump the ones over the weekend. you'll see below my poor attempt.

But i cant seem to figure this out. I'm sure its a simple one, but I'm a VBA dullard!!

Any help would be tops.
Thanks
Ben

For counter2 = 1 To 31

          mm = Val(Mid(DateRun, 1, 2))
          yyyy = Val(Mid(DateRun, 5, 4))
            dd = counter2
       
        dateChk = mm & dd & yyyy
        DayChk = Weekday(dateChk, vbMonday)
        If DayChk < 6 Then

 

[cmart02]

If I understood it correctly, what you want is:

dateChk = DateSerial(Year(yyyy), Month(mm), Day(dd))

 

[bposaner]

DateChk shows a value of 00:00:00, not sure why.
So when it goes into excel its not the correct date.

Sub TopTrans()

Dim Message, Title, Default, DateBox, dateChk As Date
Message = "Enter date required in mmyyyy format."  
Title = "DateRun" 
Default = "102005"  


DateRun = InputBox(Message, Title, Default)

Windows("TopTrans.xls").Activate
Sheets("Sheet1").Name = "" & DateRun & ""

For counter2 = 1 To 31

            mm = Val(Mid(DateRun, 1, 2))
          yyyy = Val(Mid(DateRun, 3, 4))
            dd = counter2
       
        dateChk = DateSerial(Year(yyyy), Month(mm), Day(dd))

        DayChk = Weekday(dateChk, vbMonday)
        If DayChk < 6 Then
        
            Workbooks.OpenText Filename:= _
        "G:\" & DateRun & "\" & counter2 & ".dat" _
        , Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 9), Array(1, 1), Array(10, 1), Array(21, 1), Array(31, 1))


Region = Cells(1, 1)
For Counter1 = 1 To 65000
    If Cells(Counter1, 1) = "" Then
        If Cells(Counter1, 2) = "" Then Exit For
        Cells(Counter1, 1) = Region
    Else
         Region = Cells(Counter1, 1)     'Workbooks("TopTrans.xls").Sheets("sheet1").
    End If
     
    If Cells(Counter1, 4) = "" Then
       Cells(Counter1, 5) = ""
    Else
        Cells(Counter1, 5) = "" & dateChk & ""
    End If
    
    If Cells(Counter1, 2) = "" Then
        Cells(Counter1, 1) = ""
    End If
    
Next Counter1
Close
        
        
        End If
Next counter2
      

End Sub