Need help extracting data from web page source into excel using RegEx and macro

[chococ]

Hi all,

I am new to VBA and hoping this is possible to be done with excel VBA. I want to extract part of page source from a web page and parse that into a spreadsheet using regular expressions within VBA, but I am not sure if VBA coding supports RegEx. I am using excel 2007.

Overview of task I want to do:
Extract line 82 from page source from view-source:http://giffgaff.com/index/international
and arrange the data into a spreadsheet in following format where each comma denotes separate column, with table starting from cell A3 on "Sheet2":
af,landline,18/100,mobile,18/100,text,8/100,mms,24/100
ak,landline,3/100,mobile,3/100,text,8/100,mms,24/100
al,landline,10/100,mobile,25/100,text,8/100,mms,24/100
[*code*,landline,*price*,mobile,*price*,text,*price*,mms,*price*] etc etc

What I have done so far:
I have a macro in Notepad++ that would "clean" the line 82 into above format using regular expressions and one in word that would "clean" using multiple find-and-replace(see additional information below), then I can save as .txt and import into excel. Problem is I need a one-click solution since I need to do this many times for many websites. So I am hoping to incorporate the macro from Notepad++ into excel so excel can do the "cleaning" and splitting of lines and columns. Alternative if excel can control extraction and run the macro then save and import into specific location it is also fine.

I have found a lot of replace-using-RegEx tutorial around but they only concern one \1, and I have 9 instead of 1. (see 3rd replacement in additional information below)

Ultimately I need to replace the *code* with country names too but I have already parsed the list of options using a modified code from this post: http://www.excelforum.com/excel-prog...urce-code.html I am just planning to add a vlookup into final macro, if there isn't any quicker way.

Any help would be greatly appreciated, please let me know if there is any other information I could provide with.

Thank you very much for your help in advance.


Additional information:

Replacements I did in Notepad++, using RegEx:
1.
Find:

Please Login or Register  to view this content.

Replace:

Please Login or Register  to view this content.

2.
Find:

Please Login or Register  to view this content.

Replace:

Please Login or Register  to view this content.

3.
Find:

Please Login or Register  to view this content.

Replace:

Please Login or Register  to view this content.

Notepad++ macro generated from recording:

Please Login or Register  to view this content.

Word macro:

Please Login or Register  to view this content.

[Leith Ross]

Hello chococ,

Add a new VBA module to your workbook and paste the code below into it. Run the macro GetPrices.

Please Login or Register  to view this content.

[chococ]

Hi Leith Ross,

Thanks SO much!! It worked perfectly. Just another favour to ask... I used a code you wrote in another post years ago (the one I mentioned in my thread, LINK HERE) to extract the values between html tags and gave it a little modification, so that it can look up 2 sets of tags from each url (I only have 2 so far)and put them into designated cells. At the moment it works but I am just wondering if there could be a less clumsy way to do it, I have tried removing repeating lines but they would return an error...

At the moment the code looks like this, modified from post #4 in thread mentioned:

Please Login or Register  to view this content.

It does do the job, I am just wondering if there is another way that's less cluttered. Any help much appreciated!

[ryanusingexcel]

Hello,
You two seem to be doing something similar to what I am trying to accomplish. I would like to enter an address into excel, have the address populate in this webpage: http://www.ffiec.gov/geocode/, then I would need to extract three pieces of data from the output page. Example with address: http://www.ffiec.gov/geocode/Geocode...rf78lMIIJLei4G
So I would need to extract the full census tract number which is the "State Code"+"County Code"+"Tract Code" in this case '12095018800'.

I'm not really sure where to start here, I'm fairly new to excel macros, but have a background in basic webpage coding and VB coding.

Any help is appreciated!

-Ryan