Copy table from website into excel with VBA

[mcclellan]

Hello there,

I love this website.

I am trying to create a macro that will copy a table from a website and paste it into excel.

How do I do this and how do I identify the table name?

Any help is appreciated.

Michael

 

[DocAElstein]

……
I'm just an average punter. Leiths level of knowledge and understanding is second to none
These sites may help
https://developer.mozilla.org/en-US/docs/Web/API/Node.textContent
Methods (Internet Explorer)
document Object (CUSTOM, window)
……..

. Thanks pike for the reply. It was helpful. – Those are the sort of links I was looking for but was unable to find as I lacked the experience and terminology Knowledge to enter the right search criteria.
. (And clearly your code and the discussions and comments made by Leith on it have helped to contribute to a very good and informative Thread. – So thanks again for you input.)
Alan

 

[DocAElstein]

Hi Leith,
. I will be studying and learning from your Post #19 well into the new year…….
. But I just wanted to reply and say Thanks now and give a bit of Feedback……



1a. ) I think, or rather I know, that by a stupid typo I distracted from the actual comparison I wanted to make..I meant to say Input Rather than Output, and qualify my comparison further with comparing this

    Request.Open "GET", URL, [color=darkblue]True[/color]
    Request.send
    PageSrc = Request.responseText
[color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

With something like this:

[color=darkblue]Dim[/color] FileNum [color=darkblue]As[/color] Long: FileNum = FreeFile [color=green]'Give next "Highway" Number Free for data[/color]
[color=darkblue]Dim[/color] PathAndFileName [color=darkblue]As[/color] String: [color=darkblue]Let[/color] PathAndFileName = absURI [color=green]'I am taking a Url string as an equivalent to a File Path to a somle Text File: Probably a (very stupid) fundamental mistake[/color]
[color=darkblue]Open[/color] PathAndFileName [color=darkblue]For[/color] [color=darkblue]Input[/color] [color=darkblue]As[/color] #FileNum [color=green]' Open allocates a buffer in a general Format that VBA can work with[/color]
  [color=darkblue]If[/color] PathAndFileName = [color=darkblue]False[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
  [color=darkblue]Dim[/color] PageSrcAlan [color=darkblue]As[/color] String:
  [color=darkblue]Get[/color] #FileNum, , PageSrcAlan [color=green]'Put the whole long string in PageSrcAlan string variable[/color]
[color=darkblue]Close[/color] #FileNum [color=green]'Always good practice to close / shut everything off once finished[/color]

Or this:

[color=darkblue]Dim[/color] FileNum [color=darkblue]As[/color] Long: FileNum = FreeFile [color=green]'Give next "Highway" Number Free for data[/color]
[color=darkblue]Dim[/color] PathAndFileName [color=darkblue]As[/color] String: [color=darkblue]Let[/color] PathAndFileName = absURI [color=green]'I am taking a Url string as an equivalent to a File Path to a somle Text File: Probably a (very stupid) fundamental mistake[/color]
[color=darkblue]Open[/color] PathAndFileName [color=darkblue]For[/color] [color=darkblue]Binary[/color] [color=darkblue]As[/color] #FileNum [color=green]' Open allocates a buffer, Binary means  a very fundamental format, a long stream.[/color]
  [color=darkblue]If[/color] PathAndFileName = [color=darkblue]False[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
  [color=darkblue]Dim[/color] PageSrcAlan [color=darkblue]As[/color] String: [color=darkblue]Let[/color] PageSrcAlan = Space(LOF(FileNum)) [color=green]'Binary format would said to be unformatted in comparison with other formats. A requirement then is that the accepting string is exactly the same length[/color]
  [color=darkblue]Get[/color] #FileNum, , PageSrcAlan [color=green]'Put the whole long string in PageSrcAlan string variable[/color]
[color=darkblue]Close[/color] #FileNum [color=green]'Always good practice to close / shut everything off once finished[/color]

…………………….But from your answer to 1a) and 2a) I see the naivety of wot I was saying. Clearly now I see my codes are just inputting text data. The first prepares it in a form VBA recognizes and can analyze and read, for example, line by line. The Second retrieves a long string form and I was naively thinking that could be compared with your pageSouce String. But I see now Request is an Object which you use to request from you to the server through .Open and send by .send, (and through argument (flag) True the code will only go further having got a response). A whole lot of info is then obtained. - Amongst other stuff, as you say .responseText returns the particular string that is required for making the instance of the HTML Document Object required.
….. I think I may have eventually have got this far alone…. But without you help It may have been a week or two or five before the Penny dropped.

. 2a) I have studied very carefully (**not a simple task so I may have missed something!!:..), Your HTML document Object and Pikes. They are, (I think**) identical. But I think I understand your point that you simply prefer the write method as it was intended to be used for this purpose compared with the property used by pike which in this case produces the same result

. 2b). I need to let my printer cool down a bit after printing out many inner / Outer HTMLs and Inner / Outer Text files, source codes, etc. etc….. Clearly Page Source and HTML source Code are not quite the same thing. I think I am for now happy with the explanation that PageSource is the format required to produce a very big Object with which just about everything, (including probably the HTML source code?) can be obtained from through some Method or property of that Object…?

. 3)

…

.. This is a case where late binding has to be used. The htmlfile is a……..This gets into a lot of low level system operation that I will not discuss here.
….

I think that is a good, appropriate answer for me (for now). - It confirmed my results and points in the direction of the exact reasons, which as you rightly suggest tackles, an area much too big to discuss here. Important for me is that I understand now that there is a good reason why Early Binding does not work here.

. 4)

……. I mean no disrespect to Pike..

… I am sure there is none taken.. I think we are both very grateful for you giving us the benefit of your knowledge which as pike said..

……….
….. …
But Leiths explanation of how and why is worth a million ….

. As to Your explanation of where you obtained the original color etc. I see now why I could not find it. – There is a reference to where it is in the Object but it is not in the Object itself..

. I do not quite understand one thing you said – (As the HTML document Objects from You and pike appear to me to be identical):

… This is reference is included in the HTML document area Pike excluded. ……...

Possibly I am in error and there were small differences I overlooked** ?- It was very difficult to see clearly in the watch window the vast amount of info in those objects.!!.

…….. Because of this lack of enforced rules, I have learned over time not to get to comfortable with "short" code. It will come back and bite you. It is better to take the extra time and do it right the first time than have to return and do it again. Defining object variables makes the code more flexible and more easily adaptable to future changes……..

. My opinion as before

…… for me there is no “right” way of doing it .

.

. In the RL if a “code is short and does work“, it may be appropriate… But I have already had things “coming back and biting me…”
http://www.mrexcel.com/forum/excel-questions/817446-range%3D-equivalent-range-value%3D-sometimes-range-range-value-anomaly.html?&&
http://www.mrexcel.com/forum/excel-questions/817965-dimension-array-range-anomaly-2.html
… so certainly for now, if not for as long as I am able I will Let myself try always to be as explicit as possible and not “use implicit defaults”


7.) I will need to sleep on your explanation there a bit I think. And maybe go through pikes reference and regoogle a bit for “Childs and things..” But I think I will get there.

Thanks again for your very generous help. It is much appreciated
Best wishes for the New Year.

Alan.

 

[SureShotUK]

Pike, I have been looking for exactly this, it has taken a long time to find but this answer is brilliant. Just wanted to say thanks.