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
0
Utf
4/8/2010 5:16:01 AM
excel.programming 6508 articles. 2 followers. Follow

9 Replies
526 Views

Similar Articles

[PageSpeed] 41

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
0
Utf
4/8/2010 5:30:01 AM
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
0
Utf
4/8/2010 6:53:01 AM
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" <Eric@discussions.microsoft.com> wrote in message 
news:7F05758E-6473-49ED-A24A-CB3DF49757C6@microsoft.com...
> 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 

0
ozgrid
4/8/2010 7:04:44 AM
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" <Eric@discussions.microsoft.com> wrote in message 
> news:7F05758E-6473-49ED-A24A-CB3DF49757C6@microsoft.com...
> > 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 
> 
0
Utf
4/8/2010 7:31:01 AM
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" <Eric@discussions.microsoft.com> wrote in message 
> > news:7F05758E-6473-49ED-A24A-CB3DF49757C6@microsoft.com...
> > > 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 
> > 
0
Utf
4/8/2010 10:04:01 AM
Thank everyone very much for suggestions
I get an error message 1004
Name 'method' ('_Worksheet' Object) fail, and it refers to following code
    ws.Name = wsDate.Range("b" & lngRow)
It seems to me that it cannot stop after the last sheets, and keep going for 
the next one.  
Do you have any suggestions on how to fix it?
Furthermore, do you have another approach to delete any sheet not named 
under column B?
Thanks in advance for any suggestions
Eric

"Jacob Skaria" wrote:

> 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" <Eric@discussions.microsoft.com> wrote in message 
> > > news:7F05758E-6473-49ED-A24A-CB3DF49757C6@microsoft.com...
> > > > 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 
> > > 
0
Utf
4/8/2010 12:08:02 PM
The following codes only insert the URL into cell A1 on each sheet, but I 
would like to import the external data from this URL into each sheet.
Do you have any suggestions on how to fix it?
Thank you very much for any suggestions
Eric

    ws.Hyperlinks.Add ws.Range("A1"), wsDate.Range("c" & lngRow), _
    TextToDisplay:=wsDate.Range("c" & lngRow).Text


"Jacob Skaria" wrote:

> 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" <Eric@discussions.microsoft.com> wrote in message 
> > > news:7F05758E-6473-49ED-A24A-CB3DF49757C6@microsoft.com...
> > > > 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 
> > > 
0
Utf
4/8/2010 12:11:02 PM
I solve this problem by changing wsDate.Cells(Rows.Count, "B").End(xlUp).Row 
into wsDate.Range("O1").Value, since there is formula to determine the lists 
for 500 cells, your code counts all the empty cell for number of rows, but 
there are only 2 lists with links and 498 lists with "" empty cells.
Do you have another approach to count the number of rows, not including the 
empty cells with formula?
Thanks in advance for any suggestions
Eric

"Eric" wrote:

> Thank everyone very much for suggestions
> I get an error message 1004
> Name 'method' ('_Worksheet' Object) fail, and it refers to following code
>     ws.Name = wsDate.Range("b" & lngRow)
> It seems to me that it cannot stop after the last sheets, and keep going for 
> the next one.  
> Do you have any suggestions on how to fix it?
> Furthermore, do you have another approach to delete any sheet not named 
> under column B?
> Thanks in advance for any suggestions
> Eric
> 
> "Jacob Skaria" wrote:
> 
> > 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" <Eric@discussions.microsoft.com> wrote in message 
> > > > news:7F05758E-6473-49ED-A24A-CB3DF49757C6@microsoft.com...
> > > > > 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 
> > > > 
0
Utf
4/8/2010 1:26:06 PM
Furthermore, I would like to delete any sheets, which name is not included 
within the lists under column B of sheet "Date" and the Date sheet cannot be 
deleted too. Do you have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

"Jacob Skaria" wrote:

> 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" <Eric@discussions.microsoft.com> wrote in message 
> > > news:7F05758E-6473-49ED-A24A-CB3DF49757C6@microsoft.com...
> > > > 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 
> > > 
0
Utf
4/8/2010 1:31:02 PM
Reply:

Similar Artilces:

Why does [group] appear after the filename in Excel 2003?
I upgraded from Office 2000 to Office 2003 this morning. An Excel document I created yesterday in a shared network location that has never been touched by anyone but me is now opening with [Group] status. I need to activate Autofilter, but it is unavailable (greyed) on the Tools --> Filter menu. I also can't locate a definition for this setting or how to turn it off, nor do I have any idea why it is in this "group" mode. It is definitely not a shared workbook. Please help! You may have more than one sheets selected simoultaneously. Righclick a sheet tab and select ...

hyperlink to existing excel file
When I create a hyperlink to an existing excel file and then click on it, explorer opens to the folder that the file is in instead of the excel file opening. Just to test I pointed it to a word document and the doc file opened fine. How do I get a hyperlink to open the excel file? Thanks. Are you sure it isn't to the folder instead. Only way I can reproduce that is if I select the folder instead of the file when I create the hyperlink. As expected if I use a hyperlink to a particular file it will open.. -- Regards, Peo Sjoblom "purplehaz" <software@for.me> wrote i...

