• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Enter values from cell into web page, hit button and import source code as text/html in sheet

S

Samadhan Gaikwad

Member
Hi,
I have created hyperlinks from source code by manually adding text in textarea -> Hit button and save source code of web page as html or text file and then import it from some location and create hyperlinks from them.

So to avoid this manual intervention, can some help me to to automate this.
Following are steps for vba macro:
1. Go to web page: <https://tiweb.industrysoftware.automation..com/prdata/cgi-bin/n_prdata_index.cgi>
2. Add text into textarea from Sheet2 and cell I1:
HTML: 
<tr>
</td>
<tr>
</td>
<tr></tr>
<td align=center style="border:solid 1px #060; border-right: none">
<b>
Search and Download for Single or Multiple PRs/ERs/IRs
</b>
<p>
<font size=-1>
(separate by commas)
</font>
</td>
<td style="border:solid 1px #060; border-left: none">
<textarea name="pr_numbers" rows="5" cols="22" >
</textarea>
<br>
<button onClick="return check_mvsub(this)">
<br>
<b>
Search and Download
</b>
<br>
</button>
</td>
</tr>
3. Hit "Search and Download" button.
4. Import source code of displayed page into Sheet1.

Thanks in advance!
 

Attachments

  • Capture.PNG
    Capture.PNG
    7.3 KB · Views: 5
Sorry, this is internal link and I should not be able to make it available. Just for understanding type of page, I have shared (incorrect) link. And for clarification I have shared the code which will be needed to look into (I think). pr_numbers is the id of textarea where numbers will be added into.
Please let me know if any more info. needed.
 
Samadhan Gaikwad said:
Sorry, this is internal link and I should not be able to make it available. Just for understanding type of page, I have shared (incorrect) link. And for clarification I have shared the code which will be needed to look into (I think). pr_numbers is the id of textarea where numbers will be added into.
Please let me know if any more info. needed.
Click to expand...
I see... I will pick this up as soon as I can an try to work something out.
 
Hi,

Try the following:
Code: 
Sub GetSourceCode()

    Dim ie As Object
    Dim str As String
    Dim arr

    str = Sheets("sheet2").Range("I1").Value
    Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
  
    ie.Navigate "https://tiweb.industrysoftware.automation..com/prdata/cgi-bin/n_prdata_index.cgi"
    ie.Visible = True

    Do Until ie.readyState = 4
        DoEvents
    Loop
  
    ie.document.getelementsbyname("pr_numbers")(0).Value = str
    Application.SendKeys ("~")
  
    Do Until ie.readyState = 4
        DoEvents
    Loop

    arr = Split(ie.document.body.innerHTML, vbLf)
  
    ActiveSheet.Range("A1").Resize(UBound(arr) + 1, 1).Value = Application.Transpose(arr)

End Sub

It will probably fail this first time but, since I can't test it, we will go from there.
 
Wow, it's working fine! Just to not show page, I made it hidden.
I would like to import all source code as it is without truncating it or deleting any text.
 
Yes.
I want code from start of the page:
Code: 
<!DOCTYPE html >
<html lang="en">
<head>

<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

What I am getting at start is:
Code: 
<div id="top">
<span style="float: right;">Main Page</span>
PR / ER Data 1.4
</div>
 
Hi,

I believe that ".innerHTML" will differ slightly from what you get from the browser if you select to view source code.
I may be wrong but that is how it is.
Have you confirmed if the rest is ok... you can always try ".outerHTML" to see if the result is what you want.
 
Try using .responseText instead of .innerHTML.

EDIT: Woops, my bad I thought you were using XML.
 
Last edited:
Code: 
Private Sub HTML_VBA_Excel()
Dim oXMLHTTP As Object
Dim sPageHTML As String
Dim sURL As String

'Change the URL before executing the code
sURL = "https://tiweb.industrysoftware.automation.com/prdata/cgi-bin/n_prdata_index.cgi"

'Extract data from website to Excel using VBA
Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
oXMLHTTP.Open "GET", sURL, False
oXMLHTTP.send
sPageHTML = oXMLHTTP.responseText

'Get webpage data into Excel
' If longer sourcecode mean, you need to save to a external text file or somewhere,
' since excel cell have some limits on storing max characters


Createtextfile (sPageHTML)

End Sub
Sub Createtextfile(sPageHTML)

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
strPath = "C:\Users\a3rgcw\Downloads\7956351\test.txt"
Set oFile = fso.Createtextfile(strPath)
oFile.WriteLine sPageHTML
oFile.Close
Set fso = Nothing
Set oFile = Nothing

End Sub

I have got above code which saves web page source code to dir as a text file. Can someone help me to improve code further to add text to web page from sheet and save source code of displayed web page.
 
You must log in or register to reply here.
Back
Top