Excel-form to Web-form on MAC OS

Hi,

I am a new bee to this forum with an interesting question in my mind.

I am using MAC OS X, Firefox 3.2.8 and Excel 2011/2004.

I have a webform to fill in with some metadata. The Metadata is stored in an excel sheet(s). Although I am going to create a replica of the webpage in excel.

The webpage has 50+ fields whereas there are only 21 fields in Excel sheet. It means only 21 cells will be copied on the webpage leaving the rest blank.

I do not want a script or a software to convert Excel form to Webform rather I want only the data to be copied to my Client webform.

Is there any way to do this through some scripting i.e. Applescript or VBScript

Please help me in this case as I have around 2500 forms to fill in.

Thanks in advance.

Masood

Hi Masood,

Welcome to MacScripter!

Hopefully this will get you on your way.
I’ve tested this little example and it works ok.

First of all cut’n’paste the code below and paste it into a text editor/code editor.
Save it as a .htm file and then open that in Safari.

[b]

Test Form



Text Area 1:

Text Area 2:

Text Area 3:




[/b]

You should have a very quick web page containing a test form.

Then open the Applescript code below in the Applescript Editor.
If you then execute the code it should fill in the form fields on the webpage.

tell application "Safari"
	activate
	do JavaScript "document.getElementById('textArea1').value = 'Fred Bloggs';" in document 1
	do JavaScript "document.getElementById('textArea2').value = 'Jane Bloggs';" in document 1
	do JavaScript "document.getElementById('textArea3').value = 'Glen Bloggs';" in document 1
	--do JavaScript "document.getElementById('submitButton').click();" in document 1
end tell

I’ve commented out the last ‘do javascript’ line so you can see the test data going in to the web form.
If you un-comment that line the script ‘submits’ the form.

Working forward from this example it should just be a case of pulling the data from Excel and replacing the sample data ‘Fred Bloggs, Jane Bloggs and Glen Bloggs’ with variables containing the data from Excel. If you have a search around MacScripter there’s loads of Excel snippets to get you going.

Wrap all that up in a loop and hopefully that should do the trick.
You may want to leave out the last ‘do javascript’ line I mentioned earlier so you can check the data before you ‘submit’ it.

You’ll also need to have a look at the code on the web form you’re using to get the names of the form fields. If you look at the example above you’ll need to replace the ‘textArea1’ etc with the names of the fields from your web form.

Hope that helps.

Nick

Hi TecNik,

I like your technique, but it would be more helpful if you could write a complete code for me. I am unable to recall the values from variables on the webpage. I tried to write something (see below), but the values from B2, B3 and B4 are not reflecting on the webpage.

Please help

tell application “Microsoft Excel”
activate
set f01 to value of cell “B2”
set f02 to value of cell “B3”
set f03 to value of cell “B4”

tell application "Safari"
	activate
	do JavaScript "document.getElementById('textArea1').value = var f01;" in document 1
	do JavaScript "document.getElementById('textArea3').value = var f02;" in document 1
	do JavaScript "document.getElementById('textArea2').value = var f03;" in document 1
	--do JavaScript "document.getElementById('submitButton').click();" in document 1
end tell

end tell

Hi Nick, I got it, here is the code:


tell application "Microsoft Excel"
	activate
	set f01 to value of cell "B2"
	set f02 to value of cell "B3"
	set f03 to value of cell "B4"
	
	tell application "Safari"
		activate
		do JavaScript "
		var f01 = '" & f01 & "';
		document.getElementById('textArea1').value = f01;" in document 1
		do JavaScript "
		var f02 = '" & f02 & "';
		document.getElementById('textArea2').value = f02;" in document 1
		do JavaScript "
		var f03 = '" & f03 & "';
		document.getElementById('textArea3').value = f03;" in document 1
		--do JavaScript "document.getElementById('submitButton').click();" in document 1
	end tell
end tell

but now I’m stuck with something different, the problem what I have realized is that there is no element ID on the target webpage, there is a table instead, please help me figure it out.

Also note: We can’t edit the client’s webpage, we have to stick to it as is.
Now the question is, how will I tell the applescript where to copy the values from the variable. The values need to go exactly in the blank fields.