Show Excel in two separate instances/two monitors?
I've recently been upgraded to two monitors (one of the few things IT has done right around here!). But try as I might, I can't get two Excel (2003) workbooks to show in two separate instances so I can put one on each monitor! Any solutions? Ed Hi Ed, open 2 workbooks -- click the Maximize button (so you are in resize mode) --- drag a workbook by the titlebar to the second monitor... Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * Ed wrote: > I've recently been upgraded to t...

Keeping rows together after link
I have spreadsheet that has several columns that link to anothe spreadsheet.This is G1:P100. My A,B,C,D columns is where I pu information related to the information in G-P. For instance, G-P link to a students information. It changes all the time. In A-D I hav columns to input criteria for each student. The problem is that whe the link updates the criteria and the student info end up on diffren rows now. For instance If B1 is " Has Housing" and G1 is " Steve" afte the update " Steve" might move to G3 and the "Has Housing" does no move with it -- Message...

How can I asign a number value to a text line in Excel?
tI have a Backgammon Club with Internal Club Rankings that are in Text. I want my members to report their wins - 1st, 2nd, and 3rd place - in tournaments to my web site . I would like them to imput: their nickname, date of tournament, tournament Room #, tournament points played for, and host of the tournament. I would further like the calculation to display any change in Ranking as a text cell. Additionally, I want to show for the member, how many more points are needed to get to the next levle and what that (Text)level is. If there is a "what if" for this please advise. Ri...

Plot area of embedded charts unexplainably shrinks in Excel 2003 S
I am working on a large spreadsheet full of numerical data. It contains several dozen bar and line graphs embedded across several worksheets. I have a decent understanding of Excel and Excel graphs but do not use VBA. The problem I have with Excel is that it sometimes – and unpredictably – (vertically) shrinks the plot areas of the embedded graphs. This can happen between opening the document and printing it. Other times, the plot area would shrink if I click on the plot area with my mouse, and then click off on to a cell in the same worksheet. The chart area remains the same siz...

HTTP link troubles
When I am using outlook express and recive andemail with a HTTP link ihave troulble opening it,. When I click on the blue http link it opens up in Netscape not in Internet Exployer Browser. I know I have a setting wrong but for the life of me cannot find how to switch it. Please help Windows Explorer->Tools->Folder Options->File Types. Edit what the http: urls open with. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without rea...

Can I embed a clickable link that includes command line arguments?
I 'm familiar with the process of embedding a clickable link to a file located on a share accessible to the recipient, and do so regularly by using a UNC formatted path: \\ServerName\Path\to the \location\of the\file (Interesting ... the "<" ">" I wrapped the above line in dissappeared as soon as I entered the close bracket. In Outlook [plain text] I'm not used to that happening.) My question: is it possible to do the same thing to launch a .exe (or ..cmd) with command line arguments? Something like: \\UNC Path\Executable /argument1 /a...

Is there a way to setup excel to prompt for a backup copy upon ex.
I want to automatically create a backup onto a disk each time I exit program. "sonny" wrote: > It depends on which version of Excel as to how. It is also preferred that the content of you request not be typed in the Subject field. Please take advantage of the Message area, even for short inquiries. Thanks |:>) "sonny" wrote: > I am currently using 2000. Thanks "CyberTaz" wrote: > It depends on which version of Excel as to how. > > It is also preferred that the content of you request not be typed in the > Subject field. Ple...

Sounds with Excel
Hello Friends Is it possible and how can I do this task with ExcelXp: If the value of cell A1 on "my worksheet" in "my workbook" is "1"-play any sound, If cell A1 is empty-don't play sound. For the next cell on the same sheet B1: if B1=1- play sound different than the sound for the cell A1, if B1 is empty-don't play sound. If both cells A1=1 AND B1=1 play sound different than the sounds for cell A1 and cell B1. I'm running WindowsXP. Thank you. Hi Tim have a look at http://j-walk.com/ss/excel/tips/tip87.htm Frank Tim wrote: > Hello Friends...

External Link-help!
Hi there, I am trying to get rid of an external link to my excel spreadsheet that I created. When I email the excel file to a coworker, the file asks to update links. But, I want to get rid of all the links so that when others open it, the message will not come up. Under the Edit menu, the Links option is not highlighted. Somehow, the external link got embedded into the file, and I need a solution to delete it out. My job is depending on this resolution! Thanks, Tina I could really use some help! Try Bill Manville's addin. It'll find those pesky ones. Findlink.zip ...

Payee List
Is there a way to stop money from automatically every single transactions description downloaded to my payee list? I only want the Payees I have setup for electronic payments with my bank to list when I go to electronically post a bill payment. In microsoft.public.money, Kevin wrote: >Is there a way to stop money from automatically every single transactions >description downloaded to my payee list? [M05]Tools->Settings->AccountSettings(global)->ConfirmNewPayees [other]Tools->Options->Editing->ConfirmNewPayees will give you the option to not add a payee to the ...

