Code to get data from webpage into Excel

I am trying to programmatically download, copy, import or any other way
cajole the contents of a specific web page onto an Excel Worksheet.

I have previously had some discussion with Dysgraphia so if he's still out
there apologies for this more specific re-posting.

I have tried:-
Dim webBk As Workbook
Set webBk=Workbooks.Open("http://www.somewebpage.com)

But I get a runtime error message saying MS Access cannot access the file
"http://www.somewebpage.com" because either it doesn't exist or is being used
by another program – neither of which is true. However the website does
require a UserName & Password and I think this is where this method falls
down.

So then I have tried:-
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.Navigate "https://www.somewebsite.com"
End With

This brings up the site, and I can then manually enter the security details
and get to the required page. But I don't then know the code needed to
transfer it to Excel.

Also is there any programatical way round the problem of having to log in to
the site. I believe Excel provides the means of using a Personal Digital
Signature, but haven't a clue where to get one. This application is all
private.
A
ny suggestions would be most welcome.
--
donwb

Add a reference to "Microsoft HTML Object Library" to your VBA project then
try the following code (change yourURL to the desired web page):

Sub mshtmltest()
Dim objMSHTML As New MSHTML.HTMLDocument
Dim objDocument As MSHTML.HTMLDocument
Dim E As MSHTML.HTMLGenericElement

Set objDocument = objMSHTML.createDocumentFromUrl(yourURL, vbNullString)

While objDocument.readyState <> "complete"
DoEvents
Wend

For Each E In objDocument.all
If (E.tagName = "TD") Then
Debug.Print E.innerHTML
End If
Next
End Sub

As you can see, you can filter down to the HTML element level. In the above
example, I have filtered only "TD" elements. It would be even easier if
your table had a name or id. Then you can use:

objDocument.getElementById

or

objDocument.getElementByName

methods to directly access the HTML element that contains your data.

"donbowyer" <[email protected]> wrote in message
news:[email protected]...
>I am trying to programmatically download, copy, import or any other way
> cajole the contents of a specific web page onto an Excel Worksheet.
>
> I have previously had some discussion with Dysgraphia so if he's still out
> there apologies for this more specific re-posting.
>
> I have tried:-
> Dim webBk As Workbook
> Set webBk=Workbooks.Open("http://www.somewebpage.com)
>
> But I get a runtime error message saying MS Access cannot access the file
> "http://www.somewebpage.com" because either it doesn't exist or is being
> used
> by another program - neither of which is true. However the website does
> require a UserName & Password and I think this is where this method falls
> down.
>
> So then I have tried:-
> Set IE = CreateObject("InternetExplorer.Application")
> With IE
> .Visible = True
> .Navigate "https://www.somewebsite.com"
> End With
>
> This brings up the site, and I can then manually enter the security
> details
> and get to the required page. But I don't then know the code needed to
> transfer it to Excel.
>
> Also is there any programatical way round the problem of having to log in
> to
> the site. I believe Excel provides the means of using a Personal Digital
> Signature, but haven't a clue where to get one. This application is all
> private.
> A
> ny suggestions would be most welcome.
> --
> donwb

Thanks for the input Mark.
I'm ok down to the end of the Do Events loop.
Then, instead of extracting specific data from the resulting objDocument
(because it is too difficult - for me - to define specifics) , what I would
like to do is programatically put the whole objDocument onto a Worksheet as
if I had gone to the web page, selected all, copied all then pasted it.
--
donwb


"Mark H. Shin" wrote:

> Add a reference to "Microsoft HTML Object Library" to your VBA project then
> try the following code (change yourURL to the desired web page):
>
> Sub mshtmltest()
> Dim objMSHTML As New MSHTML.HTMLDocument
> Dim objDocument As MSHTML.HTMLDocument
> Dim E As MSHTML.HTMLGenericElement
>
> Set objDocument = objMSHTML.createDocumentFromUrl(yourURL, vbNullString)
>
> While objDocument.readyState <> "complete"
> DoEvents
> Wend
>
> For Each E In objDocument.all
> If (E.tagName = "TD") Then
> Debug.Print E.innerHTML
> End If
> Next
> End Sub
>
> As you can see, you can filter down to the HTML element level. In the above
> example, I have filtered only "TD" elements. It would be even easier if
> your table had a name or id. Then you can use:
>
> objDocument.getElementById
>
> or
>
> objDocument.getElementByName
>
> methods to directly access the HTML element that contains your data.
>
> "donbowyer" <[email protected]> wrote in message
> news:[email protected]...
> >I am trying to programmatically download, copy, import or any other way
> > cajole the contents of a specific web page onto an Excel Worksheet.
> >
> > I have previously had some discussion with Dysgraphia so if he's still out
> > there apologies for this more specific re-posting.
> >
> > I have tried:-
> > Dim webBk As Workbook
> > Set webBk=Workbooks.Open("http://www.somewebpage.com)
> >
> > But I get a runtime error message saying MS Access cannot access the file
> > "http://www.somewebpage.com" because either it doesn't exist or is being
> > used
> > by another program - neither of which is true. However the website does
> > require a UserName & Password and I think this is where this method falls
> > down.
> >
> > So then I have tried:-
> > Set IE = CreateObject("InternetExplorer.Application")
> > With IE
> > .Visible = True
> > .Navigate "https://www.somewebsite.com"
> > End With
> >
> > This brings up the site, and I can then manually enter the security
> > details
> > and get to the required page. But I don't then know the code needed to
> > transfer it to Excel.
> >
> > Also is there any programatical way round the problem of having to log in
> > to
> > the site. I believe Excel provides the means of using a Personal Digital
> > Signature, but haven't a clue where to get one. This application is all
> > private.
> > A
> > ny suggestions would be most welcome.
> > --
> > donwb
>
>
>

You can past the HTML code onto your sheet, but Excel is not the best
program to render HTML onto a spreadsheet (unless you insert a browser
object onto your sheet). You can paste the text of the BODY element into a
cell on your sheet.

I think it might be helpful if you could describe the layout of the page you
are trying to paste onto a worksheet.

If it is simple text, you can use the following (after the Wend statement):

ActiveSheet.Cells(1, "A").Value = objDocument.body.innerText



"donbowyer" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the input Mark.
> I'm ok down to the end of the Do Events loop.
> Then, instead of extracting specific data from the resulting objDocument
> (because it is too difficult - for me - to define specifics) , what I
> would
> like to do is programatically put the whole objDocument onto a Worksheet
> as
> if I had gone to the web page, selected all, copied all then pasted it.
> --
> donwb
>
>
> "Mark H. Shin" wrote:
>
>> Add a reference to "Microsoft HTML Object Library" to your VBA project
>> then
>> try the following code (change yourURL to the desired web page):
>>
>> Sub mshtmltest()
>> Dim objMSHTML As New MSHTML.HTMLDocument
>> Dim objDocument As MSHTML.HTMLDocument
>> Dim E As MSHTML.HTMLGenericElement
>>
>> Set objDocument = objMSHTML.createDocumentFromUrl(yourURL,
>> vbNullString)
>>
>> While objDocument.readyState <> "complete"
>> DoEvents
>> Wend
>>
>> For Each E In objDocument.all
>> If (E.tagName = "TD") Then
>> Debug.Print E.innerHTML
>> End If
>> Next
>> End Sub
>>
>> As you can see, you can filter down to the HTML element level. In the
>> above
>> example, I have filtered only "TD" elements. It would be even easier if
>> your table had a name or id. Then you can use:
>>
>> objDocument.getElementById
>>
>> or
>>
>> objDocument.getElementByName
>>
>> methods to directly access the HTML element that contains your data.
>>
>> "donbowyer" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am trying to programmatically download, copy, import or any other way
>> > cajole the contents of a specific web page onto an Excel Worksheet.
>> >
>> > I have previously had some discussion with Dysgraphia so if he's still
>> > out
>> > there apologies for this more specific re-posting.
>> >
>> > I have tried:-
>> > Dim webBk As Workbook
>> > Set webBk=Workbooks.Open("http://www.somewebpage.com)
>> >
>> > But I get a runtime error message saying MS Access cannot access the
>> > file
>> > "http://www.somewebpage.com" because either it doesn't exist or is
>> > being
>> > used
>> > by another program - neither of which is true. However the website does
>> > require a UserName & Password and I think this is where this method
>> > falls
>> > down.
>> >
>> > So then I have tried:-
>> > Set IE = CreateObject("InternetExplorer.Application")
>> > With IE
>> > .Visible = True
>> > .Navigate "https://www.somewebsite.com"
>> > End With
>> >
>> > This brings up the site, and I can then manually enter the security
>> > details
>> > and get to the required page. But I don't then know the code needed to
>> > transfer it to Excel.
>> >
>> > Also is there any programatical way round the problem of having to log
>> > in
>> > to
>> > the site. I believe Excel provides the means of using a Personal
>> > Digital
>> > Signature, but haven't a clue where to get one. This application is all
>> > private.
>> > A
>> > ny suggestions would be most welcome.
>> > --
>> > donwb
>>
>>
>>

Thanks Mark,
The webpage I wish to put onto a WorkSheet is itself formatted as a table.
So it renders well into Excel when I manually copy and paste it and sort of
"self parses". I can then clean it up easily with simple code to get just
what I need.
What I would like to do is programmatically automate the equivalent of a
copy and paste on the WHOLE page.

Your suggestion:-
ActiveSheet.Cells(1, "A").Value = objDocument.body.innerText

would be ideal if it could select the WHOLE objDocument, not just parts ie
"InnerText".
I'm not sure if this is possible: if not, is there some way to say
With objDocument
.Select All
.Copy
End With
Thanks

--
donwb


"Mark H. Shin" wrote:

> You can past the HTML code onto your sheet, but Excel is not the best
> program to render HTML onto a spreadsheet (unless you insert a browser
> object onto your sheet). You can paste the text of the BODY element into a
> cell on your sheet.
>
> I think it might be helpful if you could describe the layout of the page you
> are trying to paste onto a worksheet.
>
> If it is simple text, you can use the following (after the Wend statement):
>
> ActiveSheet.Cells(1, "A").Value = objDocument.body.innerText
>
>
>
> "donbowyer" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the input Mark.
> > I'm ok down to the end of the Do Events loop.
> > Then, instead of extracting specific data from the resulting objDocument
> > (because it is too difficult - for me - to define specifics) , what I
> > would
> > like to do is programatically put the whole objDocument onto a Worksheet
> > as
> > if I had gone to the web page, selected all, copied all then pasted it.
> > --
> > donwb
> >
> >
> > "Mark H. Shin" wrote:
> >
> >> Add a reference to "Microsoft HTML Object Library" to your VBA project
> >> then
> >> try the following code (change yourURL to the desired web page):
> >>
> >> Sub mshtmltest()
> >> Dim objMSHTML As New MSHTML.HTMLDocument
> >> Dim objDocument As MSHTML.HTMLDocument
> >> Dim E As MSHTML.HTMLGenericElement
> >>
> >> Set objDocument = objMSHTML.createDocumentFromUrl(yourURL,
> >> vbNullString)
> >>
> >> While objDocument.readyState <> "complete"
> >> DoEvents
> >> Wend
> >>
> >> For Each E In objDocument.all
> >> If (E.tagName = "TD") Then
> >> Debug.Print E.innerHTML
> >> End If
> >> Next
> >> End Sub
> >>
> >> As you can see, you can filter down to the HTML element level. In the
> >> above
> >> example, I have filtered only "TD" elements. It would be even easier if
> >> your table had a name or id. Then you can use:
> >>
> >> objDocument.getElementById
> >>
> >> or
> >>
> >> objDocument.getElementByName
> >>
> >> methods to directly access the HTML element that contains your data.
> >>
> >> "donbowyer" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am trying to programmatically download, copy, import or any other way
> >> > cajole the contents of a specific web page onto an Excel Worksheet.
> >> >
> >> > I have previously had some discussion with Dysgraphia so if he's still
> >> > out
> >> > there apologies for this more specific re-posting.
> >> >
> >> > I have tried:-
> >> > Dim webBk As Workbook
> >> > Set webBk=Workbooks.Open("http://www.somewebpage.com)
> >> >
> >> > But I get a runtime error message saying MS Access cannot access the
> >> > file
> >> > "http://www.somewebpage.com" because either it doesn't exist or is
> >> > being
> >> > used
> >> > by another program - neither of which is true. However the website does
> >> > require a UserName & Password and I think this is where this method
> >> > falls
> >> > down.
> >> >
> >> > So then I have tried:-
> >> > Set IE = CreateObject("InternetExplorer.Application")
> >> > With IE
> >> > .Visible = True
> >> > .Navigate "https://www.somewebsite.com"
> >> > End With
> >> >
> >> > This brings up the site, and I can then manually enter the security
> >> > details
> >> > and get to the required page. But I don't then know the code needed to
> >> > transfer it to Excel.
> >> >
> >> > Also is there any programatical way round the problem of having to log
> >> > in
> >> > to
> >> > the site. I believe Excel provides the means of using a Personal
> >> > Digital
> >> > Signature, but haven't a clue where to get one. This application is all
> >> > private.
> >> > A
> >> > ny suggestions would be most welcome.
> >> > --
> >> > donwb
> >>
> >>
> >>
>
>
>