# Solved: Excel 2003 to autofill website forms?



## LEONARDTRAILERS (Oct 29, 2010)

I'm trying to get excel 2003 enter a row of data into a website form so i can submit it and repeat the the process with the next row...

I'm submitting inventory to a website but have the data in an excel spreadsheet, so I'm hoping to avoid manually entering each and every item onto each and every website. Some sites have a bulk upload feature using XML, but this is for the sites that don't...

Is this possible? By using excel's web query feature i can pull data from a site, i guess I'm trying to post it to the form...

Any takers?

Thank you in advance for your time and consideration...


----------



## LEONARDTRAILERS (Oct 29, 2010)

WOW....Nothing? Not even a "this is impossible"?


----------



## slurpee55 (Oct 20, 2004)

Uh, you posted this on a Friday, right? Don't expect too much on the weekend! 
I think it would help to see a site you want to post to, if that is possible - the limitations are more likely to be there than with Excel.


----------



## LEONARDTRAILERS (Oct 29, 2010)

the site that I've been testing on is: http://www.horseclicks.com. To see what I'm trying to do you'll have to log in. I've already set up a "help account" (separate from my own  for someone to help me with. Once logged in you'll click on the "my trailers" link on the left and then click the "add" button in the middle of the page. This will bring you to the web form I'm referring too. Mind you, i want to be able to do this to other sites as well, I just feel that once i see the dynamics of how this is done, I'll be able to do it again just changing what needs done. The user name: excelhelp pass: excel


----------



## slurpee55 (Oct 20, 2004)

This should probably be possible using VBA, but it is beyond my abilities. However, I will see if there aren't a few good coders around who could help. (I found one online and sent him a note.)


----------



## Keebellah (Mar 27, 2008)

Hi, I got Slurpee's note and I'll see if I can get an idea of what you want done.
Just don't hold your breath since I'll need a little time and tomorrow I'm on a full-day course.
Send me a PM with the "help account" login credentials since I can't get passed the login screen


----------



## slurpee55 (Oct 20, 2004)

Hans, I almost missed the login too - at the end of post #4
"The user name: excelhelp pass: excel"


----------



## Keebellah (Mar 27, 2008)

Got it!


----------



## Keebellah (Mar 27, 2008)

The difficulty is to determine the position on the website's page, and then have a script to take avalue in cell x and out it in field y on the site.
A kind of autofill but then I think somebody with php knowledge could put a shell around it.
I imagine since you say you want it to work for more sites that they're not yours to maintain?
Another option would then be to upload the data (via ftp or sftp) and put it in the underlying database.
I have seen the site and I'll just see if I can find a way to do it and once that's know vba code can be written to 'fill-in' the blanks.
I'll give it some thought and ask around.


----------



## slurpee55 (Oct 20, 2004)

One possible problem is that you don't really enter the data. It has option fields which you select from, such as this:

Horse
Cargo
Travel
Misc
so the work needs to be done on the web site end to draw from your data, not vice-versa.
Also, your data needs to conform to how the site accepts input - for instance, if your trailer was made in 1977, it would have to match the input:
pre-1980.


----------



## LEONARDTRAILERS (Oct 29, 2010)

All the drop down menu data is pretty much industry standard, Status (available, pending, sold) is how each item is listed on my spreadsheet, same with the Trailer Type, Brand, Axles, etc... and even if there is a slight discrepancy I'd just use the find/replace feature and conform my spreadsheet to fit the site.


----------



## slurpee55 (Oct 20, 2004)

I am okay with PHP and HTML as well as pretty good with Excel, but I just don't see how you can do this from your end. I truly think the website needs to be built to (also) accept data in such a way - and if I were the webmaster, I wouldn't bother to do that for one person (no offense meant, just reality.)


----------



## LEONARDTRAILERS (Oct 29, 2010)

I understand. I just wasn't sure if there was a "PUSH" or "POST" function within excel that i could use to my advantage. There are software solutions that claim to be able to do this e.g. Automate Anything, iChameleon, etc. I was just trying to use excel as an auto-fill app where I'd just rename the columns to fit the fields and it would insert the data from a row. 

I appreciate you looking into this for me, this is by far the absolute best help forum site i've ever come across on the web (and I've been to hundreds)


----------



## slurpee55 (Oct 20, 2004)

I agree about this site (the folks here are great and so is the advice) - I don't think either of those programs could do what you want either.
There are a few people who come here on occasion (Zack Barresse being the one I have in mind) who would know if this is in any way possible. I will send him a note and ask him to look at your thread, but I have no idea when he might pop in - it could be today or in 4 weeks....


----------



## LEONARDTRAILERS (Oct 29, 2010)

slurpee you're awesome!!


----------



## slurpee55 (Oct 20, 2004)

