Problem with dates in macro...

[lopiteaux]

Hi all - I've got a little problem with a macro I'm running which is quite frankly, got me completely dumstruck.

In short, the macro has two parts - firstly, it imports data from a source .txt file and copies it into the controlfile (the file containing the macro). So far, so good. Because the import uses a VLOOKUP to find a date corresponding to the previous business day, I've used the following code:

Dim RateDate as Date

strName = InputBox("Input Report Date", "Import Report", Format(Now, "ddmmyy"))
specyear = Right(strName, 2)
specmonth = Left(Right(strName, 4), 2)
specday = Left(strName, 2)

If Weekday(Date) = 2 Then
RateDate = Format((specday - 3 & "/" & specmonth & "/" & "20" & specyear), "dd/mm/yyyy")

Else
RateDate = Format((specday - 1 & "/" & specmonth & "/" & "20" & specyear), "dd/mm/yyyy")
End If

As you can see it uses an InpotBox with today as the default date and then manipulates this date into the correct format for my VLOOKUP. It then executes the following bit of code to look up the value:

USDRate = Application.VLookup(CLng(RateDate), Columns("A:C"), 2, False)
Windows(ControlFile).Activate
Sheets("IDRRates").Activate
Range("C2").Select
ActiveCell.Value = USDRate

and subsequently copies it into the controlfile. As mentioned, up until this point it all works fine. The next step in the macro - the step giving me the headache - copies the information off the controlfile into a .csv file, which is then uploaded via XML into another piece of software. The software recognises dates in the British format, i.e. dd/mm/yyyy, and in the controlfile the dates are saved and displayed as such (if I reformat the dates to d mmmm yyyy I get the correct date), but for some or other reason just copying it over doesn't seem to work - the software rejects the import, quoting the value of the date as incorrect (in this example, quoting it as "6/7/2011" when the date displayed in the .xls controlfile is "07/06/2011").

I used the following bit of code on the dates to transform them into date from text - which incidentally, worked perfectly for a good two weeks before breaking on me:

Range("B2").Select 'Where B2 is the start of the series.

Do
ActiveCell.Value = Left(ActiveCell, 10)
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
Range("B:B").Select
Selection.NumberFormat = "dd/mm/yyyy"

If I run this code in step through mode, I can see that the dates are effectively flipped around - i.e. from British "07/06/2011" to American "06/07/2011", causing issues with the system.

Can someone please help? I've been banging my head against the wall on this for a day or two and cannot seem to find the answer...

Thanks,

C.

 

[AlphaFrog]

Why the small font? Hard to read for us old guys.

I don't quite follow everything going on here, but here's a guess.

I used the following bit of code on the dates to transform them into date from text - which incidentally, worked perfectly for a good two weeks before breaking on me:

Range("B2").Select 'Where B2 is the start of the series.

Do
ActiveCell.Value = Left(ActiveCell, 10)
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
Range("B:B").Select
Selection.NumberFormat = "dd/mm/yyyy"

If I run this code in step through mode, I can see that the dates are effectively flipped around - i.e. from British "07/06/2011" to American "06/07/2011", causing issues with the system.

Can someone please help? I've been banging my head against the wall on this for a day or two and cannot seem to find the answer...

Thanks,

C.

Did it work for two weeks up until the dates in column B went past the 13th of the month? I think that's because the system was using the day value as the month and the month value as the day. So once any day value in a date went past 12, it couldn't be used as a Month value and was no longer recognized as a valid date.

Maybe try something like this...

    Dim cell As Range

    For Each cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
        cell.Value = DateSerial(Mid(cell, 7, 4), Mid(cell, 4, 2), Left(cell, 2))
    Next cell
    
    Range("B:B").NumberFormat = "dd/mm/yyyy"

 

[lopiteaux]

AlphaFrog - thanks a million for this one. Can't believe I missed it, in retrospect it's so obvious...


It seems to be working perfectly now - I'll let you know tomorrow when I run it in again whether this is true.


I've got two quick questions for you, if you don't mind... firstly - and I know this might be a bit of a blonde question - how does Range("B" & Rows.Count).End(xlUp)calculate the number of the last cell? I know that obviously B concatenates with the count of rows, suggested by Rows.Count, but why the addition of .End(xlUp)?

Secondly, and this is a bit of an intricate and open question, one of the manual steps behind this process is that I have to retrieve a .xls file from a https site, where you have to enter a login and a login password. My question is this - given the https address, as well as the login information, is it possible to write a macro that retrieves this file automatically?

Thanks again for all your help.

 

[AlphaFrog]

You're welcome.

how does Range("B" & Rows.Count).End(xlUp) calculate the number of the last cell? I know that obviously B concatenates with the count of rows, suggested by Rows.Count, but why the addition of .End(xlUp)?

Select any empty cell well below the last used cell in a column
Then Ctrl+Up Arrow on the keyboard.
That's what .End(xlUp) does.
Finding the Last Cell in a Range

Secondly, and this is a bit of an intricate and open question, one of the manual steps behind this process is that I have to retrieve a .xls file from a https site, where you have to enter a login and a login password. My question is this - given the https address, as well as the login information, is it possible to write a macro that retrieves this file automatically?

Probably Yes. The specifics on how to do that would depend on the website. Here's a couple of things to look at to get you started.
http://www.mrexcel.com/forum/showthread.php?t=319342
http://vbadud.blogspot.com/2009/08/how-to-login-to-website-using-vba.html