Copy data from HTML
up vote
1
down vote
favorite
I am trying to learn how to parse data from HTML using Excel VBA. So I found one example online which works fine but when I change URL address from www.yahoo.com to local HTML file on C it gives me error i.e. Method 'busy' of object 'IwebBrowser2' failed. Code is:
Sub GetBodyText()
Dim URL As String
Dim Data As String
URL = "file:///C:/test.html"
Dim ie As Object
Dim ieDoc As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate URL
Do Until (ie.readyState = 4 And Not ie.Busy)
DoEvents
Loop
Set ieDoc = ie.Document
Data = ieDoc.body.innerText
'Split Data into separate lines
'or just use Range("A1")=data
Dim myarray As Variant
myarray = Split(Data, vbCrLf)
For i = 0 To UBound(myarray)
'Start writing in cell A1
Cells(i + 1, 1) = myarray(i)
Next
ie.Quit
Set ie = Nothing
Set ieDoc = Nothing
End Sub
html excel parsing vba
edited Sep 23 '15 at 5:00
pnuts
46.4k76195
asked Sep 13 '13 at 14:52
user2774120
2327
add a comment |
up vote
1
down vote
favorite
I am trying to learn how to parse data from HTML using Excel VBA. So I found one example online which works fine but when I change URL address from www.yahoo.com to local HTML file on C it gives me error i.e. Method 'busy' of object 'IwebBrowser2' failed. Code is:
Sub GetBodyText()
Dim URL As String
Dim Data As String
URL = "file:///C:/test.html"
Dim ie As Object
Dim ieDoc As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate URL
Do Until (ie.readyState = 4 And Not ie.Busy)
DoEvents
Loop
Set ieDoc = ie.Document
Data = ieDoc.body.innerText
'Split Data into separate lines
'or just use Range("A1")=data
Dim myarray As Variant
myarray = Split(Data, vbCrLf)
For i = 0 To UBound(myarray)
'Start writing in cell A1
Cells(i + 1, 1) = myarray(i)
Next
ie.Quit
Set ie = Nothing
Set ieDoc = Nothing
End Sub
html excel parsing vba
edited Sep 23 '15 at 5:00
pnuts
46.4k76195
asked Sep 13 '13 at 14:52
user2774120
2327
1
Will your final result be parsing a file or parsing a page? You can pull the data from the file or the web page without loading an InternetExplorer.Application object.
– Tom Hines
Jun 2 '17 at 18:56
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am trying to learn how to parse data from HTML using Excel VBA. So I found one example online which works fine but when I change URL address from www.yahoo.com to local HTML file on C it gives me error i.e. Method 'busy' of object 'IwebBrowser2' failed. Code is:
Sub GetBodyText()
Dim URL As String
Dim Data As String
URL = "file:///C:/test.html"
Dim ie As Object
Dim ieDoc As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate URL
Do Until (ie.readyState = 4 And Not ie.Busy)
DoEvents
Loop
Set ieDoc = ie.Document
Data = ieDoc.body.innerText
'Split Data into separate lines
'or just use Range("A1")=data
Dim myarray As Variant
myarray = Split(Data, vbCrLf)
For i = 0 To UBound(myarray)
'Start writing in cell A1
Cells(i + 1, 1) = myarray(i)
Next
ie.Quit
Set ie = Nothing
Set ieDoc = Nothing
End Sub
html excel parsing vba
edited Sep 23 '15 at 5:00
pnuts
46.4k76195
asked Sep 13 '13 at 14:52
user2774120
2327
I am trying to learn how to parse data from HTML using Excel VBA. So I found one example online which works fine but when I change URL address from www.yahoo.com to local HTML file on C it gives me error i.e. Method 'busy' of object 'IwebBrowser2' failed. Code is:
Sub GetBodyText()
Dim URL As String
Dim Data As String
URL = "file:///C:/test.html"
Dim ie As Object
Dim ieDoc As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate URL
Do Until (ie.readyState = 4 And Not ie.Busy)
DoEvents
Loop
Set ieDoc = ie.Document
Data = ieDoc.body.innerText
'Split Data into separate lines
'or just use Range("A1")=data
Dim myarray As Variant
myarray = Split(Data, vbCrLf)
For i = 0 To UBound(myarray)
'Start writing in cell A1
Cells(i + 1, 1) = myarray(i)
Next
ie.Quit
Set ie = Nothing
Set ieDoc = Nothing
End Sub
html excel parsing vba
html excel parsing vba
edited Sep 23 '15 at 5:00
pnuts
46.4k76195
asked Sep 13 '13 at 14:52
user2774120
2327
edited Sep 23 '15 at 5:00
pnuts
46.4k76195
asked Sep 13 '13 at 14:52
user2774120
2327
edited Sep 23 '15 at 5:00
pnuts
46.4k76195
edited Sep 23 '15 at 5:00
pnuts
46.4k76195
edited Sep 23 '15 at 5:00
pnuts
46.4k76195
46.4k76195
asked Sep 13 '13 at 14:52
user2774120
2327
asked Sep 13 '13 at 14:52
user2774120
2327
asked Sep 13 '13 at 14:52
user2774120
2327
2327
1
Will your final result be parsing a file or parsing a page? You can pull the data from the file or the web page without loading an InternetExplorer.Application object.
– Tom Hines
Jun 2 '17 at 18:56
add a comment |
1
Will your final result be parsing a file or parsing a page? You can pull the data from the file or the web page without loading an InternetExplorer.Application object.
– Tom Hines
Jun 2 '17 at 18:56
1
1
Will your final result be parsing a file or parsing a page? You can pull the data from the file or the web page without loading an InternetExplorer.Application object.
– Tom Hines
Jun 2 '17 at 18:56
Will your final result be parsing a file or parsing a page? You can pull the data from the file or the web page without loading an InternetExplorer.Application object.
– Tom Hines
Jun 2 '17 at 18:56
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
For IE, just use:
URL = "c:test.html"
answered Sep 13 '13 at 14:57
Joe
6,02711025
Hi Thanks, But i am still getting same error. Is it because there is prompt for "Enable Active x" when i open local html file ?
– user2774120
Sep 13 '13 at 15:03
Possibly, but it worked for me. Try this: just beforeie.navigate URL, addie.visible=true. This will actually display the IE window, so it should show you what's holding it up.
– Joe
Sep 13 '13 at 15:10
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
For IE, just use:
URL = "c:test.html"
answered Sep 13 '13 at 14:57
Joe
6,02711025
Hi Thanks, But i am still getting same error. Is it because there is prompt for "Enable Active x" when i open local html file ?
– user2774120
Sep 13 '13 at 15:03
Possibly, but it worked for me. Try this: just beforeie.navigate URL, addie.visible=true. This will actually display the IE window, so it should show you what's holding it up.
– Joe
Sep 13 '13 at 15:10
add a comment |
up vote
0
down vote
For IE, just use:
URL = "c:test.html"
answered Sep 13 '13 at 14:57
Joe
6,02711025
Hi Thanks, But i am still getting same error. Is it because there is prompt for "Enable Active x" when i open local html file ?
– user2774120
Sep 13 '13 at 15:03
Possibly, but it worked for me. Try this: just beforeie.navigate URL, addie.visible=true. This will actually display the IE window, so it should show you what's holding it up.
– Joe
Sep 13 '13 at 15:10
add a comment |
up vote
0
down vote
up vote
0
down vote
For IE, just use:
URL = "c:test.html"
answered Sep 13 '13 at 14:57
Joe
6,02711025
For IE, just use:
URL = "c:test.html"
answered Sep 13 '13 at 14:57
Joe
6,02711025
answered Sep 13 '13 at 14:57
Joe
6,02711025
answered Sep 13 '13 at 14:57
Joe
6,02711025
answered Sep 13 '13 at 14:57
Joe
6,02711025
6,02711025
Hi Thanks, But i am still getting same error. Is it because there is prompt for "Enable Active x" when i open local html file ?
– user2774120
Sep 13 '13 at 15:03
Possibly, but it worked for me. Try this: just beforeie.navigate URL, addie.visible=true. This will actually display the IE window, so it should show you what's holding it up.
– Joe
Sep 13 '13 at 15:10
add a comment |
Hi Thanks, But i am still getting same error. Is it because there is prompt for "Enable Active x" when i open local html file ?
– user2774120
Sep 13 '13 at 15:03
Possibly, but it worked for me. Try this: just beforeie.navigate URL, addie.visible=true. This will actually display the IE window, so it should show you what's holding it up.
– Joe
Sep 13 '13 at 15:10
Hi Thanks, But i am still getting same error. Is it because there is prompt for "Enable Active x" when i open local html file ?
– user2774120
Sep 13 '13 at 15:03
Hi Thanks, But i am still getting same error. Is it because there is prompt for "Enable Active x" when i open local html file ?
– user2774120
Sep 13 '13 at 15:03
Possibly, but it worked for me. Try this: just before
ie.navigate URL, add ie.visible=true. This will actually display the IE window, so it should show you what's holding it up.– Joe
Sep 13 '13 at 15:10
Possibly, but it worked for me. Try this: just before
ie.navigate URL, add ie.visible=true. This will actually display the IE window, so it should show you what's holding it up.– Joe
Sep 13 '13 at 15:10
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f18789316%2fcopy-data-from-html%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Will your final result be parsing a file or parsing a page? You can pull the data from the file or the web page without loading an InternetExplorer.Application object.
– Tom Hines
Jun 2 '17 at 18:56