Transferring HTML form data to a spreadsheet?

G

Guest

Joined
Oct 22, 2014
Messages
26
Reaction score
0
Hi Guys

I was hoping someone could help me. Say I have a simple web form,
consisting of one text field. How can I get the contents of that text
field into a cell in my spreadsheet? For example, lets say, I type my
name "2eXtreme" into the Google search bar, is there any way I can run
a macro or something similar on the page to read the contents of the
search bar text and then copy it into a cell in excel? I've tried
using Web Queries, but only text field labels get copied into the
spreadsheet, not the text field contents...

I hope this makes sense, any help is very much appreciated!
 
F

fgribben

Hi Guys

I was hoping someone could help me. Say I have a simple web form,
consisting of one text field. How can I get the contents of that text
field into a cell in my spreadsheet? For example, lets say, I type my
name "2eXtreme" into the Google search bar, is there any way I can run
a macro or something similar on the page to read the contents of the
search bar text and then copy it into a cell in excel? I've tried
using Web Queries, but only text field labels get copied into the
spreadsheet, not the text field contents...

I hope this makes sense, any help is very much appreciated!
Click to expand...

*bump*

Can anyone tell me if this is even possible? Please
 
J

JP

Do you want the URL? Or do you want the body of the web page result
after you click Search?

--JP
 
F

fgribben

Do you want the URL? Or do you want the body of the web page result
after you click Search?

--JP
Click to expand...

neither, what i want is to transfer the contents inside the search box
to a cell in excel. i used google as an example because its very
simple web form. in reality, i'd be opening a php page with a number
of text boxes displayed, with each text box containing certain
information.

you see, ive undertaken a task to check all the data contained in the
text boxes in a php file, and it would be easier to do if i had the
info transferred to a spreadsheet so i could sort it etc. i want to be
able to runa macro that accepts a url; goes to that url; then cycles
through each text box on that page, copies the information from each
text box, then pastes each text boxes contents into a new cell.

currently, when i try to use a web query to do this, the infomration
inside the text box isnt copied into the spreadsheet, only the name of
the actual text box.

so to summarise, my macro needs to:

go to a specified url
go to the first text box on the field (by text box i mean a field
where a user can enter text like the google search bar)
copy the contents of that text box (information is loaded into the
text box from a database that i dont have access to)
paste the contents of the text box into a cell in my spreadsheet

cycling through each text box isnt necessary, i jus need vb code that
gets the contents of a text box, my experiences in javascript would
lead me to believe its something like:

document.form.textBoxA.value

but i cant seem to find a good answer, i dotn really know where to
start as i havent done vb before

thanks very much for your time!
 
J

JP

I think I understand, you want the text from inside a text box on a
webpage. For example, you want the contents of the search box from the
google homepage.

If you go to google.com and click on View>Source (in Internet
Explorer) there is a single text box:

<input maxlength=2048 name=q size=55 title="Google Search" value="">

Technically it is called an input box. You should make a note of the
name "q" which you would use to identify the contents.

If you set a reference to the Internet Explorer object (shdocvw.dll)
then you can use this code:

Sub GoToGoogleWithEarlyBinding()
Dim appIE As InternetExplorer
Dim sURL As String
Dim SearchBox As Variant

Set appIE = New InternetExplorer
sURL = "http://www.google.com"

With appIE
.Navigate sURL
.Visible = True
End With

' loop code until page is fully loaded
Do While appIE.Busy
Loop

Set SearchBox = appIE.Document.getElementsByName("q")

If Not SearchBox Is Nothing Then
SearchBox(0).Value = "2eXtreme"
End If

End Sub

Or if you didn't want to set a reference to the IE object library,
this code would work:

Sub GoToGoogleWithLateBinding()
Dim appIE As Object
Set appIE = CreateObject("Internetexplorer.application")
Dim sURL As String
Dim SearchBox As Variant

sURL = "http://www.google.com"

With appIE
.Navigate sURL
.Visible = True
End With

' loop code until page is fully loaded
Do While appIE.Busy
Loop

Set SearchBox = appIE.Document.getElementsByName("q")

If Not SearchBox Is Nothing Then
SearchBox(0).Value = "2eXtreme"
End If

End Sub


Step through the code and you can see how it creates a new IE window,
finds the textbox and inserts a value. If you needed the searchbox
result for a particular page, click View>Source and find the name of
the input box, then set a reference to it using "Set SearchBox =
appIE.Document.getElementsByName("input box name")" and extract the
value by assigning it to a variable, such as

Dim X As String
X = SearchBox(0).Value

