Can a macro read a list and insert the names into a web address?

[Darren]

I want to know if a macro can read a list of names (can be from 10 - 100
long) from 1 sheet and copy each name individually into a web address. Then
place the results onto another sheet at pre determined places.
Heres what I have:
On Sheet xp from cell B3 is the list of names
b
3 Thrasherfan
4 Mummybear58
3 Galadriel107

I want to read each name, 1 at a time and place them into:
http://hiscore.runescape.com/index_lite.ws?player=*Insert name here*

Then place that information onto:\
Sheet Team 1, cell B1 for 1st name
Sheet Team 1, cell B41 for 2nd name
Adding 40 cells for each consecutive name. So name 3 would be B81 etc.

Is this possible, and if so, how would I go about it?

 

[hirandesilva]

Darren,

For iRow = 10 to 100

strURL = "http://hiscore.runescape.com/index_lite.ws?player=" + str
(Sheets("xp").Cells(iRow,2).Value

Sheets("Team1").Cells((iRow-9)*40+1,2).Value = strURL

Next iRow

Note: if the 1st row is 20 then replace the 9 with 19.

Hiran

 

[paul.robinson]

Hi
Step1: Select your list of names with the mouse (no heading row) and
in the name box (just above the column A heading) type Webnames and
press return. This names that data range.
I'll assume the sheet you have the names on is called "My Names" and
you want to put the info on the sheet called "Team 1". Change as
required.

Step 2: Open the visual basic editor by pressing Alt+F11. You should
see VBA Project(Your workbook name) on the left hand side. Make sure
it is highlighted then do Insert, Module.

Step 3: Paste in this code

Sub MakeWebNames()
Dim WN As Range, OneName As Range
Dim Namerow As Long
Set WN = Worksheets("My Names").Range("Webnames")
Namerow = 1
With Worksheets("Team 1")
For Each OneName In WN
.Hyperlinks.Add Anchor:=.Cells(Namerow, 2), Address:= _
"http://hiscore.runescape.com/index_lite.ws?player=" &
OneName, _
TextToDisplay:="http://hiscore.runescape.com/index_lite.ws?
player=" & OneName
Namerow = Namerow + 40
Next OneName
.Columns("B").AutoFit
End With
Set WN = Nothing
End Sub

Careful with the line wrapping - it needs to look like above. You may
need to change sheet names.

Step 4: Run the macro. Go back to excel and do Tools, Macro,
Macros...Select the macro and run it.

I use Excel 2003 and XP. If you have Excel 2007 running a macro is a
bit different in terms of where it is.
regards
Paul

 

[paul.robinson]

Sorry, your browser might wrap text (mine did). Lines with " _" on the
end need to finish with those characters, not wrap to the next line.
regards
Paul

 

[Matthew Herbert]

Darren,

I put the code together rather quickly; it's not tested, but it should at
least give you an idea of the syntax you can use to piece together what you
need. (You'll have to debug the code, and using F8 (i.e. Step Into) is a
good way to debug the code. Also, I used only one parameter for
..TextToColumns, which is a very poor assumption). The code assumes that the
return text is separated by a line feed characer (i.e.
vbLf/Chr(10)/CHAR(10)), the text is delimited by a comma, the names are
listed in worksheet 1, and the output is listed in worksheet 2.

Best,

Matthew Herbert

Sub CustomData()
Dim rngCell As Range
Dim rngList As Range
Dim strRes As String
Dim varArr As Variant
Dim rngOut As Range
Dim rngPaste As Range
Dim rngData As Range
Dim lngCnt As Long
Const c_intOffset As Integer = 39
Const c_strBaseURL As String =
"http://hiscore.runescape.com/index_lite.ws?player="

With ThisWorkbook
Set rngList = .Worksheets(1).Range("B3")
Set rngList = Range(rngList, rngList.End(xlDown))
Set rngOut = .Worksheets(2).Range("B1")
End With

lngCnt = 0
For Each rngCell In rngList.Cells
strRes = GetXMLHTTP(c_strBaseURL & rngCell.Value)
If strRes <> "" Then
varArr = Split(strRes, vbLf)

Set rngPaste = rngOut.Offset(lngCnt * c_intOffset, 0)

Set rngData = Range(rngPaste, _
rngPaste.Offset(UBound(varArr), 0))

rngData.Value = Application.Transpose(varArr)

rngData.TextToColumns Comma:=True

lngCnt = lngCnt + 1
End If
Next rngCell
End Sub

Function GetXMLHTTP(strURL As String) As String

Dim objXMLHTTP As Object
Dim strText As String

Set objXMLHTTP = CreateObject("Microsoft.XMLHTTP")

If strURL = "" Then
GetXMLHTTP = ""
Exit Function
End If

With objXMLHTTP
.Open "GET", strURL, False
.Send
strText = .responseText
End With

If objXMLHTTP.statusText = "OK" Then
GetXMLHTTP = strText
Else
GetXMLHTTP = ""
End If

End Function