Excel, VBA, IE and Me

Cauld

Ahhh, my first thread :-)

I have a long, long list of of people's names (thousands) in excel. Each name is on its own row. I need to go into my company's online database and add a property to each individual. I know I can use excel to open IE windows and I know there's a way to put data from a cell into a specific field. I guess with that knowledge I imagine there's a way to automate at least part of what I do.

When I do this manually I first enter the query information (last name and first name) into the query form and click submit. I then choose the specific individual if there are multiple people with that name by clicking on that person's name. I then click on a specific tab (Categories). I then click new and click into the new line under the 'Categories' heading. I paste in the value and then click on a link to restart the process. It takes me close to a minute per person.

One of the problems with this is that sometimes the list I have doesn't exactly correspond to the online database. For example my record might show Bob Doe, when the online database shows Robert Doe. It's fine if it skips these ones and I manually do them later.

Ideally I'd like to make something that automatically enters the information for me and highlights each line as it finishes. If it can't find a record, then I'd like it to just restart the process with the next row and not highlight the line. I can manually go back and do those.

I understand it will be hard to help with the information I've given, but if someone has an example of something similar I have a reasonable amount of confidence I could adapt it to my needs. I've just never used excel to do things in IE and have only limited VB experience in general.

whuppins

In my experience, Excel can't do a whole lot of interactive stuff with a web page once it's been launched. I could be wrong, though. How exactly do you know it can fill web forms, out of curiosity?

Ideally, you'd want to use some method of connecting directly to the database instead of having to go through the web API. Excel has tools for this, like performing DB queries and XML import/export. If you had a better idea of how this database worked (or if there was someone you could ask), you could probably build some automation into the process.

If you're forced to go through the web, perhaps someone can show you (us) how to get Excel to fill forms and activate controls on web pages. If not, you may be able to at least automate the first step if a query to this particular site returns a predictable 'search results' URL -- for example, instead of using Google's form and button to search for "Bob Doe", you could just type:

http://www.google.com/search?hl=en&q=Bob+Doe

into the address bar by hand. If it's something like this, Excel could at least do your querying for you. If your criteria are submitted to a cgi script or something behind the scenes, it may not be possible.

Cauld

I guess I don't know that excel can fill out online forms, but I thought I'd read about some of that when I was googling around looking for help.

Unfortunately once I log into the online database, the url doesn't change. It is a Siebel database, if that helps at all. I'm considering asking someone in the company for help like you mentioned, since I'll likely have a lot of these kinds of projects in the future and hopefully if they can provide me with a solution I could custom tailor it to each project.

whuppins

OK, I take it back. Excel can do it, I think. I should have guessed that crafty old Microsoft includes InternetExplorer.Application as an allowable VBA object, complete with its own set of methods. Or at least, that's what this page is trying to get me to believe. I can't find any reference to the IE-related objects/properties/methods in my Excel 2003 documentation.

That page may be a good starting place to solve your problem, but it may be pretty daunting if you don't have much VBA experience. I'd play around with it myself (Excel's kind of a hobby of mine), but I'm going to be very busy the next few days.

Daenris

It's possible that Excel could possibly accomplish something like this with advanced VBA, maybe. However, VBA really sucks. Every time I try to do something in it beyond simple things it becomes a bigger task than it should. If you (or someone in the company) has any programming experience, it would likely be easier to create a new program in another language to do exactly what you need it to do.

blincoln

Is there a DBA that you can talk to where you work? You can probably do this a lot faster with a query directly against the database. Is the property that you're setting the same value for each person, or do different people get different values for that property?