Solved

VBA to Copy a Table from IE and Paste into Excel

Posted on 2013-12-04
6
3,066 Views
Last Modified: 2013-12-06
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!

Thanks!


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
    dat.PutInClipboard

    End With

    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:= _
        False

End Sub

Open in new window

0
Comment
Question by:ashleyna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39696988
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).
0
 

Author Comment

by:ashleyna
ID: 39697041
I tried that - now instead of posting in separate rows, all of the data is dumped into cell A1.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39697377
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)
   Next
Next

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ashleyna
ID: 39700920
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.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 39700961
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>?
0
 

Author Comment

by:ashleyna
ID: 39700990
Actually using innerHTML worked! That put everything into separate columns and rows. Thanks for your help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question