Source-Code - an old fashioned way/Some help please. 2 Source Code Scrape methods - want to combine

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
140
I need/want to get the red bit of this macro


Sub GH() Dim i As Long With CreateObject("MSXML2.XMLHTTP") .Open "GET", "http://www.google.com", False .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 5.1; rv:24.0) Gecko/20100101 Firefox/24.0" .send For i = 1 To Len(.responseText) Step 1023 Cells(i \ 1023 + 1, "A").Value = "'" & Mid(.responseText, i, 1023) Next End WithEnd Sub

'Into' the blue bit of this macro:

Option Explicit
Private Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000
Private Declare Function InternetOpen Lib "Wininet.dll" Alias "InternetOpenA" (ByVal lpszAgent As String, ByVal dwAccessType As Long, ByVal lpszProxyName As String, ByVal lpszProxyBypass As String, ByVal dwFlags As Long) As Long
Private Declare Function InternetReadFile Lib "Wininet.dll" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer
Private Declare Function InternetOpenUrl Lib "Wininet.dll" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal sUrl As String, ByVal sHeaders As String, ByVal lHeadersLength As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function InternetCloseHandle Lib "Wininet.dll" (ByVal hInet As Long) As Integer


Public Sub GetWebPageData()


Dim hInternet, hSession, lngDataReturned As Long
Dim iReadFileResult As Integer
Dim sBuffer As String * 64
Dim sTotalData As String
Dim sUrl As String
Dim sLine As String


sUrl = "http://www.engadget.com/" 'Long Website here
hSession = InternetOpen("", 0, vbNullString, vbNullString, 0)


If hSession Then hInternet = InternetOpenUrl(hSession, sUrl, vbNullString, 0, INTERNET_FLAG_NO_CACHE_WRITE, 0)


If hInternet Then
iReadFileResult = InternetReadFile(hInternet, sBuffer, 128, lngDataReturned)


sTotalData = sBuffer


Do While lngDataReturned <> 0
iReadFileResult = InternetReadFile(hInternet, sBuffer, 128, lngDataReturned)
sTotalData = sTotalData + Mid(sBuffer, 1, lngDataReturned)
Loop
End If


iReadFileResult = InternetCloseHandle(hInternet)


'WEBPAGE loaded into sTotalData
Cells(2, 2) = sTotalData
End Sub

Both Macro's work perfectly well. But I cant get the 1st one to work on my excelvba.

They extract Source-Code from a website into Excel.

The first one does it across as many cells as it takes to get the full source code pasted into excel.

The second one does it all into 1 cell, and so has the draw back that it cant get all the data into 1 cell.

The first one, I cant use (or seem to use). No matter how many Tools>References I enable. It says Access Denied on .Send

The second one, I can use. It's a joy to see source code tangibly pasted into a cell, but due to the memory limits of cells, it obviously cant paste all the source code of the page I need.

I like the second one. Its better imo. easier to understand, more close to home in a hands-on way. I could understand it better ... and no opening of IE required! and no malarki in terms of Java Script or XML/XMLCCMS PHP malarki which I hate!!

Im so old school.

I believe you can beat the computer with old fashinoned logical down to earth thinking.

Am I strange!?!?

Anyway, I truelly believe if we can re-jig the 2nd macro to do a

For i = 1 To Len(.responseText) Step 1023 Cells(i \ 1023 + 1, "A").Value = "'" & Mid(.responseText, i, 1023)
Next

It would work the same and have the same functionality as the 1st smaller macro.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Your first macro also fails for me. But it works if you change the URL from 'http://www.google.com' to the one in your second macro 'http://www.engadget.com/'.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,410
The second one, I can use. It's a joy to see source code tangibly pasted into a cell, but due to the memory limits of cells, it obviously cant paste all the source code of the page I need.
So use the For Next loop of the first, changing ".responseText" to "sTotalData".

PS use CODE tags when posting VBA code.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
Your fist one fails as google only works on https, not http
 

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
140

ADVERTISEMENT

I need/want to get the red bit of this macro


Sub GH() Dim i As Long With CreateObject("MSXML2.XMLHTTP") .Open "GET", "http://www.google.com", False .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 5.1; rv:24.0) Gecko/20100101 Firefox/24.0" .send For i = 1 To Len(.responseText) Step 1023 Cells(i \ 1023 + 1, "A").Value = "'" & Mid(.responseText, i, 1023) Next End WithEnd Sub

'Into' the blue bit of this macro:

Option Explicit
Private Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000
Private Declare Function InternetOpen Lib "Wininet.dll" Alias "InternetOpenA" (ByVal lpszAgent As String, ByVal dwAccessType As Long, ByVal lpszProxyName As String, ByVal lpszProxyBypass As String, ByVal dwFlags As Long) As Long
Private Declare Function InternetReadFile Lib "Wininet.dll" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer
Private Declare Function InternetOpenUrl Lib "Wininet.dll" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal sUrl As String, ByVal sHeaders As String, ByVal lHeadersLength As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function InternetCloseHandle Lib "Wininet.dll" (ByVal hInet As Long) As Integer


Public Sub GetWebPageData()


Dim hInternet, hSession, lngDataReturned As Long
Dim iReadFileResult As Integer
Dim sBuffer As String * 64
Dim sTotalData As String
Dim sUrl As String
Dim sLine As String


sUrl = "http://www.engadget.com/" 'Long Website here
hSession = InternetOpen("", 0, vbNullString, vbNullString, 0)


