Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Autofill text boxes in a website from excel

0

This has probably been answered but I can not find it and dont know what to even search for really. I am very computer literate but never played with codes/macros alot so I do appologize for a dumb question if it is. (I use a mac). And I was a admin on a forum many years ago and understand this was probably gone over before, I just dont know how to find it. 

I am an insurance adjuster. For work I have to fill out a spreadsheet on every claim. Then on the website I have to input these #'s into text boxes on every claim. The text box is named the same in each claim from the "Inspect elements" on right click. So what I am looking for is after I am done with the spreadsheet is there an easy way to hit "edit" on the website so I can fill in the lets say 5 text boxes, and then hit another button that would run the script or macro or what ever. Since it would be a new spreadsheet for each claim its not like an autofill from just one spreadsheet, but from a new version of that spreadsheet that would have new #'s for each one. I hope I have made sence here if not I am sorry. Also if this is possible there are a few different things I could use the same system on to save5 minutes+ on each claim. Thats not alot but when doing 3-6 claims a day every day this would add up to a significant time savings compaired to just slowly inputting all this info into the same fields over and over again. 

Oh and last piece of info, the excel document has multiple tabs, I am using the 3rd tab in named "estimate breakdown" I dont know if this would change anything but the more info i put the better. 

Thanks for any help.

-John

Answer
Discuss

Discussion

Do you have access to the database that is behind the form? Or, you only have access to the form?
don (rep: 1989) Apr 18, '17 at 6:03 am
just the form
Jvelella (rep: 2) Apr 18, '17 at 11:00 pm
Add to Discussion

Answers

0
Selected Answer

Here is a great little compact macro that should do what you want. I found it on the web and edited it for your purpose.

Sub ExceltoWebsiteForm()

Dim ie      As Object
Dim frm     As Variant
Dim element As Variant

Set ie = CreateObject("InternetExplorer.Application")
'If there is an error running the code, comment-out the above line and un-comment the below line
'(if you un-comment the next line, make sure to enable Microsoft Internet Controls - Tools > References > Microsoft Internet Controls)
'Set ie = New InternetExplorerMedium

ie.navigate "http://www.website.com/"
While ie.readyState <> 4: DoEvents: Wend

'try to get form by ID
Set frm = ie.document.getElementById("form")

'try to get form by name
If frm Is Nothing Then Set frm = ie.document.getElementsByName("form").Item(0)


If frm Is Nothing Then

   'error message if the form was not found
   MsgBox "Form not found"
   ie.Quit
   Set ie = Nothing

Else
   ie.Visible = True

   For Each element In frm.elements
      On Error Resume Next
      Select Case element.Name
         Case "formFieldName1": element.Value = Sheets("Sheet1").[A1]
         Case "formFieldName2": element.Value = Sheets("Sheet1").[A2]
      End Select
   Next

   'uncomment next line to submit the form
   'frm.submit

End If

End Sub

Things to change:

http://www.website.com/ - the url where your form is located

form - the id of the form - this could also be the name of the form, depending on the setup of the website. You will have to look at the websites html code to figure this out and it will look something like this: <form id="form_name" method="post" action="/">

The second "form" that appears in the macro code is for the tag name of the form, if it even uses a tag name - it will most likely have an ID though and that's the easiest way to reference it.

formFieldName1 - this is the name of the field within the form where you want to put information. It will appear in the html code below where it has the form code listed above here.

Sheets("Sheet1").[A1] - this is in the same line as the last part and it says from which worksheet and which cell in that worksheet you want to get the data that you input into the website.

This might take some playing around with, but everything should be there now. Also, you might have to enable an object library for this to work, I don't remember.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login