HTML Paste (95+)

Topic

New Reply

We are trying to automatically paste html table into excel from a web page.
The html table is automatically created and we assign to the clipboard. This works fine.

If we try and paste from within javascript to excel: ActiveSHeet.Paste then the dates are formatted incorrectly. for instance 01/02/2003, 1st Feb is pasted as the 2nd Jan. While dates with days greater than 12 are pasted correctly though are text not dates.

WHat should we do?

Reply | Quote

Replies

It interprets dates as if they are in US format (mm/dd/yyyy) instead of UK format (dd/mm/yyyy). Can you create the HTML table with US format dates? Or else switch the PC temporarily to US format (Regional Settings)?

Reply | Quote

Hans,

This could cause a problem when pushing the application to the world. Some countries will get confused if the date is in MMDDYY format and not their standard. Any other suggestions?

Reply | Quote

I don’t know JavaScript at all, so I don’t know how it interacts with Excel, but I imagine you would have to loop through the cells of the table, get the text of each, convert it to the correct format and then put it into Excel. A lot more work than simply copying and pasting… But perhaps somebody who knows JavaScript has a better suggestion.

Reply | Quote

Would it work to change the date format to display Feb 1, 2003?

Reply | Quote

Why pasting? Is that the only way to get the info off the web page?

Cheers

Reply | Quote

Pasting is the easiest wayto get the information off of the web page as it is already held in a html table. The paste works fine if done manually it’s just automatically. Thought about changing the date to have full month, though this could cause a problem in countries like France where months have different names. The html data is initially text, then excel realises that the information contained is a date. Formatting is an option though takes away from the gadget being generic, and we will have to write a gadget for each extract to excel.

Reply | Quote

for XL97 and up, use:

Application.CommandBars(1).FindControl(Id:=22, recursive:=True).Execute

It’ll make Excel believe you used the user interface to do the paste.

Reply | Quote

Thanks shall test on Tuesday. WIll reply then

Reply | Quote