If hSession Then hInternet = InternetOpenUrl(hSession, sUrl, vbNullString, 0, INTERNET_FLAG_NO_CACHE_WRITE, 0)


If hInternet Then
iReadFileResult = InternetReadFile(hInternet, sBuffer, 128, lngDataReturned)


sTotalData = sBuffer


Do While lngDataReturned <> 0
iReadFileResult = InternetReadFile(hInternet, sBuffer, 128, lngDataReturned)
sTotalData = sTotalData + Mid(sBuffer, 1, lngDataReturned)
Loop
End If


iReadFileResult = InternetCloseHandle(hInternet)


'WEBPAGE loaded into sTotalData
Cells(2, 2) = sTotalData
End Sub

Both Macro's work perfectly well. But I cant get the 1st one to work on my excelvba.

They extract Source-Code from a website into Excel.

The first one does it across as many cells as it takes to get the full source code pasted into excel.

The second one does it all into 1 cell, and so has the draw back that it cant get all the data into 1 cell.

The first one, I cant use (or seem to use). No matter how many Tools>References I enable. It says Access Denied on .Send

The second one, I can use. It's a joy to see source code tangibly pasted into a cell, but due to the memory limits of cells, it obviously cant paste all the source code of the page I need.

I like the second one. Its better imo. easier to understand, more close to home in a hands-on way. I could understand it better ... and no opening of IE required! and no malarki in terms of Java Script or XML/XMLCCMS PHP malarki which I hate!!

Im so old school.

I believe you can beat the computer with old fashinoned logical down to earth thinking.

Am I strange!?!?

Anyway, I truelly believe if we can re-jig the 2nd macro to do a

For i = 1 To Len(.responseText) Step 1023 Cells(i \ 1023 + 1, "A").Value = "'" & Mid(.responseText, i, 1023)
Next

It would work the same and have the same functionality as the 1st smaller macro.

Your first macro also fails for me. But it works if you change the URL from 'http://www.google.com' to the one in your second macro 'http://www.engadget.com/'.

Andrew!!!!!!!!!!!!!!!

OM!!!! MANI!!! PADME!!! HUM!!!!!!

WOW THANK YOU!!!!!

Source code spread out 105 Cells = 1023*105 = 107415 characters!!!

It works. Let me try it with some websites I need to work on!! YES!!! It works for them too!!!

Contact | Honorable David Jolly, and the next page,

Zip Code Not Entered | Honorable David Jolly

BOW To all of you who helped me!!!

..... However .... 5 minutes later like an ungrateful sod ... yes, Its a Megga Big Issue for me that you guys have resolved!!! Wonder-Brilliant!!!

It works brilliantly ... Jesus, at least it works.

but ... ... Doesnt pick up the type of source code i need.

you know with the type that says: <div class="form-100"><label for="required-issue">

or, <div class="form-100"><label for="required-subject">Message Subject:</label>

which relate to the form fields.

That's what I need. ANYWAY, enough moaning from me for now!! Might work on it later myself .... But Thank you for your time and help/assistance with all of this!!! Im grateful beyond belief!!!!


p.s. WHY Didint the google.com one work? Is that because, do you think, as this massive evil corporation (people! Blekko is better, and Wolfram Alpha!) they are developing AI-Type stuff or programs to prevent this?

No, It's probably their database. All they need to do is lookup against a repository of all known stored VBA Macros (which they are against) and detect if the one weve used or the method weve used has employed that and hence block the return of any data, or simply negate it.

Anyway .... You MADE ME VERY HAPPY TODAY !! :) :d :D CHEESY-GRIN!!!

... need to figure out why not returning form-field elements javascript ...

next job tomorrow. :) :P <-- Pipe smoking 5-Infinite minutes of bliss

5:30. Home time!
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
Google doesn't work because you've got the wrong address
 

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
140

ADVERTISEMENT

oh dear, that didnt come out either..

I mean stuff like this
<div class="form-100"><label for="required-subject">Message Subject:</label> <input class="validate[required] is-required" id="required-subject" maxlength="100" name="required-subject" placeholder="Message Subject" /></div>

i did a crtnl+F for div class="form-100" accross the 48 cells the source code was pasted into, but no returns.

Will look at this again tomorrow ... but thank you thank you for your assistance, help, attention and valuable time into my thread!! Most Grateful n 'appy! ;D
 

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
140
What !!?!?

I pAsted <div class="form-100"><label for="required-subject">Message Subject:</label> <input class="validate[required] is-required" id="required-subject" maxlength="100" name="required-subject" placeholder="Message Subject" /></div>

<div class="form-100"><label for="required-subject">Message Subject:</label> <input class="validate[required] is-required" id="required-subject" maxlength="100" name="required-subject" placeholder="Message Subject" /></div>

i write out by hand:

<div class="form-100"><label for="required-subject">Message Subject:</label> - does that work?

ok. gotta go ! X Thanks!
 

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
140
oh, the **** this is code in itself, which seems to override textual fucntionality/ or act magically or more powerfully in the forum your write it in on a html page!?!?

a bit like a magic key, or when you want to write a smily face in a forum, you would put <Smile or something and it would magically post the smile.

but if we were writing in a normal text view editor (non-html), i bet it would come out as written. (source mode activated on text editor. lets see ...

ok ... attempt 3:

<div class="form-100"><label for="required-subject">Message Subject:</label> <input class="validate[required] is-required" id="required-subject" maxlength="100" name="required-subject" placeholder="Message Subject" /></div>


need to get around this magic?

another day.

Thank you guys for your help - mucho gracias
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,290
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top