I'm not sure about that (  ) but I did send Zack a note.


----------



## Zack Barresse (Jul 25, 2004)

Hi all,

Yes, I apologize for my infrequency. Sometimes my schedule and work can be daunting. Keep on me like my wife does. Almost. 

You can do things like this, yes. Like you said, the "POST" method is possible using VBA. Some problems I do foresee though is not all controls are named the same, and you'd really need to know the control names to code it. And if this is only a "test" site, we don't know what actual site you're looking at doing this on, which would help. One problem with test data (or site in this case) is often it isn't representative enough of actual data (or site) for the solution to crossover and work on the real-deal. Often times it's a PITA and we'll end up making two different solutions. So the actual site should be utilized.

One example of logging into an internet site via VBA can be found here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=399

Take a look at that link (you have to login, which means being a member, an unscrupulous attempt by me to gain more members, muwahahahaha!) and look at the *ieForm(0).Value = MyLogin* line. (But really though, you don't have to login, the line I am talking about is the important part.) Finding that control number amounts to more or less a guessing game, or one of trial and error. Sure you can use object names like "Username" or "Password", but not all web coders name their controls like this, sometimes for security purposes they intentionally name them something else. That command line example doesn't use a name, but position in order of index, so no name is required. But you'd still end up doing a trial and error to find out which buttons were which. Obviously this is possible too, it just takes time.

So without knowing more information and specifics about the entire scope of your project and the actual site to be utilized, I can tell you that it's most likely possible, but we can't give you a solution as the specifics aren't nailed down yet. Make sense? Post more information, get down to specifics, and we can see about getting you a working solution.

HTH


----------



## slurpee55 (Oct 20, 2004)

Zack, doesn't that merely attempt to log in via VBA? What he is trying to do here is load a form on a page with values drawn from Excel after already logging in.


----------



## slurpee55 (Oct 20, 2004)

Oh, and Leonard, do join VBA Express - it is Zack's site, and really brilliant VBA code can be found there - way beyond what I can figure out to do with most of the time, but....


----------



## Zack Barresse (Jul 25, 2004)

Well, it shows what would be needed as the first leg here. Once login is established and the data is accessible you can grab it by a number of ways. I generally like to parse the source code with either the HTML library reference or the MSXML2 library. (Examples here and here

Here are some more examples of logging into a website with form-specific controls:

http://www.vbaexpress.com/forum/showthread.php?t=6079
http://www.vbaexpress.com/forum/showthread.php?t=9329
http://www.xtremevbtalk.com/showthread.php?t=298964
http://www.xtremevbtalk.com/showthread.php?t=296194
http://www.vbaexpress.com/forum/archive/index.php/t-5978.html
http://www.vbaexpress.com/forum/archive/index.php/t-11287.html
http://www.vbaexpress.com/kb/getarticle.php?kb_id=973 (checking gmail, somewhat related)
http://www.mrexcel.com/forum/showthread.php?t=342939&page=2 (getting text)
http://www.codeforexcelandoutlook.com/excel-vba/automate-internet-explorer/

So it's a start.


----------



## LEONARDTRAILERS (Oct 29, 2010)

HorseClicks.com is one of the actual sites that my inventory is going onto, i just created a test account so anyone helping can log in to see the form in question and grab the source code if necessary. I'm in the process of teaching myself python, why not VBA while I'm at it


----------



## LEONARDTRAILERS (Oct 29, 2010)

@Zack (or anyone more knowledgeable than I):
i visited the link you posted:
http://www.vbaexpress.com/forum/showthread.php?t=6079

It sounds like he's requesting something similar:
"by selecting a row of data from the Excel sheet and then using VBA to open and automatically fill in the web login form with the data contained in the current record"

I'm going through the code and i don't see any reference to an excel sheet, it looks as if the passwords are hard coded into the VBA script... but i could be wrong.


```
[COLOR=blue]Option Explicit[/COLOR] 
 
[COLOR=blue]Sub[/COLOR] TestIE_Rollin() 
     
    [COLOR=blue]Dim[/COLOR] oIE [COLOR=blue]As[/COLOR] InternetExplorer 
    [COLOR=blue]Dim[/COLOR] ieForm 
    [COLOR=blue]Dim[/COLOR] i [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] 
     
    SetRefs 
    [COLOR=blue]Set[/COLOR] oIE = [COLOR=blue]New[/COLOR] InternetExplorer 
    oIE.Visible = [COLOR=blue]True[/COLOR] 
    oIE.Navigate "https://www2.cplink2.com/bin/web_loginout.exe" 
    [COLOR=blue]Do Until[/COLOR] oIE.ReadyState = READYSTATE_COMPLETE: [COLOR=blue]Loop[/COLOR] 
         
        [COLOR=blue]With[/COLOR] oIE.Document 
            .forms("passlogon").All("UserID").Value = "jsmith" 
            .forms("passlogon").All("NodeID").Value = "M1234567" 
            .forms("passlogon").All("Password").Value = "myPass" 
            .GetElementsByName("rbFCRA").Item(0).Click 
            .GetElementsByName("rbClaimsUse").Item(0).Click 
            .GetElementsByName("rbHaveConsent").Item(0).Click 
        [COLOR=blue]End With[/COLOR] 
         
        [COLOR=blue]Set[/COLOR] oIE = [COLOR=blue]Nothing[/COLOR] 
        [COLOR=blue]Set[/COLOR] ieForm = [COLOR=blue]Nothing[/COLOR] 
         
    [COLOR=blue]End Sub[/COLOR] 
     
    [COLOR=blue]Sub[/COLOR] SetRefs() 
        [COLOR=blue]On Error Resume Next[/COLOR] 
         [COLOR=darkgreen]' Adds Internet Controls Ref (SHDocVw.dll)[/COLOR]
        ThisWorkbook.VBProject.References.AddFromGuid "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}", 1, 1 
    [COLOR=blue]End Sub[/COLOR]
```
Is there somewhere in the code that refers the IE to pull from an excel spreadsheet and i'm missing it?


----------



## slurpee55 (Oct 20, 2004)

Yes, the log in is hard coded, but that page talks about radio buttons only (which are assigned a value of 1 or 0) whereas the drop-down form that we were looking at, well, that one I listed in post #10 on trailer types:
Horse
Cargo
Travel
Misc
You would have to convert your data to match these values (and all the other data as well) prior to uploading - and there is no guarantee that any two sites will code the same thing the same way (in the above, for instance, I would have been prone to code Misc as 4 and Travel as 3 at the very least.)
You could ultimately write some code that would make a copy of your data all converted over to prepare it for upload - assuming Zack figures out how to do that....


----------



## Zack Barresse (Jul 25, 2004)

So you're only looking to fill in the values of the fields on the web page? Nothing else after that? Just navigate to the site, login, fill in the form? Also, what other sites? Since web sites are so different, I'd recommend programming these on a one-up schedule. There may be similarities, but we'd still need to either find the layout of the controls or their names. Are you also keeping the URL, username and password for the site in the Excel sheet? Exactly how many sites are you looking at doing this for?


----------



## LEONARDTRAILERS (Oct 29, 2010)

I attached a spreadsheet called "sample_inventory" so you can see what my inventory program exports as an excel file... I can change column names or even tailor the details (in this one i changed "Horse Trailer - Living Quarters" just to "Horse") with the find and replace to handle the drop down items...

*in this file i also added a "Living Quarters" column to help it conform to this site...

**BTW the info is on "sheet 2" of this file


----------



## LEONARDTRAILERS (Oct 29, 2010)

This spreadsheet is only an export of my inventory. I'm comfortable with adding to it, changing minor details to tailor it, or taking things away from it... Adding the user name and password to the sheet would be awesome (but not necessary). I swear i'm not a n00b, i'm just way more comfortable with Adobe's graphics programs over MS office any day. 

But if there's more that can be done then it would be awesome to have it:
1) go to the URL
2) log in
3) go to the trailer upload page
4) auto fill the form from a row in my spreadsheet
5) add the picture URL's
6) hit submit
7) repeat steps 4 - 6 with the next row of info...

