Code to get data from webpage into Excel
in [Programming]
|
Prev: SuDoku
Next: Request help
From: donbowyer on 9 Nov 2005 11:49 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
From: Mark H. Shin on 9 Nov 2005 16:36 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" <donbowyer(a)discussions.microsoft.com> wrote in message news:EA516D7E-0167-44C6-9AB5-FDEBC505F8D5(a)microsoft.com... >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
From: donbowyer on 9 Nov 2005 20:17 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" <donbowyer(a)discussions.microsoft.com> wrote in message > news:EA516D7E-0167-44C6-9AB5-FDEBC505F8D5(a)microsoft.com... > >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 > > >
From: Mark H. Shin on 10 Nov 2005 01:55 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" <donbowyer(a)discussions.microsoft.com> wrote in message news:1321F9E6-7533-4EE5-9926-1FEFEB03E902(a)microsoft.com... > 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" <donbowyer(a)discussions.microsoft.com> wrote in message >> news:EA516D7E-0167-44C6-9AB5-FDEBC505F8D5(a)microsoft.com... >> >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 >> >> >>
From: donbowyer on 10 Nov 2005 14:32 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" <donbowyer(a)discussions.microsoft.com> wrote in message > news:1321F9E6-7533-4EE5-9926-1FEFEB03E902(a)microsoft.com... > > 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" <donbowyer(a)discussions.microsoft.com> wrote in message > >> news:EA516D7E-0167-44C6-9AB5-FDEBC505F8D5(a)microsoft.com... > >> >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 > >> > >> > >> > > >
|
Pages: 1 Prev: SuDoku Next: Request help |