VBA Macro Web Query Infinite Loop

Hello everyone,


I'm trying to build my first VBA in Excel for my bachelor thesis and it's not yet working out as I hoped.

I have a list in a column with 5207 company names (listed on London Stock Exchange) of which I need the TIMD (this is just an abbreviation of the company names of 2 to 4 letters that the London Stock Exchange uses).

My aim is to build a VBA with a web query that goes to the page of the LSE and copies the first company name of my column in a certain search mask on their site and copies the result (when you enter the "long company name" they give you the TIDM) back in Excel. After the first company name, the procedure should be repeted automatically for all the 5207 company names.

- My first problem is that when I copy the first cell with a company name in it by using Ctrl+c and go to "Data" -> "Get external Data" -> "Out of the Web" (the names of the excel commands may differ in my German version) and now have the LSE Website open, I cannot paste the company name with Ctrl+v. Unfortunately you can't copy the content of cells (even though it's only text) with Ctrl+c and have it then in the Web Query Broswer. Does anyone know how to resolve this problem?
Instead of using Ctrl+c, I also tried to copy the company names by using F2 -> then mark the text by using the mouse -> then press Ctrl+c -> Esc to get out of the cell -> then copy it in the Excel Web Broswer by using Ctrl+v. This works. But will it work when it comes to teach VBA to do this not only for one cell, but for 5207 cells? The company names in the cells have different lenghts, so VBA might maybe only copy the number of letters one taught him (e.g. the company that serves as a modell for VBA has only 7 letters, but others will have more, but for those, only 7 letters will only copied instead of the hole name).

- When the first problem is solved, I would like VBA to copy the information of the website for one company after the other one below the other (I want to avoid overwriting). It should copy the information for the next company e.g. 8 ligns below the point where the one before was pasted in. Does someone know how this works?

- And of course, I also need an infinite loop for the 5207 search procedures. After having registerd the procedure for the first company, I went into the VBA Editor and added at the top "For i = 1 To 5207" and and the bottom "Next i". It this correct so far? Does it know where to find the next i?


I attached my Excel file to my thread. In the Excel file I registerd the macro in which I did the procedure for the fist company of the column (As I said before, I copied the company name "manually" by using F2 -> using the mouse to mark it -> Ctrl+c -> Esc. This will probably not work out when it comes to the automatisation). I removed my infinite loop in this version.

I also attached a screenshot of the Web Query Browser. It shows which part of the website I need. I set the checkmark only there in my Web Query, as I don't need the rest.



I would be really grateful if someone had an idea and could explain me what to do so that I can learn it.

Thank you very much in advance!


Greetings from Munich,
Simon

There are 2 codes on the attached, you can pick up which ever suits you. The code takes ages as it goes down to each cell- over 5k rows plus the constant refresing of tables.

Thank you for your answers!!

The query worked quite well, unfortunately it couldn't get all the TIDM because when a company delists itself, changes its name or merges, it gets a new TIDM or does not have a TIDM any more.

But at least I got a bit more than 1000 already.

The problem with the List of all securites excluding Debt is the same, it's not possible to find the companies in this file in case of delisting, merger etc.


Thanks again a lot for your help!!


Greetings,
Simon