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

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

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

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

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

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

How to import a list of links into Excel?
 

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" 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

How to import a list of links into Excel?
 

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" 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

How to import a list of links into Excel?
 

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" 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

How to import a list of links into Excel?
 

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" 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