Locking a Marketing List
I can't find any information about what it means to Lock a marketing list. I've searched the KB, the web, the Help, and the marketing automation training materials, but have not found any references to this and how it's used. What does this mean? Steve, Flagging a Marketing List as "locked" turns off the Manage Members functionality. It prevents you from being able to use the Advanced Find controls for adding, removing and evaluating marketing list members from the marketing list screens as well as selecting records from a list view and adding them to that list. Ho...

Discount Lists 01-28-05
Hey all, First time posting here! Have an issue with a discount list not applying to a Price List Item. It seems to be applied to the PLI, but when I bring up a quote, and say order 5 of something, they are still at the same unit price as if I ordered 1. Is there another place where I need to define the discount list? Also, is there a good CRM site that lists some FAQs or just a good site on CRM? Thanks! Michael Hi Michael When you open the discount price item under the price list is the discount list specified here? Thats quite a tongue twister! To answer your query on other ...

Link subform ID control
Link subform ID control Using Access =9203=85 I have a form with one subform. The subform holds an ID field/control (called synopsisID) and a textbox for narrative entries (memo datatype called synopsis). The main form and subform are properly linked in the Child/Master field setting (synopsisID to synopsisID). My question/issue is how to update the synopsisID textbox on the main form when it=92s assigned (by autonum) in the subform. I tried the after update event of the synopsisID on the subform, but couldn=92t get it to populate the main form. Maybe there=92s a better ...

storing multiple list selections in access
i have 2 tables, table a and table b. is it possibile for a control bound to table a (a list box, with multiple selections eneabled) to be able to select multiple records from table b, and store them? i.e. table A record 1 has table B records 1,2,3,4,8,12, and so on selected while Table A record 2 only has table B records 3, 8 and 15 selected, and so on.. is there some way to store those selections within table A? for those curious, i'm making a character sheet for an RPG.. table A is the actual sheet, while B is the list of spells.. i'm looking for a way to store which spells each ...

display countdown timer in Excel
I'd like to display (in Excel 2003) a countdown timer in a worksheet (either in a cell or a textbox or other control). I want the user to be able to see the clock tick down from say 20 seconds to 0 in one second increments (or decrements, I guess). Is there a function that will do this? If this is somethingVBA can handle better, I can post this question in the programming newsgroup. Thanks, Bert It has to be done through VBA. You could write code or have someone do it for you. That someone would be Bob Phillips who has a free downlaodable workbook with a Timer setup. http://...

Excel 2000
Hi! *First off:* I have created a spreadsheet that has a dynamic range an data validation. *Next:* i know i can add options to the 'dynamic range' and my dro down menu will add the option to its list. *Problem:* How can I make it so the user can add data into th validated data list/drop down menu rather than having the user addin it into the dynamic range? *Example:*this drop down menu indicates how often a system is updated weekly, monthly, etc. etc... i don't want to restrict it, so i want t make it so they can indicate their own time on how often those update occur. TIA -...

Can I add a domain to the Blocked Senders list?
I am just getting used to Outlook 2003 and trying to make use of the SP{AM filter. I see that when I right click on a message that one of the options id to add a sender to the "Blocked Senders List" Is there a way that I can also include the option to block a domain? Thanks Dunc Dunc wrote: > Is there a way that I can also include the option to block a domain? There is an easy way, if you're running Outlook 2003+SP1. Dunc wrote: > Is there a way that I can also include the option to block a domain? Right-click on any e-mail, choose (menu) Junk Mail, Junk Mail...

Some Contacts List messages going into Junk Mail folder
My wife and I use the same computer, and we have our separate email addresses set up in WLM. We also share the Contacts folder. Starting about a month or two ago, many of the messages from my wife's friends, who are in our Contacts folder, have been going repeatedly into her Junk Mail folder. Even clicking of the Not Junk option has failed to stem this flow. I have not had this experience with my email messages. What might we be missing in our setup? Gordon Biggar Hotmail type accounts for both email addresses ? - Hotmail.com, Live.com, Msn.com *Note: All Hotmai...

How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows and columns. How do I create drop down lists for both the rows and columns and then return the cell value? eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each cell within this group how do I create drop downs for each selection, 2-6 and B-G, and return the value to a selected cell. In a typical table of this nature there would be descriptive row and column headers. To do a lookup on this table you would then look for the intersection of a specific row header and a specific column ...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

Link Help!!!
hi, How can I add or remove link from “My Work” under workplace, like Calendar or Reports...? And where the file sitelog.xml?? thnx, ...

imported distributions lists show as contacts
Client exported his Contacts containing several Distribution Lists, then imported into a new system. Teh imported DL's show up as separate Contact Lists...how, why, and more importantly, is there a simplified way to correct? Version of Outlook they were exported from and imported into? How did you import them? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net ...

Preventing Excel files from being moved or deleted
What permissions setting is used to prevent users from moving or deleting an Excel file on a network? This is more of an network operating system setting question. I would think putting the file on a share that the users only had readonly access would be enough. But this would mean that the users could still copy (not move) the file. And they wouldn't be able to update the workbook. bg500 wrote: > > What permissions setting is used to prevent users from moving or deleting an > Excel file on a network? -- Dave Peterson All explained in Start>Help and Support "p...