imported data from web: comma separator and date format problem (vba)

Hello everyone,

I 'm trying to make a macro that does a few things:

1) download data from the web (basically recording a macro to refresh the data that was imported with the data wizard)
2) finding a max/min value in a given column, based on a date in another column. For an example, see below.

Main problem: I'm importing data from a UK site but I work on a Belgian computer with Belgian/French date,number and time formats. This means numbers and dates aren't recognised properly and I somehow have to convert the imported data to something my pc recognises. So here we arrive at the underlying problems:

1) I recorded a few "find and replace" actions into a macro. For example, using find and replace I convert 803,000.05 to 803000,05 But the result is not recognised as a number anymore, so my min() max() functions don't want to work. How can I force this into a number?

2) May 1 is imported by excel as "May 1", but april 30 is imported as "01/04/2030". This probably because in dutch, the word "may" does not exist, but april is written exactly like in English and Excel tries to make some kind of date of this. How can I prevent this last thing from happening?
My idea here is to make my own conversion matrix, meaning I "find and replace" all english months with dutch months, then change the format from text to date format. This should all happen within the macro.

If you guys could give me pointers on how to do all this, that would be much appreciated!

greetings,

Homecore


Extra information: an example table

Day Date Open High Low Close Volume
Friday June 08 28.86 30.19 28.15 30.08 881,209
Thursday June 07 29.81 29.87 28.85 28.93 492,015
Wednesday June 06 28.20 29.45 28.14 29.22 910,508
Tuesday June 05 27.84 28.39 27.56 27.91 630,885

In this set of imported data, I would like to find the max value of column "high", from june 6 and later dates. So the conversions should be June 06 > 6 juni (in Dutch), 29.45 > 29,45 and 910,508>910508. These should be recognised as a date and 2 numbers so I can use functions on it.

For your number columns, Data > Text to Columns, click Finish.

For the dates, select a column, Data Text to Columns, click Next twice, click Date, select the original date format in the dropdown (e.g. DMY) then click Finish.

Thanks for the tips, Thalassa!

For the number columns, using text to columns works, although I don't fully understand the underlying mechanics of it.

For the date columns however, I'm still having problems:

• the "text to columns" function assumes I have a given day, month ànd a year in my original table. So it proposes original date formats like DMY/YMD/....

• But in my original data, the column simply says "april 30", without a year mentioned in the table.

April 30 still becomes 1/04/1930

Can you elaborate on how I should fix this? If the webpage I'm importing shows april 30, I want it to become 30/04/2012 in Excel.

With April 30 in A1 this works for me


=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1))&"-"&LEFT(A1,FIND(" ",A1)-1)&"-"&YEAR(TODAY()))