I ran into some strangeness while copying tables from the web to Excel. To demonstrate, I’ll use Debra’s sample data table. I run this code
Dim ieApp As Object
Dim ieDoc As Object
Dim Clip As DataObject
Set ieApp = CreateObject(“InternetExplorer.Application”)
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
Set ieDoc = .document
Set Clip = New DataObject
Clip.SetText “<html>” & ieDoc.all.tags(“TABLE”).Item(3).outerhtml & “</html>”
Sheet1.PasteSpecial “Unicode Text”
That code automates Internet Explorer, navigates to the page, gets the table, puts it in the clipboard, and pastes it to a cell.
Nice. I delete the data and merge A1:C1
I run the code again and get this
Not so nice. I fixed it by adding this line to the code
That was easy enough. The problem was that I didn’t know it was merged cells causing the problem. I had some tables coming in fine and other were truncated at seemingly random spots. Once I discovered that some of the cells were merged, I deleted the rows and it ran fine. Then later, it would screw up again. Finally I figured out that the HTML table had some colspan attributes in some of the td tags. The colspan attribute, when pasted into Excel, creates merged cells. Now you know.
And if you’re wondering, the tables in question were in a website that required a log in, so I couldn’t use a web query. By automating IE, I could navigate to the login page, fill in the form, then navigate to the page with the tables I wanted. I’ll post about that as soon as I work out a few bugs.