Solved

VBA to Copy a Table from IE and Paste into Excel

Posted on 2013-12-04
6
2,714 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

707 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

14 Experts available now in Live!

Get 1:1 Help Now