Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a macro read a list and insert the names into a web address?
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_l...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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a macro read a list and insert the names into a web address?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a macro read a list and insert the names into a web address?
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 On Jan 27, 7:39*pm, Darren wrote: 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_l...er=*Insertname 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a macro read a list and insert the names into a web address?
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 On Jan 27, 8:36*pm, wrote: 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 On Jan 27, 7:39*pm, Darren wrote: 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_l...nsertnamehere* 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can a macro read a list and insert the names into a web address?
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 "Darren" wrote: 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_l...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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel list of names, addresses and email to address book/contact list??? | Excel Discussion (Misc queries) | |||
print lables from excel names and address list | Excel Discussion (Misc queries) | |||
add email address to a list of names | Excel Discussion (Misc queries) | |||
How do I put two names on a mailing list at the same address witho | Excel Discussion (Misc queries) | |||
How do I put two names on a mailing list at the same address witho | Excel Discussion (Misc queries) |