| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to import a list of links into Excel?
Does anyone have any suggestions on how to import a list of links in to Excel?
Under the sheet "Date", there is a list of http links under column C, and a list of name under column B, I would like to import each link into specific sheet at cell A1. For example, in sheet "Date", there is a link www.cnn.com in cell C2, and 1 in cell B2, so this link is imported into sheet "1", and keep running the rest of links. Does anyone have any suggestions on how to do it in Excel macro? Thanks in advance for any suggestions Eric |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to import a list of links into Excel?
Hi Eric
If you have numerics in ColB of Sheet 'Date' try the below. Please make sure you try the below in a saved workbook. For the sheet named "1" the formula will return the corresponding link as a hyperlink... =HYPERLINK("http://" & VLOOKUP(INT(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")),Date!B:C,2,0)) or else try =HYPERLINK("http://" & VLOOKUP(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),""),Date!B:C,2,0)) -- Jacob (MVP - Excel) "Eric" wrote: Does anyone have any suggestions on how to import a list of links in to Excel? Under the sheet "Date", there is a list of http links under column C, and a list of name under column B, I would like to import each link into specific sheet at cell A1. For example, in sheet "Date", there is a link www.cnn.com in cell C2, and 1 in cell B2, so this link is imported into sheet "1", and keep running the rest of links. Does anyone have any suggestions on how to do it in Excel macro? Thanks in advance for any suggestions Eric |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to import a list of links into Excel?
Do you have any suggestions on how to code macro to do that?
My list contains 100 links, and furthermore, I would like to check if sheet name (100) is not available within this workbook, then insert a new sheet and name (100). Do you have any suggestions on how to code it in macro? Thanks in advance for any suggestions Eric "Jacob Skaria" wrote: Hi Eric If you have numerics in ColB of Sheet 'Date' try the below. Please make sure you try the below in a saved workbook. For the sheet named "1" the formula will return the corresponding link as a hyperlink... =HYPERLINK("http://" & VLOOKUP(INT(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")),Date!B:C,2,0)) or else try =HYPERLINK("http://" & VLOOKUP(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),""),Date!B:C,2,0)) -- Jacob (MVP - Excel) "Eric" wrote: Does anyone have any suggestions on how to import a list of links in to Excel? Under the sheet "Date", there is a list of http links under column C, and a list of name under column B, I would like to import each link into specific sheet at cell A1. For example, in sheet "Date", there is a link www.cnn.com in cell C2, and 1 in cell B2, so this link is imported into sheet "1", and keep running the rest of links. Does anyone have any suggestions on how to do it in Excel macro? Thanks in advance for any suggestions Eric |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to import a list of links into Excel?
Sub AddSheet100()
Dim ws As Worksheet On Error Resume Next Set ws = Sheets("100") On Error GoTo 0 If Not ws Is Nothing Then MsgBox "Sheet called '100' already exists" Else Sheets.Add().Name = "100" End If End Sub -- Regards Dave Hawley www.ozgrid.com "Eric" wrote in message ... Do you have any suggestions on how to code macro to do that? My list contains 100 links, and furthermore, I would like to check if sheet name (100) is not available within this workbook, then insert a new sheet and name (100). Do you have any suggestions on how to code it in macro? Thanks in advance for any suggestions Eric "Jacob Skaria" wrote: Hi Eric If you have numerics in ColB of Sheet 'Date' try the below. Please make sure you try the below in a saved workbook. For the sheet named "1" the formula will return the corresponding link as a hyperlink... =HYPERLINK("http://" & VLOOKUP(INT(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")),Date!B:C,2,0)) or else try =HYPERLINK("http://" & VLOOKUP(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),""),Date!B:C,2,0)) -- Jacob (MVP - Excel) "Eric" wrote: Does anyone have any suggestions on how to import a list of links in to Excel? Under the sheet "Date", there is a list of http links under column C, and a list of name under column B, I would like to import each link into specific sheet at cell A1. For example, in sheet "Date", there is a link www.cnn.com in cell C2, and 1 in cell B2, so this link is imported into sheet "1", and keep running the rest of links. Does anyone have any suggestions on how to do it in Excel macro? Thanks in advance for any suggestions Eric |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to import a list of links into Excel?
Do you have any suggestions on how to create a loop to retrieve the link and
insert into specific sheet one at a time until the end of the list? Thank everyone very much for any suggestions Eric "ozgrid.com" wrote: Sub AddSheet100() Dim ws As Worksheet On Error Resume Next Set ws = Sheets("100") On Error GoTo 0 If Not ws Is Nothing Then MsgBox "Sheet called '100' already exists" Else Sheets.Add().Name = "100" End If End Sub -- Regards Dave Hawley www.ozgrid.com "Eric" wrote in message ... Do you have any suggestions on how to code macro to do that? My list contains 100 links, and furthermore, I would like to check if sheet name (100) is not available within this workbook, then insert a new sheet and name (100). Do you have any suggestions on how to code it in macro? Thanks in advance for any suggestions Eric "Jacob Skaria" wrote: Hi Eric If you have numerics in ColB of Sheet 'Date' try the below. Please make sure you try the below in a saved workbook. For the sheet named "1" the formula will return the corresponding link as a hyperlink... =HYPERLINK("http://" & VLOOKUP(INT(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")),Date!B:C,2,0)) or else try =HYPERLINK("http://" & VLOOKUP(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),""),Date!B:C,2,0)) -- Jacob (MVP - Excel) "Eric" wrote: Does anyone have any suggestions on how to import a list of links in to Excel? Under the sheet "Date", there is a list of http links under column C, and a list of name under column B, I would like to import each link into specific sheet at cell A1. For example, in sheet "Date", there is a link www.cnn.com in cell C2, and 1 in cell B2, so this link is imported into sheet "1", and keep running the rest of links. Does anyone have any suggestions on how to do it in Excel macro? Thanks in advance for any suggestions Eric |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to import a list of links into Excel?
Hi Eric
Try the below Sub MyMacro() Dim lngRow As Long, ws As Worksheet, wsDate As Worksheet Set wsDate = Sheets("Date") For lngRow = 2 To wsDate.Cells(Rows.Count, "B").End(xlUp).Row If Not SheetExists(wsDate.Range("b" & lngRow)) Then Set ws = Sheets.Add(After:=Sheets(Sheets.Count)) ws.Name = wsDate.Range("b" & lngRow) Else Set ws = Sheets(wsDate.Range("b" & lngRow).Text) End If ws.Hyperlinks.Add ws.Range("A1"), wsDate.Range("c" & lngRow), _ TextToDisplay:=wsDate.Range("c" & lngRow).Text Next End Sub Function SheetExists(strSheet As String) As Boolean Dim ws As Worksheet On Error Resume Next Set ws = Sheets(strSheet) If Not ws Is Nothing Then SheetExists = True End Function -- Jacob (MVP - Excel) "Eric" wrote: Do you have any suggestions on how to create a loop to retrieve the link and insert into specific sheet one at a time until the end of the list? Thank everyone very much for any suggestions Eric "ozgrid.com" wrote: Sub AddSheet100() Dim ws As Worksheet On Error Resume Next Set ws = Sheets("100") On Error GoTo 0 If Not ws Is Nothing Then MsgBox "Sheet called '100' already exists" Else Sheets.Add().Name = "100" End If End Sub -- Regards Dave Hawley www.ozgrid.com "Eric" wrote in message ... Do you have any suggestions on how to code macro to do that? My list contains 100 links, and furthermore, I would like to check if sheet name (100) is not available within this workbook, then insert a new sheet and name (100). Do you have any suggestions on how to code it in macro? Thanks in advance for any suggestions Eric "Jacob Skaria" wrote: Hi Eric If you have numerics in ColB of Sheet 'Date' try the below. Please make sure you try the below in a saved workbook. For the sheet named "1" the formula will return the corresponding link as a hyperlink... =HYPERLINK("http://" & VLOOKUP(INT(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")),Date!B:C,2,0)) or else try =HYPERLINK("http://" & VLOOKUP(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),""),Date!B:C,2,0)) -- Jacob (MVP - Excel) "Eric" wrote: Does anyone have any suggestions on how to import a list of links in to Excel? Under the sheet "Date", there is a list of http links under column C, and a list of name under column B, I would like to import each link into specific sheet at cell A1. For example, in sheet "Date", there is a link www.cnn.com in cell C2, and 1 in cell B2, so this link is imported into sheet "1", and keep running the rest of links. Does anyone have any suggestions on how to do it in Excel macro? Thanks in advance for any suggestions Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create and import filename list into excel | Excel Programming | |||
| EXCEL - LINKS cannot easily get list of all links & names in book | Excel Worksheet Functions | |||
| how do i import a file list in to excel | Excel Discussion (Misc queries) | |||
| How do I import a Windows Explorer list into Excel? | Excel Discussion (Misc queries) | |||
| import of txt list to Excel | Excel Programming | |||
Hybrid Mode
