Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HTML Paste (95+)

    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?

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

    Re: HTML Paste (95+)

    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)?

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Paste (95+)

    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?

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

    Re: HTML Paste (95+)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Paste (95+)

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

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: HTML Paste (95+)

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

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Paste (95+)

    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.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Paste (95+)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Paste (95+)

    Thanks shall test on Tuesday. WIll reply then

Posting Permissions

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