Transferring HTML form data to a spreadsheet?

[Anon]

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!

 

[Anon]

> 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!

*bump*

Can anyone tell me if this is even possible? Please

 

[JP]

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

--JP

> 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

 

[Anon]

> Do you want the URL? Or do you want the body of the web page result

> after you click Search?

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!

 

[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

>

> 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
> 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
> 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

 

[Anon]

> 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
> 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
> ' loop code until page is fully loaded

> Do While appIE.Busy
> Set SearchBox = appIE.Document.getElementsByName("q")
> If Not SearchBox Is Nothing Then

> SearchBox(0).Value = "2eXtreme"
> 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
> ' loop code until page is fully loaded

> Do While appIE.Busy
> Set SearchBox = appIE.Document.getElementsByName("q")
> If Not SearchBox Is Nothing Then

> SearchBox(0).Value = "2eXtreme"
> 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,

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!

 

[Anon]

> 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
> 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
> ' loop code until page is fully loaded

> Do While appIE.Busy
> Set SearchBox = appIE.Document.getElementsByName("q")
> If Not SearchBox Is Nothing Then

> SearchBox(0).Value = "2eXtreme"
> 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
> ' loop code until page is fully loaded

> Do While appIE.Busy
> Set SearchBox = appIE.Document.getElementsByName("q")
> If Not SearchBox Is Nothing Then

> SearchBox(0).Value = "2eXtreme"
> 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,

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

 

[JP]

Can you post the code exactly as you are using it?

Thx,
JP