Sample/partial code from the webpage is given below:

			<form action="Index.cfm?FuseAction=Library.CheckNewData" method="post" name="03cbdba0-4244-41bf-8cec-de72afadf602_1"></form>
			<input type="hidden" name="StudioID" value="5">
			<input type="hidden" name="filmCategoryID" value="">
			<input type="hidden" name="FilmID" value="20404">
			<input type="hidden" name="CountryID" value="505">
			<input type="hidden" name="ClassificationID" value="61">
			<input type="hidden" name="Template" value="DVD">
			<input type="hidden" name="ElementID" value="277">
			<input type="hidden" name="PartID" value="">
			<input type="hidden" name="Variation" value="">
			<input type="hidden" name="FactID" value="03cbdba0-4244-41bf-8cec-de72afadf602">
			<input type="hidden" name="AssetID" value="0a064ac4-80f1-40a8-9be6-ce7c8a26a7d6">
			<input type="hidden" name="FactValueID" value="">
			<tr>
				<td><input type="checkbox" class="factcheckbox" id="fact_check" value="03cbdba0-4244-41bf-8cec-de72afadf602"></td>
				<td class="filmtitle" valign="middle">
					Aspect Ratio
				</td>
				<td class="filmtitle"> 
					
				</td>
				<td class="content" valign="middle">
					
					<input type="text" name="newFactValue" value="" class="FormFieldSelect" size="80">
					
				</td>
				<td>
					<input type="button" value="Edit" class="content" onclick="submit();">
				</td>
				<td>
					 
				</td>
			
			</tr>
	

	

	
		
			<form action="Index.cfm?FuseAction=Library.CheckNewData" method="post" name="0c5f71cb-c1d3-4e26-98e5-5795a108530b_2"></form>
				<input type="hidden" name="StudioID" value="5">
				<input type="hidden" name="filmCategoryID" value="">
				<input type="hidden" name="FilmID" value="20404">
				<input type="hidden" name="CountryID" value="505">
				<input type="hidden" name="ClassificationID" value="61">
				<input type="hidden" name="Template" value="DVD">
				<input type="hidden" name="ElementID" value="277">
				<input type="hidden" name="PartID" value="">
				<input type="hidden" name="Variation" value="">
				<input type="hidden" name="FactID" value="0c5f71cb-c1d3-4e26-98e5-5795a108530b">
				<input type="hidden" name="AssetID" value="0a064ac4-80f1-40a8-9be6-ce7c8a26a7d6">
				<input type="hidden" name="FactValueID" value="f4e45256-f10b-4757-a16c-62fdf5743a86">
			<tr>
				<td><input type="checkbox" class="factcheckbox" id="fact_check" value="0c5f71cb-c1d3-4e26-98e5-5795a108530b"></td>
				<td width="176" class="filmtitle" valign="middle">
					Audio
				</td>
				<td class="filmtitle"> 
					
				</td>
				<td class="content" valign="middle">
					
					  
						<input type="text" name="newFactValue" value="" class="FormFieldSelect" title="Audio format offered with the release" size="80">
					  
					
				</td>
		
				<td valign="middle">
					<input type="button" value="Edit" class="content" onclick="submit();">
				</td>
		
			

			
	
			</tr>
		
	

	

	
			<form action="Index.cfm?FuseAction=Library.CheckNewData" method="post" name="15871d9b-1206-4857-b5a2-4345811c8e87_3"></form>
			<input type="hidden" name="StudioID" value="5">
			<input type="hidden" name="filmCategoryID" value="">
			<input type="hidden" name="FilmID" value="20404">
			<input type="hidden" name="CountryID" value="505">
			<input type="hidden" name="ClassificationID" value="61">
			<input type="hidden" name="Template" value="DVD">
			<input type="hidden" name="ElementID" value="277">
			<input type="hidden" name="PartID" value="">
			<input type="hidden" name="Variation" value="">
			<input type="hidden" name="FactID" value="15871d9b-1206-4857-b5a2-4345811c8e87">
			<input type="hidden" name="AssetID" value="0a064ac4-80f1-40a8-9be6-ce7c8a26a7d6">
			<input type="hidden" name="FactValueID" value="">
			<tr>
				<td><input type="checkbox" class="factcheckbox" id="fact_check" value="15871d9b-1206-4857-b5a2-4345811c8e87"></td>
				<td class="filmtitle" valign="middle">
					Audio Descriptive Logo Present
				</td>
				<td class="filmtitle"> 
					
				</td>
				<td class="content" valign="middle">
					
					<input type="text" name="newFactValue" value="" class="FormFieldSelect" title="Is there an Audio Descriptive (aka. Audio Description) logo on the packaging?" size="80">
					
				</td>
				<td>
					<input type="button" value="Edit" class="content" onclick="submit();">
				</td>
				<td>
					 
				</td>
			
			</tr>

Hi Masood,

This little snippet of code works for me when I test it on the webpage code you listed:-


tell application "Safari"
	activate
	do JavaScript "document.getElementsByName('newFactValue')[0].value = 'Fred Bloggs';" in document 1
	do JavaScript "document.getElementsByName('newFactValue')[1].value = 'Jane Bloggs';" in document 1
	do JavaScript "document.getElementsByName('newFactValue')[2].value = 'Glen Bloggs';" in document 1
end tell

It’s using getElementsByName instead of getElementById.

HTH

Nick