Convert Excel table to HTML table 2

[AndyGroom]

I need some code to take a selection from an Excel spreadsheet that resides on Clipboard and convert it into an HTML table. I can probably make a fist of coding it myself but I wondered if anyone had already got any code they might feel like posting?


- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[TipGiver]

If you pasted from clipboard to a text editor (NOTEPAD and NOT WORDPAD) you would see that each element from the COLUMNS is delimited by a single TAB, whereas the NEW LINE by ENTER.
I assume that you know the tags to create a table (easy to see from frontpage). <td> and <tr> should REPLACE the TABs and ENTERs.


Hope this hint help you.

 

[AndyGroom]

Thanks. I was actually going for a more realistic conversion, so that the column widths, cell colours, fonts etc looked the same (since a HTML table will support these attributes). I guess I should have explained that.

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[CassandraR]

Hi Andy:

In Excel 2000, there is the option of saving the file as a Web Page (HTML) file. Granted that Excel, like other HTML-aware MS software, spews a lot of extraneous tags, but it could be a starting place to convert an Excel sheet to an HTML file.


Cassie
PIII-500Mhz-128MB
Win98SE-VB6SP5

 

[strongm]

Easiest way in VB is to add a DHTML Edit Control to your components, drop a DHTMLEdit or DHTMLSafe, then something lie th following:

[blue]Private Sub Command2_Click()
    DHTMLSafe1.ExecCommand DECMD_PASTE
    Debug.Print DHTMLSafe1.DOM.body.getElementsByTagName("TABLE")(0).outerHTML
End Sub[/blue]

 

[AndyGroom]

Do you know if the DHTML components will be present on a machine (assuming IE is installed)? If so then I can hopefully use this method, it'll save a lot of programming.

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[AndyGroom]

...also, when trying your code I get "Permission denied" on the first line. Do I need to set properties for the control prior to pasting?

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[strongm]

Quickest answer would be to use the DHTMLEdit control rather than the DHTMLSafe control

 

[strongm]

(oh, and whilst Microsoft don't seem to advertise the fact any more, the control should be on any Windows box with IE5 or better installed, so you've certainly got it under Me, W2K, XP, 2K3)

 

[CassandraR]

Hi strongm:

Thanks for the tip on using the DHTML controls.

A star for you.


Cassie
PIII-500Mhz-128MB
Win98SE-VB6SP5

 

[AndyGroom]

If I use a DHTMLEdit instead I get this error:

"Method ExecCommand of object IDHTMLEdit failed"

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[AndyGroom]

I have got a little further but still no cigar. I copy an area from an Excel spreadsheet onto clipboard and then run this code:

DHTMLEdit1.DocumentHTML = ""
DHTMLEdit1.ExecCommand DECMD_PASTE
WebBrowser1.Document.Write DHTMLEdit1.DOM.body.outerHTML

but I now get "Method DOM of object IDHTMLEdit failed". I'm going to download the SDK from MSDN and read up on it, unless you can post a quick fix...

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[strongm]

Ah - now that reminds me that there was a security patch that could cause that error message. Hang on a sec ... <tappety tappety> ... yes, here we are (and Anthonly Muller's post suggest one workaround):

http://groups.google.com/group/micr...read/thread/c4953436aaa87d77/b2b6908f0036fb5f

 

[AndyGroom]

Thanks for that.

Actually your original post put me on the right track. The solution seems to be to do:

DHTMLEdit1.DocumentHTML = ""

within the Form_Load event. Then when you do the subsequent paste it seems to work fine. It obviously needs a moment to initialise itself.

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments