bromy2004
asked on
Get Table from website into Excel VBA
Hi,
I work for a Stationery company and we constantly need to find cartridges for Printers with a Printer Supplied
I'm trying to get data from a website into Excel.
The Website is http://www.dynamicsupplies.com.au
the search URL is http://www.dynamicsupplies.com.au/search.php?Vendor2=Search+All+Manufacturers&q=%CODE%&Submit=Search
replacing %CODE% with a search string from Excel
I know a fair bit about Excel and VBA but i'm hopeless when it comes to HTML and the Code tags.
I've attached a before and after of what i would like
I have Chrome and (obviously) internet Explorer installed.
can anyone point me in the right direction?
EE---Dynamics-Search.xls
I work for a Stationery company and we constantly need to find cartridges for Printers with a Printer Supplied
I'm trying to get data from a website into Excel.
The Website is http://www.dynamicsupplies.com.au
the search URL is http://www.dynamicsupplies.com.au/search.php?Vendor2=Search+All+Manufacturers&q=%CODE%&Submit=Search
replacing %CODE% with a search string from Excel
I know a fair bit about Excel and VBA but i'm hopeless when it comes to HTML and the Code tags.
I've attached a before and after of what i would like
I have Chrome and (obviously) internet Explorer installed.
can anyone point me in the right direction?
EE---Dynamics-Search.xls
ASKER
I would normally do that,
thats how i got the "Results"
however the list is generally 10-50 Printers, and searching for each link and returning the table is time consuming
so i was hoping a macro would do it.
thats how i got the "Results"
however the list is generally 10-50 Printers, and searching for each link and returning the table is time consuming
so i was hoping a macro would do it.
I'm working on it ... it's a litte tricky as it requires regex parsing on an automated explorer html string
Cheers
Dave
Cheers
Dave
ASKER
thank you dave,
although that last bit flew straight over my head. :s
although that last bit flew straight over my head. :s
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've run in on my Workbook as well as your supplied workbook, but the TextToColumns isn't working
How does the RegExp work?
I noticed you've set the "Pattern" twice.
ScreenShot002.jpg
How does the RegExp work?
I noticed you've set the "Pattern" twice.
ScreenShot002.jpg
Thats strange, it should just be splitting the column on the "|" data as my sample file did. I presume it works manually?
I retested my file and it worked fine
I use the first pattern to strip out all line breaks so that I have a single string to parse, the second pattern looks for strings contained in between the </TD> and </TD>
Cheers
Dave
I retested my file and it worked fine
I use the first pattern to strip out all line breaks so that I have a single string to parse, the second pattern looks for strings contained in between the </TD> and </TD>
Cheers
Dave
ASKER
Figured it out,
the TextToColumns was missing Other:=True
Have added and works fine, except for the last line, which remains as a long string
the TextToColumns was missing Other:=True
Have added and works fine, except for the last line, which remains as a long string
Yep, that was the 2% part I figured would be your direction bit.
I'm out of time to tweak that part further :)
Dave
I'm out of time to tweak that part further :)
Dave
After looking at u guys, im seriously thinking of learning VB!!
But for now, this is from my side (i guess this is going to be the crudest code ever!!)
:P
Tils.
But for now, this is from my side (i guess this is going to be the crudest code ever!!)
:P
Tils.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tils,
That is really good, and clear enough to understand.
I'll use that one with another macro i'm using.
That is really good, and clear enough to understand.
I'll use that one with another macro i'm using.
ASKER
Well done and Thank you to Dave and Tils
k....... kool :)
Thanks for the Points, Broomy =)
Tils.
Tils.
Hope that helps!