E
Eric
Referring to the post in Excel Worksheet Function
SOS - Please Help
Does anyone have any suggestion on how to load a web link instead of opening
a file? Please see the coding at the bottom.
Instead of a list of file names on column A under Lists worksheet, it is a
list of web links on column A under Lists worksheet.
For example,
In cell A2, there is http://www.myurl.com/john.html
In cell A3, there is http://www.myurl.com/1.html
I would like to load a list of web links one by one into Temp worksheet,
after the
first web link is loaded from cell A2 under Lists worksheet, then retrieve
the
value in cell A10 under Temp worksheet and paste this value into Z2 under
Lists worksheet. After that,
repeat for the next web links ...
after the second link is loaded from cell A3 under Lists worksheet, then
retrieve the value in cell A10 under Temp worksheet and paste this value into
Z3 under Lists worksheet.
repeat for the next web links until the end of the lists
Does anyone have any suggestions?
Thank anyone very much for any suggestions
Eric
=====
Coding
=====
Sub Updating_Lists()
Dim myRng As Range
Dim myCell As Range
Dim wkbk As Workbook
With Worksheets("Lists")
'still starting in row 2!
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myRng.Cells
Set wkbk = Nothing
On Error Resume Next
On Error GoTo 0
myCell.Offset(0, 1).Value = ""
myCell.Offset(0, 2).Value = ""
myCell.Offset(0, 3).Value = ""
Next myCell
For Each myCell In myRng.Cells
Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3)
On Error GoTo 0
If wkbk Is Nothing Then
myCell.Offset(0, 1).Value = "Failed to open!"
Else
wkbk.Close savechanges:=True
myCell.Offset(0, 1).Value = "ok"
With myCell.Offset(0, 2)
.NumberFormat = "mm/dd/yyyy"
.Value = Date
End With
With myCell.Offset(0, 3)
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
End If
Next myCell
'better to include an extension
Workbooks("Update Lists.xls").Close savechanges:=True
End Sub
SOS - Please Help
Does anyone have any suggestion on how to load a web link instead of opening
a file? Please see the coding at the bottom.
Instead of a list of file names on column A under Lists worksheet, it is a
list of web links on column A under Lists worksheet.
For example,
In cell A2, there is http://www.myurl.com/john.html
In cell A3, there is http://www.myurl.com/1.html
I would like to load a list of web links one by one into Temp worksheet,
after the
first web link is loaded from cell A2 under Lists worksheet, then retrieve
the
value in cell A10 under Temp worksheet and paste this value into Z2 under
Lists worksheet. After that,
repeat for the next web links ...
after the second link is loaded from cell A3 under Lists worksheet, then
retrieve the value in cell A10 under Temp worksheet and paste this value into
Z3 under Lists worksheet.
repeat for the next web links until the end of the lists
Does anyone have any suggestions?
Thank anyone very much for any suggestions
Eric
=====
Coding
=====
Sub Updating_Lists()
Dim myRng As Range
Dim myCell As Range
Dim wkbk As Workbook
With Worksheets("Lists")
'still starting in row 2!
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myRng.Cells
Set wkbk = Nothing
On Error Resume Next
On Error GoTo 0
myCell.Offset(0, 1).Value = ""
myCell.Offset(0, 2).Value = ""
myCell.Offset(0, 3).Value = ""
Next myCell
For Each myCell In myRng.Cells
Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3)
On Error GoTo 0
If wkbk Is Nothing Then
myCell.Offset(0, 1).Value = "Failed to open!"
Else
wkbk.Close savechanges:=True
myCell.Offset(0, 1).Value = "ok"
With myCell.Offset(0, 2)
.NumberFormat = "mm/dd/yyyy"
.Value = Date
End With
With myCell.Offset(0, 3)
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
End If
Next myCell
'better to include an extension
Workbooks("Update Lists.xls").Close savechanges:=True
End Sub