How to import a list of links into Excel?

E

Eric

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
 
J

Jacob Skaria

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))
 
E

Eric

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 said:
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 said:
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
Click to expand...
Click to expand...
 
O

ozgrid.com

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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 said:
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 said:
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)


:

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
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

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 said:
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 said:
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 said:
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

:

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)


:

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
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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 said:
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 said:
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
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

:

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)


:

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
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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 said:
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 said:
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
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

:

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)


:

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
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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 said:
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 said:
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

:

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

:

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)


:

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
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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 said:
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 said:
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
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

:

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)


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help on coding? 2
How to load specific range of html into Excel? 2
How to re-fresh the import links if it fails? 1
How to move worksheet into specific position? 3
Need VBA Script to Open URLs from a list in Excel 2
How to code it in macro? 2
How to delete sheets using macro? 13
Conditional Validation In Excel 1

Top