Build an Excel Spreadsheet to Hold a Url List

 

images\img00533.gif

Here we see that there are 8 pages of results. We need to see if we can find the URLs for page 2, 3, 4, etc. and then understand how they are built in order to generate a list of them. A handy tool that you can use sometimes is found in the View menu. It is View à Hyperlinks.

 

 

images\img00534.gif

The View à Hyperlinks will bring up a box that shows all of the Hyperlinks on the web page in the Browser pane. Notice that on this site, after the basic site navigation links, the other URLs get pretty long and complex. If you can find your URLs quickly like this the View à Hyperlinks feature is very quick and easy.

 

This sites links are a bit more complicated and I recommend a manual process to build the list.

 

 

images\img00535.gif

What you can do is right click on the link to page 2 and select Copy Shortcut then paste the URL in Notepad.

 

 

images\img00536.gif

With the link in Notepad you can analyze it to see if you can find the variables. In this case I have found pagenumZ1, which may be the page counter. To sanity check this and to make sure that page number is the only variable in need to deal with, I am also going to use Copy Shortcut on the Page 3 and Page 4 links.


After getting the URLs for Page 2, 3, and 4 I am going to go to Page 2 in the Browser pane which will allow me to get the shortcut for Page 1. This is important since Page 1 will show whether this page uses a 0 or a 1 as the starting point.

 

 

images\img00537.gif

Here is what all of these URLs look like in Notepad. In comparing them we find out our guess at pagenum being the part of the link that changed pages was wrong. We see that the links are identical except for the frtsz portion where 0 is Page1, 50 is Page 2, 100 is Page 3, and 150 is Page 4.


There are 50 items per page and the count starts at 0. We can now build a list of URLs for the 8 pages to use with the Basic Automation Wizard.

 

We are going to build this list in Excel so we can store it to use later.

 

images\img00538.gif

We place a Header in Row 1, Column A called URL and paste the URL for Page 1 here. Then we build a Page Counter in Column B.

 

images\img00539.gif

We edit the Page 1 URL in Row 2, Column A to use an Excel formula so we can generate the other URLs based on the information in Column B. The = lets Excel know that the cells information is a formula, the quotation marks go around text characters, the & is used to insert information from other cells to the equation, and (B2) is the data inserting.

 

Once we have this built, we can drag the A:2 down to A:9 and our formula will insert (B3) thru (B9) respectively. This provides a list of the 8 URLs needed for our first Basic Automation Wizard.

 

images\img00540.gif

Here is a sample of the final version of the Excel sheet. Now, we are ready to launch the Basic Automation Wizard.