paste HTML with date different in VBA from manual, format error

[Geoff Kelly]

If I copy an entire web page in IE that contains dates in dd/mm/yyyy format
(also my regional setting), then paste into a spreadsheet with CTL-V, the
dates are interpreted correctly. (the dates are in a column of a table) If I
perform the same function from VBA with worksheet.paste, dates with days >
12 are OK but others are treated as mm/dd/yyyy format. A similar post
recently mentioned VBA uses mm/dd/yyyy format only. I have no control over
the web page format.
Using a web query works fine but the web page provides buttons to change the
range of dates displayed, and uses the post method so I can't use a web
query for other than the default date range. I have to manually click the
button (etc) to display the required period, then copy/paste into excel, I
am trying to automate the paste and subsequent analysis.
Does anyone have a better solution than pasting the same info as text and
writing my own complex decoding code just to get the date right?
Using Excel 2000 but alsow want it to work on 97.
One would think Microsoft programmers are ingorant of international date
formats, or is VBA only meant to be used in the US?
Geoff Kelly

 

[Graeme Whelan]

If the date is always read into the same cell (eg: A1), and EXACTLY the
same format (mm/dd/yyyy), you could try using your VBA to insert a
formula in an adjacent cell like:
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
Or you just use the date, right, mid, and left worksheet functions
directly in your VBA code to calculate the date, assign it to a
variable, then over-write the cell with the date reference. This might
be the best way to go anyway, as doing something like:
Dim MyDate as String
MyDate = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
will read the date as a text string and make parsing it into
day/month/year a lot easier. I seem to remember a VBA
method/function/property (something anyway) which will read a string
into a user-specified date format. I can't find it at the moment, but
I'll keep looking in MSDN and let you know. I think it may be in VB.NET
rather than VBA????

BTW, does anyone out there know how to FORCE pasted data into the
previously set format of the destination cell? Is it as simple as
"paste special/values only"???

Regards,
Graeme

 

[Graeme Whelan]

On another note, do the buttons and date-entry fields you use when doing
this manually have specific names which might be accessed though a java
method?? That way you could use a web query with a date range defined
by code. This might be easier if you can step through a downloaded copy
of the web page a see what format it likes for the post-data you're
sending when using the date fields and buttons on the page normally. Of
course if it's well written all the data retrival happens on the server
and then you're screwed unless you can match the post-data format and
syntax EXACTLY.

Regards,
Graeme

 

[Geoff Kelly]

I have found a kludge solution - by using
application.sendkeys "%EP"
with the target being the active cell in the active sheet (activated the
required sheet first)
This handles the paste as if done from the keyboard.
Interestingly,
application.sendkeys "^V"
doesn't work! So much for Microsoft documentation that says you can use ^ as
a prefix to mean CTRL..
Geoff