Copy data from Web to excel using VBA

Question

I have a webpage and need my code to copy the entire data from the page and copy it into the excel sheet, which is not happening right now. My excel sheet is coming to be completely blank. I think the ^a feature is not working on the IE to select the data and then copy it.

Any help is much appreciated. Below is the code I am using.

Sub Webdata()

    Dim assetname As String, country As String, area As String, region As String, pth As String, folname As Variant, assetname1 As String

    Website = "http://website.com/"
    Set myIE = CreateObject("InternetExplorer.Application")
    myIE.Navigate source
    myIE.Visible = True
    Application.Wait Now + TimeSerial(0, 0, 10)
    SendKeys "^a"
    Application.Wait Now + TimeSerial(0, 0, 2)
    SendKeys "^c"
    Application.Wait Now + TimeSerial(0, 0, 2)
    Sheets.Add
    ActiveSheet.Name = "Webdata"
    ActiveSheet.Paste
    Application.Wait Now + TimeSerial(0, 0, 2)

    Range("A1").Select
    Cells.Find(What:="Api Number", After:=ActiveCell, LookIn:= _
               xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
               xlNext, MatchCase:=False, SearchFormat:=False).Activate

    ActiveCell.Offset(1, 0).Select
    Selection.Copy
    Sheets("Sheet1").Activate
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                           :=False, Transpose:=False
    Application.CutCopyMode = False
    myIE.Quit

    Set myIE = Nothing
    Err.Clear
    Sheets("Webdata").Select
    ActiveSheet.Delete

End Sub

Yes, there is a way. What did you research and what did you try so far? Show what you did already in your question and tell us what exactly your difficulty is and where you got stuck or errors. There are several question here on Stack Overflow answering exactly this kind of question. — Pᴇʜ, Jul 30 '18 at 12:48
Thanks a lot for your response. I have the below query for this but somehow this is not working for me. — Nishant, Jul 30 '18 at 12:57
Please add everything that belongs to your question into the original question above by [edit]ing it. Don't use comments to ask something. Also explain *what exactly* is not working. Because *"not working"* doesn't tell us anything about your issue. — Pᴇʜ, Jul 30 '18 at 13:00
Sub Webdata() Website = "http://pipeline.wyo.gov/Wellapi.cfm?oops=ID85545&nAPINO=" & source Set myIE = CreateObject("InternetExplorer.Application") myIE.Navigate source myIE.Visible = True Application.Wait Now + TimeSerial(0, 0, 10) SendKeys "^a" Application.Wait Now + TimeSerial(0, 0, 2) SendKeys "^c" Sheets.Add ActiveSheet.Name = "Webdata" ActiveSheet.Paste Application.Wait Now + TimeSerial(0, 0, 2) Range("A1").Select Cells.Find(What:="Api Number").Activate myIE.Quit Set myIE = Nothing Err.Clear Sheets("Webdata").Select ActiveSheet.Delete End Sub — Nishant, Jul 30 '18 at 13:02
As I said: [Edit] your question and add it there. Format it as code block (4 space indentation, or use ctrl + K to indent it). Then remove the comments with the code. — Pᴇʜ, Jul 30 '18 at 13:04
Thanks I have edited my original comment and placed the code as well... — Nishant, Jul 30 '18 at 13:09
Well, but you still didn't ask a question nor did you tell what's wrong with your code or what your expected outcome is. What Errors do you get or what happens instead of what you expected? You might also want to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to make your code stable. — Pᴇʜ, Jul 30 '18 at 13:14
Hi, I have updated my requirement, can you please check once. Thanks — Nishant, Jul 30 '18 at 13:33
@Nishant Are you sure those ID-s in the URL are safe to be published to the world? — xxbbcc, Jul 30 '18 at 18:06

QHarr · Accepted Answer · 2018-07-30T21:08:08.483

1

That table is a mess so rather than spending time perfecting how to write out the table to the sheet in the way I normally would i.e. looping rows of tables and table cells within rows, I will stick with your idea of copying the table but use the clipboard, with .SetText, rather than SendKeys. The table of interest is within nested frames so you have to negotiate those first.

Set hTable = .frames(2).document.getElementsByTagName("table")(0)

Code:

Option Explicit
Public Sub GetInfo()
    Dim IE As New InternetExplorer, html As HTMLDocument, hTable As HTMLTable, clipboard As Object
    Application.ScreenUpdating = False
    With IE
        .Visible = True
        .navigate "http://pipeline.wyo.gov/Wellapi.cfm?oops=IDxxxxx&nAPINO=xxxxxx" '<==Input your personal URL here 
        While .Busy Or .readyState < 4: DoEvents: Wend
        Set html = .document
        With html
            Set hTable = .frames(2).document.getElementsByTagName("table")(0)
            Set clipboard = New MSForms.DataObject
            clipboard.SetText hTable.outerHTML
            clipboard.PutInClipboard
            ActiveSheet.Cells(1, 1).PasteSpecial
        End With
        .Quit
    End With
    Application.ScreenUpdating = True
End Sub

References:

VBE> Tools > References:

Microsoft Forms 2.0 Object Library
HTML Object Library
Internet Explorer Controls

edited Jul 30 '18 at 21:08

answered Jul 30 '18 at 18:17

QHarr

83,427
12
54
101

Thanks a lot QHarr for heloing me out, however I am getting compile error - user defined type not defined. Please excuse my very limited knowledge on VBA. – Nishant Jul 31 '18 at 06:08
Now I am getting compile error for Set clipboard = New MSForms.DataObject – Nishant Jul 31 '18 at 06:39
Error is Compile Error "User-defined type not defined" and New MSForms.DataObject is highlighted – Nishant Jul 31 '18 at 06:50
1

Ohh yes, this is working now....Thank you so much QHarr...It is really very helpful...Thanks again for your kind efforts. – Nishant Jul 31 '18 at 07:14
Hi QHarr, when I am trying to loop this, I am getting an error - Run time error 462 The remote server machine does not exist or is unavailable. Please advise how to proceed. – Nishant Jul 31 '18 at 08:55
That error would normally be if internet connection lost/application closed. – QHarr Jul 31 '18 at 08:56
This is still working for first iteration, but when I add iterations for a loop it stops at the second one. – Nishant Jul 31 '18 at 09:00
I just added the loop and to export data from multiple pages. – Nishant Jul 31 '18 at 09:04
I can't really debug without the links. .Quit is telling Internet Explorer to close. In your loop do you see IE being opened again? You want to write your loop so that is loops with IE open and for the loop to finish before the .Quit. – QHarr Jul 31 '18 at 09:06
1

Ohh yes....thanks a lot again and again and again...It works wonderfully now. You are brilliant. – Nishant Jul 31 '18 at 09:15
I'm using a similar script and get a `Run Time Error 438` at the `clipboard.SetText hTable.outerHTML` step. – Gitty Jun 16 '20 at 18:37
Have you instantiated clipboard and is hTable not nothing? – QHarr Jun 16 '20 at 18:38
@QHarr `If Not ieTable Is Nothing Then` next line `Set clip = New DataObject` next line `clip.SetText ieTable.outerHTML` – Gitty Jun 16 '20 at 18:49
try late bound like here: https://stackoverflow.com/a/60906400/6241235 – QHarr Jun 16 '20 at 21:30

Copy data from Web to excel using VBA

1 Answers1