• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4182
  • Last Modified:

VBA to Copy a Table from IE and Paste into Excel

Hi all!

I am having some trouble copying a table from IE and pasting it into excel. The following code works to download the data, but it concatenates all the columns together.

For example,
Column1: Ticker
Column 2: CompanyName
Column 3: 04562E207

turns into one long string in excel: TickerCompanyName04562E207

Does anybody know how to copy the table while preserving the formatting of a table?

Any help is much appreciated!


Private Sub IE_Navigate_and_Download()
Application.StatusBar = "Opening IE"
Dim IE As InternetExplorerMedium
Dim dat As DataObject
Dim Htable As IHTMLElementCollection
Dim maTable As IHTMLTable

    Set dat = New DataObject
    Set IE = New InternetExplorerMedium
    URL = "Internal Website"

    With IE
        .Visible = False
        .Navigate URL

        While .Busy Or .ReadyState <> 4: DoEvents: Wend

        Set Htable = IE.Document.getElementsByName("td")
        Set maTable = Htable(0)

    dat.SetText IE.Document.DocumentElement.innerText

    End With

    ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:= _

End Sub

Open in new window

  • 3
  • 3
1 Solution
First, I see no good reason to use the clipboard. Forget the whole DataObject. Just use a string.

Dim dat As String
dat = IE.Document.DocumentElement.innerText
Range("A1").Text = dat

See what that gets you.

The clipboard does some funny things (and using it also may interfere with the user if the user has something copied).
ashleynaAuthor Commented:
I tried that - now instead of posting in separate rows, all of the data is dumped into cell A1.
I see. What does the data look like? If you format A1 to be multiline, is each row on its own line in the cell? What is separating the columns? Tab characters? Commas?

Then you can split the text based on that and put it in your sheet.

Here is an example for newline and commas
Dim table_text As Variant
Dim row_text As Variant
table_text = Split(dat, Chr(10)&Chr(13)) 'Chr(10) is CR Chr(13) is LF (Windows newline)
For i = 1 To Ubound(table_text )
   row_text = Split(table_text, ",") ' For tab use Chr(9)
   For j = 1 To Ubound(table_text(i))
      Cells(i,j).Text = row_text(j)

Open in new window

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

ashleynaAuthor Commented:
Unfortunately, there are no separators. However, data is on separate lines within the cell. Here is how the data comes out:

1105234ABC CompanyB72ZK78USMining333 10075-2510 - -5.40%--0.05%
4590123789SmithBKL456TaiwanSemiconductor156 10000-1234 - -3.01%--0.01%

The spaces in the above only show up when the original data had spaces (e.g. ABC Company had a space in the original data).

Is there a way of simply copying the entire page of IE? I know I could do it using SendKeys, but I really don't want to do it that way.
I agree that SendKeys is a bad idea.

The output you show makes sense and explains why it pasted the way it did in your original code.

What does the page look like? Is it an html table?
I'm guessing you are losing the column separation with DocumentElement.innerText.
Try printing out the contents of DocumentElement.innerHTML. I'll bet you see something separating them then. Maybe </td><td>?
ashleynaAuthor Commented:
Actually using innerHTML worked! That put everything into separate columns and rows. Thanks for your help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now