Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Test that date hasnt passed (VBA Excel 2003)

    Hi,

    Im importing some data into a spreadsheet that is to only show data for the current date or the future.

    I cant seem to find any script in the vba help or even the microsoft vba book that shows how to put together such a test.

    Does anyone have some sample script that will check the data as its being imported and to only allow through information for the current date, i.e. 23/04/2007, and future dates?

    Any help is grately appreciated.

    Many Thanks.
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    Could you provide some details about what kind of file you are importing, and how? Is it another workbook, or a text file, or are you importing from a database?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    If all you need is to check the date, some applicable VBA functions are:

    Date - will return today's date
    IsDate(strDateString) will test that the date is a valid date
    DateValue(strDateString) will convert a string to a date variable

    for example:

    If IsDate(strDateString) Then
    If DateValue(strDateString) >= Date Then Debug.Print "True"
    End If

    You will have to be sure you understand the format of the date in the source file. MS VBA tends to interpret ambiguous date strings as being US convention, i.e, 4/10/2007 as April 10, not October 4.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    Hi Hans,

    The file Im importing is a pipe delimited text file, via a straight forward excel vba macro. Do you need to see the script?

    Regards.
    Lee

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    Hi John,

    Thanks for your response.

    With the date check, I need to make sure that the date hasnt passed. The text file Im importing has both passed and future dates in. I only need to display on the spreadsheet future dates, and ensure that it doesnt import a date from last week or last month.

    Regards.
    Lee

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    Without the code and a sample of the source layout, Loungers can't provide any more specific advice.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    No problem, sorry about that.

    The script code is below and I attach a sample from the text file;

    Public Sub SMSSort()

    Dim iDelimiter As Integer
    Dim iLength As Integer
    Dim iColumn As Integer
    Dim iRow As Integer
    Dim iCount As Integer
    Dim strLineofText As String
    Dim strField As String

    Columns("A:C").Select
    Range("A1:C1").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .Font.Name = "Times New Roman"
    .Font.Size = 12
    End With

    Range("A1").Select
    Open "C:TLOGTEST.txt" For Input As #1
    Line Input #1, strLineofText
    iDelimiter = InStr(strLineofText, "|")

    iCount = 1
    iRow = 2
    iColumn = 1


    While iDelimiter <> 0
    strField = Left(strLineofText, iDelimiter - 1)
    ActiveSheet.Cells(1, iColumn) = strField
    iLength = Len(strLineofText)
    strLineofText = Right(strLineofText, iLength - iDelimiter)
    iDelimiter = InStr(strLineofText, "|")
    iColumn = iColumn + 1
    Wend

    Do
    Line Input #1, strLineofText
    iDelimiter = InStr(strLineofText, "|")
    iColumn = 1
    While iDelimiter <> 0
    strField = Left(strLineofText, iDelimiter - 1)
    ActiveSheet.Cells(iRow, iColumn) = strField
    iLength = Len(strLineofText)
    strLineofText = Right(strLineofText, iLength - iDelimiter)
    iDelimiter = InStr(strLineofText, "|")
    iColumn = iColumn + 1
    Wend
    ActiveSheet.Cells(iRow, iColumn) = strLineofText
    iRow = iRow + 1
    Loop While EOF(1) = False
    Close #1

    Columns("A:C").EntireColumn.AutoFit
    End Sub
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    Which column is going to contain the dates? In other words, is the date the first item in each line, or the second, or ...?

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    I would assume the first column using a "dd/mm/yyyy" format, prior to the pipe.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    Oops, yes. I hadn't looked at the attachment. Thanks!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    Try the attached version.
    Attached Files Attached Files

  12. #12
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    That works a treat, thanks for your time and help Hans.

    Best Regards.
    Lee

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    I wonder much slower this (untested) would be:

    dtmDate = DateSerial(Split(strDate, "/") 3, Split(strDate, "/") 2, Split(strDate, "/") 1)

    (it came to me in a nightmare. And, oddly enough, I'm off to the dentist now.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    Ouch! It must be really bad if you're having nightmares about VBA and Excel!

    The line should be

    dtmDate = DateSerial(Split(strDate, "/")(2), Split(strDate, "/")(1), Split(strDate, "/")(0))

    and it is more than 60% slower, among others because VBA isn't smart enough to compute the Split only once. If you change it to

    arr = Split(strDate, "/")
    dtmDate = DateSerial(arr(2), arr(1), arr(0))

    it's still 25% slower than the original version.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test that date hasnt passed (VBA Excel 2003)

    I assume that

    dateconv = vbFalse

    should be

    stringtodateconv = vbFalse

Page 1 of 2 12 LastLast

Posting Permissions

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