Then you can insert it into a cell like this: Range("A1") = X


HTH,
JP
 
F

fgribben

I think I understand, you want the text from inside a text box on a
webpage. For example, you want the contents of the search box from the
google homepage.

If you go to google.com and click on View>Source (in Internet
Explorer) there is a single text box:

<input maxlength=2048 name=q size=55 title="Google Search" value="">

Technically it is called an input box. You should make a note of the
name "q" which you would use to identify the contents.

If you set a reference to the Internet Explorer object (shdocvw.dll)
then you can use this code:

Sub GoToGoogleWithEarlyBinding()
Dim appIE As InternetExplorer
Dim sURL As String
Dim SearchBox As Variant

Set appIE = New InternetExplorer
sURL = "http://www.google.com"

With appIE
.Navigate sURL
.Visible = True
End With

' loop code until page is fully loaded
Do While appIE.Busy
Loop

Set SearchBox = appIE.Document.getElementsByName("q")

If Not SearchBox Is Nothing Then
SearchBox(0).Value = "2eXtreme"
End If

End Sub

Or if you didn't want to set a reference to the IE object library,
this code would work:

Sub GoToGoogleWithLateBinding()
Dim appIE As Object
Set appIE = CreateObject("Internetexplorer.application")
Dim sURL As String
Dim SearchBox As Variant

sURL = "http://www.google.com"

With appIE
.Navigate sURL
.Visible = True
End With

' loop code until page is fully loaded
Do While appIE.Busy
Loop

Set SearchBox = appIE.Document.getElementsByName("q")

If Not SearchBox Is Nothing Then
SearchBox(0).Value = "2eXtreme"
End If

End Sub

Step through the code and you can see how it creates a new IE window,
finds the textbox and inserts a value. If you needed the searchbox
result for a particular page, click View>Source and find the name of
the input box, then set a reference to it using "Set SearchBox =
appIE.Document.getElementsByName("input box name")" and extract the
value by assigning it to a variable, such as

Dim X As String
X = SearchBox(0).Value

Then you can insert it into a cell like this: Range("A1") = X

HTH,
JP
Click to expand...

Thanks very much for your reply, it's very much appreciated. When I
try to run either set of code, I get:

Run-time error '91':

Object variable or With block variable not set

Do you know what would be causing this error? Thanks very much for
your help!
 
F

fgribben

I think I understand, you want the text from inside a text box on a
webpage. For example, you want the contents of the search box from the
google homepage.

If you go to google.com and click on View>Source (in Internet
Explorer) there is a single text box:

<input maxlength=2048 name=q size=55 title="Google Search" value="">

Technically it is called an input box. You should make a note of the
name "q" which you would use to identify the contents.

If you set a reference to the Internet Explorer object (shdocvw.dll)
then you can use this code:

Sub GoToGoogleWithEarlyBinding()
Dim appIE As InternetExplorer
Dim sURL As String
Dim SearchBox As Variant

Set appIE = New InternetExplorer
sURL = "http://www.google.com"

With appIE
.Navigate sURL
.Visible = True
End With

' loop code until page is fully loaded
Do While appIE.Busy
Loop

Set SearchBox = appIE.Document.getElementsByName("q")

If Not SearchBox Is Nothing Then
SearchBox(0).Value = "2eXtreme"
End If

End Sub

Or if you didn't want to set a reference to the IE object library,
this code would work:

Sub GoToGoogleWithLateBinding()
Dim appIE As Object
Set appIE = CreateObject("Internetexplorer.application")
Dim sURL As String
Dim SearchBox As Variant

sURL = "http://www.google.com"

With appIE
.Navigate sURL
.Visible = True
End With

' loop code until page is fully loaded
Do While appIE.Busy
Loop

Set SearchBox = appIE.Document.getElementsByName("q")

If Not SearchBox Is Nothing Then
SearchBox(0).Value = "2eXtreme"
End If

End Sub

Step through the code and you can see how it creates a new IE window,
finds the textbox and inserts a value. If you needed the searchbox
result for a particular page, click View>Source and find the name of
the input box, then set a reference to it using "Set SearchBox =
appIE.Document.getElementsByName("input box name")" and extract the
value by assigning it to a variable, such as

Dim X As String
X = SearchBox(0).Value

Then you can insert it into a cell like this: Range("A1") = X

HTH,
JP
Click to expand...

Thanks very much for your reply, but when I try to run either section
of code I get:

Run-time error '91':

Object variable or With block variable not set

This error happens at the line

"X = SearchBox(0).Value"

Thanks again
 
Top