Need String As Range & Need Contents of Cell Developed Into This Code

drmingle

Board Regular
Joined
Oct 5, 2009
Messages
229
So this code works fine (it was provided by another member here at Mr.Excel.com):

Code:
Sub GetTextOrHtmlFromIe()
'Standard module code, like: Module1.
Dim bln As Boolean
Dim NPOFile$, myFile$
Dim ie As Object, objDoc As Object, f As Object, fs As Object
Const strMsg As String = "To get a text version of your page, click [Yes]," & vbLf & _
"To get the Html version, click [No]"
Const strURI As String = "[URL]http://www.google.com/#sclient=psy&hl=en&safe=active&source=hp&q=site:+squidoo.com+%22space[/URL] saver treadmill%22&aq=f&aqi=&aql=&oq=&pbx=1&bav=on.2,or.r_gc.r_pw.&fp=d69918eb06677aa8"
Set ie = CreateObject("internetexplorer.application")
ie.Navigate strURI
'Wait for page to load!
Do
If ie.ReadyState = 4 Then
ie.Visible = False
Exit Do
Else
DoEvents
End If
Loop
Set objDoc = ie.Document
If MsgBox(strMsg, vbInformation + vbYesNo, "Display page!") = vbYes Then bln = True
If bln Then
'Text
strMyPage = objDoc.getElementById("resultStats").innerText
Else
'Html
strMyPage = objDoc.getElementById("resultStats").innerHTML
End If
'Use this Text file!
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
End If
myFile = fileToOpen
NPOFile = "NotePad.exe " & myFile
'Work with file [Append Text to File].
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(myFile, 8, TristateUseDefault)
f.Write Chr(9) & strMyPage
f.Close
'Open NotePad with a data file!
ActiveSheet.Select
Call Shell(NPOFile, 1)
Set objDoc = Nothing
Set ie = Nothing
End Sub

However, I am needing to make two adjustments which I need help with:
  1. Const strURI As String = [needs to be a value contained within A2/Sheet1 - instead of hard coded into VBA]
  2. f.Write Chr(9) & strMyPage [needs to be a value contained within B2/Sheet1- instead of results URL only]. I thought this might look like f.Write Chr(9) & Sheet1.Range(B2) &strMyPage
Any help would be appreciated...
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top