But that's in a perfect world, I'd be more than happy if someone could help me with step 4 (maybe 5 if thats possible without 1000's of hours of coding and research)


----------



## LEONARDTRAILERS (Oct 29, 2010)

Oh, and to answer your question Zack, there are about 20 (or more) trailer and horse trailer classified ad websites that i'd have to repeat this on... I just figured that i'd have to change the URL, the names of the columns to match the fields on the site and maybe tailor some details to make it match the drop downs and code accordingly if there are any radio buttons or check boxes. 

Also, i set my inventory to export the URL of the trailer's photo along with the other columns, i just didn't have that option selected when i exported this sample...


----------



## Zack Barresse (Jul 25, 2004)

Like I said, each website will be unique. Unless all of the controls are setup exactly the same, which is mostly doubtful. It would be like creating 20 custom solutions.


----------



## slurpee55 (Oct 20, 2004)

If they each and every one called the same option/item by the same name it would be a horrible chore - the browser would have to make a match by the option names with the same name in Excel. But the odds of that being true are impossibly low. I'm sure Zack could do this, but he is volunteering here - he has a job and a family.... Sorry Leonard, but I think you are stuck doing it the long, slow way.


----------



## LEONARDTRAILERS (Oct 29, 2010)

Hey, it was a shot in the dark... I do thank you for taking your time to look into this for me. You guys are AWESOME! :up:


----------



## slurpee55 (Oct 20, 2004)

Yeah - well, personally I am sorry, and if it had been just one page on one site maybe we could have gotten it done....
But one never knows - there may be some neat piece of software out there that will do this that, given the apparent amount of this you want to do, may be worth buying.


----------



## LEONARDTRAILERS (Oct 29, 2010)

Now if it could work on one page... wouldn't it just be a matter of swapping out the URL and then renaming the elements in my spreadsheet to correspond to the next sites parameters (plus a slew of other changes I'm sure)? Even if someone could direct me to a half way working model that will post data from a row in a spreadsheet to a web form I'd scour VBA forums and code til i made it my own. (as nice as it would be to have the work done it's not necessary, I'm more than happy to do the leg work) *I just needed to to know that (a) what I'm trying to do it possible and (b) pointed in the direction of a project or 2 that's even remotely similar to mine. * I'm just looking to get this to work on this "HorseClicks.com" at first and then if it's not a feasible solution then I'll explore other avenues (unless it's NOT a feasible solution already and I'm just kidding myself)

