Solved

VBA to Copy a Table from IE and Paste into Excel

Posted on 2013-12-04
6
2,841 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
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.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now