?
Solved

VBA to Copy a Table from IE and Paste into Excel

Posted on 2013-12-04
6
Medium Priority
?
3,412 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And 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 2000 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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Progress
Introduction to Processes
Suggested Courses

743 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