Cheers


----------



## slurpee55 (Oct 20, 2004)

I suspect (and I'm over my head here) that swapping out the URL would be the only easy part - all the rest would be long, tedious and laborious. You would probably have to build a different macro for each different URL....


----------



## LEONARDTRAILERS (Oct 29, 2010)

yes but if i had one working macro/script for one site to use as a guide to make the necessary changes for every other website... I'm not worried about future websites just yet, I'm still trying to find a working model of something at least remotely similar to my goal here so i can try to get this working with this HorseClicks.com.


----------



## LEONARDTRAILERS (Oct 29, 2010)

Also, how do programs like robo-form auto-fill webforms? You would think that what i'm trying to do isn't much different and since i'm trying to push data from a spreadsheet it wouldn't be so hard...


----------



## slurpee55 (Oct 20, 2004)

Roboform has you fill out unchanging data (or fairly constant data that you have to change yourself, such as your address) and uploads that if you click into a field with a name that seems to fit (e.g. "Name, First" or "First Name" would both be accepted). But it is comparatively dumb compared to what you want done here - it doesn't even recognize a form (as far as I know) unless you click on the toolbar in your browser.


----------



## LEONARDTRAILERS (Oct 29, 2010)

Not sure if anyone is still following this thread but I've solved my own problem by using iMacros firefox plugin... If anyone else has a similar issue to mine post here and i'll include all the details when i have more time


----------



## Keebellah (Mar 27, 2008)

Thanks to for the information. Looking forward to see your solution when the time comes.
Thanks for sharing.


----------



## LEONARDTRAILERS (Oct 29, 2010)

Well, my goal was to export my inventory into an excel spreadsheet (each column a detail: stock #, year, make, model, size, etc.../each row a trailer), then use each row of data to auto-fill classified ad submission forms while uploading photos as well. Then have the script submit the ad, navigate back to the submission form and repeat the process with the next row of data.

After scouring Google for countless hours i came across the Firefox plug-in "iMacros" (they also have a plug-in for both IE and Chrome in case one of those are your browser of choice). I took apart a few of the demo scripts and pieced together a few working ones the are going to turn out better then i could have imagined. Here's what I'm getting it do so far:

*Mind you, this is still a work in process and I'm still tailoring my script to work exactly how i want it, but my test runs while slow are coming along great!*

1. I export my inventory to a .CSV file

2. My script (running in Firefox) first reads the stock #s from the .CSV file and then navigates to my company's website searching for the images of those trailers and then downloading them to my computer in a folder specified in the photo location column in the .CSV file (using a preset file path but making the image file name a variable to match the corresponding stock number)

3. Then it navigates to (in this instance we'll use) a free classified ad website's ad submission page and fills out the form using row data from my .CSV file; also uploading the photo (it only lets you upload from a local drive, not link to an image URL)

4. After that it submits the ad photo and all

5. Then re-navigates back to the submission page and repeats the process with the next row of data.

Now I almost have a complete working script (A LOT of trial and error AND Google). For the next site i'll just tailor the spreadsheet data to match text fields, drop down menus & radio buttons/check boxes and change some links that way i won't have to re-invent the wheel.

In short, this has been a very time consuming yet *AWESOME *learning experience and only strengthened my resolve to learn a computer programming language (Python)

And sure I've spent a lot of time on this project, but the time I've put in versus the time I'll save in the long run (a whole 8 hour day usually spent manually uploading just a portion (plus everything I've learned) has made it well worth it!

Thank you to everyone who tried to help me with my issue. And if I can help anyone else I'm more than happy to try.


----------



## LEONARDTRAILERS (Oct 29, 2010)

IT WORKS! I've uploaded 73 pieces of inventory in 12 minutes (versus 3 hours)


----------

