Sign in to follow this  
Followers 0
AutoAsh

Extracting data from website into Excel

1 post in this topic

Hi,

i'm trying to learn some VBA and get to grips with AutoIt, but in the meantime i have a need to perform some data extraction via a macro and don't know how to do it.

i have a long list of part numbers in an Excel worksheet and i have to punch those into the search box on a particular site (in this case Viking Direct's) and then copy the results of the pricing details and paste them into the appropriate cells in Excel.

i've tried recording with both windows (excel and ie) open and the code produced just about does what it's supposed to, but of course it only does it the once, rather than going all through the list until it finds an empty cell (or the end of the list).

So, my questions is this;

Now that i have a clunky but working macro, how can i get it to repeat the cycle and go through each and every part number (found in cells A1,A2,A3,A4..etc) and put the results into the appropriate rows/columns?

Not sure if it'll help or confuse...but here's the script the recording has produced;

Opt("WinWaitDelay",100)
Opt("WinTitleMatchMode",4)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
WinWait("Microsoft Excel - Book1","Worksheet Menu Bar")
If Not WinActive("Microsoft Excel - Book1","Worksheet Menu Bar") Then WinActivate("Microsoft Excel - Book1","Worksheet Menu Bar")
WinWaitActive("Microsoft Excel - Book1","Worksheet Menu Bar")
MouseMove(16,182)
MouseDown("left")
MouseUp("left")
MouseMove(25,183)
MouseDown("left")
MouseUp("left")
MouseDown("right")
MouseUp("right")
MouseMove(83,289)
MouseDown("left")
MouseUp("left")
MouseMove(62,167)
MouseDown("left")
MouseUp("left")
MouseMove(64,168)
MouseDown("right")
MouseUp("right")
MouseMove(128,205)
MouseDown("left")
MouseUp("left")
WinWait("Viking Direct - Your Favourite Office Products, For Less - Windows Internet Explorer","Favorites Command Ba")
If Not WinActive("Viking Direct - Your Favourite Office Products, For Less - Windows Internet Explorer","Favorites Command Ba") Then WinActivate("Viking Direct - Your Favourite Office Products, For Less - Windows Internet Explorer","Favorites Command Ba")
WinWaitActive("Viking Direct - Your Favourite Office Products, For Less - Windows Internet Explorer","Favorites Command Ba")
MouseMove(324,15)
MouseDown("left")
MouseUp("left")
MouseMove(68,319)
MouseDown("left")
MouseUp("left")
MouseDown("right")
MouseUp("right")
MouseMove(120,390)
MouseDown("left")
MouseUp("left")
MouseMove(156,320)
MouseDown("left")
MouseUp("left")
MouseMove(601,494)
MouseDown("left")
MouseMove(547,492)
MouseUp("left")
MouseMove(532,492)
MouseDown("left")
MouseMove(510,492)
MouseUp("left")
MouseMove(684,493)
MouseDown("left")
MouseMove(512,481)
MouseUp("left")
MouseMove(521,472)
MouseDown("right")
MouseUp("right")
MouseMove(544,503)
MouseDown("left")
MouseUp("left")
WinWait("Microsoft Excel - Book1","Worksheet Menu Bar")
If Not WinActive("Microsoft Excel - Book1","Worksheet Menu Bar") Then WinActivate("Microsoft Excel - Book1","Worksheet Menu Bar")
WinWaitActive("Microsoft Excel - Book1","Worksheet Menu Bar")
MouseMove(66,15)
MouseDown("left")
MouseMove(66,14)
MouseUp("left")
MouseMove(121,168)
MouseDown("left")
MouseUp("left")
MouseDown("right")
MouseUp("right")
MouseMove(191,234)
MouseDown("left")
MouseUp("left")
MouseMove(68,204)
MouseDown("left")
MouseUp("left")

Until i get a proper handle on all this VBA/autoit cleverness, i would really appreciate any help!

Many Thanks!

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0