Updating inventory file

QuestionEdit

I created anfile for my entire catalog of products that I routinely add/change/update and import into my website.

I am a drop-shipper so I rely on my vendors having stock whenever I sell an item, which is not always the case. I cannot manually go through 1,600 items everyday to verify they have stock. I deal with 5 different warehouses, so I've been told it's a little tricky to link it to excel.

What I've come up with, and really hope its possible, is to have a cell for each item, and link it to that websites product page. If the page is available, then the item is in stock and will let me know by, maybe, putting a yes in another cell. If the link doesn't work, then the item is unavailable and it should read no.

The problem is that my biggest warehouse actually has an inventory status line on all their listings. Is there a way to also link to a line of text, and to retrieve that line and enter it into another cell? The line states NOW, if available, and maybe 1-4 weeks, or 2-3 months if on backorder.

Any help would be greatly appreciated. I have no idea where to start.

Stephan

AnswerEdit

Stephan,

the link to your biggest warehouse is probably the best supported by Excel. You can set up a web query to pull that data. Then you can have formulas extract information from the data pulled in.

Here are several links to information on setting up and using Web queries in Excel. the last article might be a good place to start. It references the first article in the list as well.

Excel 2002 (Getting Data from the Web) http://office.microsoft.com/en-us/excel-help/getting-data-from-the-web-in-excel-HA001045085.aspx

Overview of Web Queries in2002 http://office.microsoft.com/en-us/excel-help/overview-of-improved-web-queries-in-excel-2002-

HA001045093.aspx

Get external data from a Web page http://office.microsoft.com/en-us/excel-help/redir/HA010218472.aspx?

Excelimport connections http://office.microsoft.com/en-us/excel-help/connect-to-import-external-data-HP010089898.aspx?CTT=1

http://office.microsoft.com/en-us/excel-help/redir/HA001045093.aspx?

queryid=5fa43b608a7245febed5d7aba2d1cefb&respos=2&CTT=1

A query of the Office site on Web Queries - has links to the above articles plus more http://office.microsoft.com/en-us/excel-help/results.aspx?qu=%22web+query%22&origin=HP010089898

Non-Microsoft source: Excel Web Query Secrets Revealed http://www.vertex42.com/News/excel-web-query.html

As far as checking if a page exists, I would see setting up a sheet with the product information including the URL to the page. Then running a macro that tries to open that page as a new workbook inand using error handling to test whether the page is opened or an error is obtained. You would then need code to close the page.

sub Testsite() Dim bk as Workbook On Error Resume Next set bk = Workbooks.Open("Http:\\www.mysupplier.com") On Error goto 0 if bk is nothing then

  ' can't open the page

else

  bk.Close SaveChanges:=False

end if End sub

would be the type of approach I am suggesting.

someone who claims expertise in web programming might have a better method to do the checking.