Link to home
Start Free TrialLog in
Avatar of bromy2004
bromy2004Flag for Australia

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
Avatar of proadmin
proadmin
Flag of United States of America image

Believe it or not Excel is really good taking tables from HTML pages - try copy and pasting into your XLS file - if that isn't good then try copy and pasting into a Wordpad file and then into Excel from there.

Hope that helps!
Avatar of bromy2004

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.
I'm working on it  ... it's a litte tricky as it requires regex parsing on an automated explorer html string

Cheers

Dave


thank you dave,
although that last bit flew straight over my head. :s
ASKER CERTIFIED SOLUTION
Avatar of Dave
Dave
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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
Yep, that was the 2% part I figured would be your direction bit.

 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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Tils,
That is really good, and clear enough to understand.

I'll use that one with another macro i'm using.
Well done and Thank you to Dave and Tils
k....... kool :)
Thanks for the Points, Broomy =)


Tils.