Can anybody help on macro please?

E

Eric

Does anyone have any suggestion on how to load a web link by modifying
following codes? 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, any web links
In cell A3, any web links


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
 
J

Joel

It is better to open each workbook and copy the data. You don't need a temp
worksheet


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
myfilename = myCell.Text
Workbooks.Open Filename:=myfilename
ThisWorkbook.Sheets("Lists").Range("Z" & myCell.Row) = _
ActiveWorkbook.ActiveSheet.Range("A10")
ActiveWorkbook.Close savechanges:=False
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
 
E

Eric

Hi Joel:
The given code is to open each workbook under Lists worksheet, but the list
of workbook have been changed to a list of web links, and I would like to
open a web link instead, so I want to modify the given code for accessing the
data on the web.
Do you have any suggestions?
I have post this question for a few day without any reply and don't know why.
You are the first one, I am very appreciated for your reply, hope you can
give me any suggestions on how to change the existing codes or your written
code for accessing the data based on the web link.

For example,
In cell A2 under List worksheet, there is a web link
http://www.stata.com/help.cgi?macro
Once I load this web link into Temp worksheet, and I would like to copy cell
A10 under Temp worksheet into cell B2 under List worksheet.
The value is "Title" in cell B2 under List worksheet

In cell A3 under List worksheet, there is a web link
http://www.stata.com/statalist/archive/2007-09/msg00915.html
Once I load this web link into Temp worksheet, and I would like to copy cell
A10 under Temp worksheet into cell B3 under List worksheet.
The value is "to follow-up on my last message, I found some time today to
write a" in cell B3 under List worksheet.

Repeat the same tasks until the end of the list

Do you have any suggestions?
Thank you very much for any suggestions
Eric
 
J

Joel

Im not getting any useful data in cell A10 from either of you posted websites.

The way of doingf this task is to record a macro while doing a webquery from
the spreadsheet. The record macro is on the spreadsheet under tools macro.
The web query is under data - import data

Here is the code I got from one of your websites

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.stata.com/statalist/archive/2007-09/msg00915.html", _
Destination:=Range("A1"))
.Name = "msg00915"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


You want to make two changes to the code
1) load it into temp worksheet
2) Make the http address a variable. set the variable MyWebsite to equal
the data in column A.
Mywebsite = mycell.text
3) change the name of the query table to make it a general name for all
websites. Any string can be used.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))
.Name = "MyWebsite"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
 
E

Eric

Hi Joel:
I try following code

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))

It seems to me that
"URL;" & Mywebsite is not equal to
"URL;http://www.stata.com/help.cgi?macro".

Do you have any suggestions on how to solve it?
Thank you very much for any suggestions
Eric
 
J

Joel

If you used a recorded macro you removed tooooo many statements. You must
have the refresh instruction which is the statedment that actually performs
the query. Here is the code I recorded with some minor changes that you had
in your code.



With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))
.Name = "help.cgi?macro"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Eric said:
Hi Joel:
I try following code

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))

It seems to me that
"URL;" & Mywebsite is not equal to
"URL;http://www.stata.com/help.cgi?macro".

Do you have any suggestions on how to solve it?
Thank you very much for any suggestions
Eric

Joel said:
Im not getting any useful data in cell A10 from either of you posted websites.

The way of doingf this task is to record a macro while doing a webquery from
the spreadsheet. The record macro is on the spreadsheet under tools macro.
The web query is under data - import data

Here is the code I got from one of your websites

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.stata.com/statalist/archive/2007-09/msg00915.html", _
Destination:=Range("A1"))
.Name = "msg00915"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


You want to make two changes to the code
1) load it into temp worksheet
2) Make the http address a variable. set the variable MyWebsite to equal
the data in column A.
Mywebsite = mycell.text
3) change the name of the query table to make it a general name for all
websites. Any string can be used.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))
.Name = "MyWebsite"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Click to expand...
Click to expand...
 
E

Eric

Thank you very much for your suggestion
There is an error on the refresh instruction which is the statedment that
actually performs the query. I want to store the url into Mywebsite variable
and combine the query together, but it is not working on following codes.
Do you have any suggestions on following error? and do you have any
suggestions on how to fix it?
Thank you very much for any suggestions
Eric

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
Mywebsite = 1
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" &
Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1"))

Error pointing to
..Refresh BackgroundQuery:=False



Joel said:
If you used a recorded macro you removed tooooo many statements. You must
have the refresh instruction which is the statedment that actually performs
the query. Here is the code I recorded with some minor changes that you had
in your code.



With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))
.Name = "help.cgi?macro"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Eric said:
Hi Joel:
I try following code

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))

It seems to me that
"URL;" & Mywebsite is not equal to
"URL;http://www.stata.com/help.cgi?macro".

Do you have any suggestions on how to solve it?
Thank you very much for any suggestions
Eric

Joel said:
Im not getting any useful data in cell A10 from either of you posted websites.

The way of doingf this task is to record a macro while doing a webquery from
the spreadsheet. The record macro is on the spreadsheet under tools macro.
The web query is under data - import data

Here is the code I got from one of your websites

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.stata.com/statalist/archive/2007-09/msg00915.html", _
Destination:=Range("A1"))
.Name = "msg00915"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


You want to make two changes to the code
1) load it into temp worksheet
2) Make the http address a variable. set the variable MyWebsite to equal
the data in column A.
Mywebsite = mycell.text
3) change the name of the query table to make it a general name for all
websites. Any string can be used.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))
.Name = "MyWebsite"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
:

Hi Joel:
The given code is to open each workbook under Lists worksheet, but the list
of workbook have been changed to a list of web links, and I would like to
open a web link instead, so I want to modify the given code for accessing the
data on the web.
Do you have any suggestions?
I have post this question for a few day without any reply and don't know why.
You are the first one, I am very appreciated for your reply, hope you can
give me any suggestions on how to change the existing codes or your written
code for accessing the data based on the web link.

For example,
In cell A2 under List worksheet, there is a web link
http://www.stata.com/help.cgi?macro
Once I load this web link into Temp worksheet, and I would like to copy cell
A10 under Temp worksheet into cell B2 under List worksheet.
The value is "Title" in cell B2 under List worksheet

In cell A3 under List worksheet, there is a web link
http://www.stata.com/statalist/archive/2007-09/msg00915.html
Once I load this web link into Temp worksheet, and I would like to copy cell
A10 under Temp worksheet into cell B3 under List worksheet.
The value is "to follow-up on my last message, I found some time today to
write a" in cell B3 under List worksheet.

Repeat the same tasks until the end of the list

Do you have any suggestions?
Thank you very much for any suggestions
Eric

:

It is better to open each workbook and copy the data. You don't need a temp
worksheet


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
myfilename = myCell.Text
Workbooks.Open Filename:=myfilename
ThisWorkbook.Sheets("Lists").Range("Z" & myCell.Row) = _
ActiveWorkbook.ActiveSheet.Range("A10")
ActiveWorkbook.Close savechanges:=False
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


:

Does anyone have any suggestion on how to load a web link by modifying
following codes? 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, any web links
In cell A3, any web links


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

Eric

It seems to me that the query for url has been changed if the URL is combined
together.
Based on both following queries, the first one can load the code for 1, but
the second one cannot do it.
Do you have any suggestions on what wrong it is and how to solve it?
Thank you very much suggestions
Eric

[Can load the code = 1]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1",
Destination:=Sheets("Temp").Range("$A$1"))

[Cannot load the code = 1]
Mywebsite = 1
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" &
Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1"))


Joel said:
If you used a recorded macro you removed tooooo many statements. You must
have the refresh instruction which is the statedment that actually performs
the query. Here is the code I recorded with some minor changes that you had
in your code.



With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))
.Name = "help.cgi?macro"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Eric said:
Hi Joel:
I try following code

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))

It seems to me that
"URL;" & Mywebsite is not equal to
"URL;http://www.stata.com/help.cgi?macro".

Do you have any suggestions on how to solve it?
Thank you very much for any suggestions
Eric

Joel said:
Im not getting any useful data in cell A10 from either of you posted websites.

The way of doingf this task is to record a macro while doing a webquery from
the spreadsheet. The record macro is on the spreadsheet under tools macro.
The web query is under data - import data

Here is the code I got from one of your websites

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.stata.com/statalist/archive/2007-09/msg00915.html", _
Destination:=Range("A1"))
.Name = "msg00915"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


You want to make two changes to the code
1) load it into temp worksheet
2) Make the http address a variable. set the variable MyWebsite to equal
the data in column A.
Mywebsite = mycell.text
3) change the name of the query table to make it a general name for all
websites. Any string can be used.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))
.Name = "MyWebsite"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
:

Hi Joel:
The given code is to open each workbook under Lists worksheet, but the list
of workbook have been changed to a list of web links, and I would like to
open a web link instead, so I want to modify the given code for accessing the
data on the web.
Do you have any suggestions?
I have post this question for a few day without any reply and don't know why.
You are the first one, I am very appreciated for your reply, hope you can
give me any suggestions on how to change the existing codes or your written
code for accessing the data based on the web link.

For example,
In cell A2 under List worksheet, there is a web link
http://www.stata.com/help.cgi?macro
Once I load this web link into Temp worksheet, and I would like to copy cell
A10 under Temp worksheet into cell B2 under List worksheet.
The value is "Title" in cell B2 under List worksheet

In cell A3 under List worksheet, there is a web link
http://www.stata.com/statalist/archive/2007-09/msg00915.html
Once I load this web link into Temp worksheet, and I would like to copy cell
A10 under Temp worksheet into cell B3 under List worksheet.
The value is "to follow-up on my last message, I found some time today to
write a" in cell B3 under List worksheet.

Repeat the same tasks until the end of the list

Do you have any suggestions?
Thank you very much for any suggestions
Eric

:

It is better to open each workbook and copy the data. You don't need a temp
worksheet


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
myfilename = myCell.Text
Workbooks.Open Filename:=myfilename
ThisWorkbook.Sheets("Lists").Range("Z" & myCell.Row) = _
ActiveWorkbook.ActiveSheet.Range("A10")
ActiveWorkbook.Close savechanges:=False
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


:

Does anyone have any suggestion on how to load a web link by modifying
following codes? 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, any web links
In cell A3, any web links


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

Joel

I'm going to respond to each of you r two postings seperatly. whe you get an
error on the refresh statement it really means there was something wrong with
the format of the query. You had an extra amphersand. The code below works.


Mywebsite = 1
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & _
Mywebsite, Destination:=Sheets("Temp").Range("$A$1"))

.Refresh BackgroundQuery:=False
end with
Eric said:
Thank you very much for your suggestion
There is an error on the refresh instruction which is the statedment that
actually performs the query. I want to store the url into Mywebsite variable
and combine the query together, but it is not working on following codes.
Do you have any suggestions on following error? and do you have any
suggestions on how to fix it?
Thank you very much for any suggestions
Eric

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
Mywebsite = 1
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" &
Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1"))

Error pointing to
.Refresh BackgroundQuery:=False



Joel said:
If you used a recorded macro you removed tooooo many statements. You must
have the refresh instruction which is the statedment that actually performs
the query. Here is the code I recorded with some minor changes that you had
in your code.



With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))
.Name = "help.cgi?macro"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Eric said:
Hi Joel:
I try following code

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))

It seems to me that
"URL;" & Mywebsite is not equal to
"URL;http://www.stata.com/help.cgi?macro".

Do you have any suggestions on how to solve it?
Thank you very much for any suggestions
Eric

:

Im not getting any useful data in cell A10 from either of you posted websites.

The way of doingf this task is to record a macro while doing a webquery from
the spreadsheet. The record macro is on the spreadsheet under tools macro.
The web query is under data - import data

Here is the code I got from one of your websites

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.stata.com/statalist/archive/2007-09/msg00915.html", _
Destination:=Range("A1"))
.Name = "msg00915"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


You want to make two changes to the code
1) load it into temp worksheet
2) Make the http address a variable. set the variable MyWebsite to equal
the data in column A.
Mywebsite = mycell.text
3) change the name of the query table to make it a general name for all
websites. Any string can be used.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite, _
Destination:=sheets("Temp").Range("A1"))
.Name = "MyWebsite"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
:

Hi Joel:
The given code is to open each workbook under Lists worksheet, but the list
of workbook have been changed to a list of web links, and I would like to
open a web link instead, so I want to modify the given code for accessing the
data on the web.
Do you have any suggestions?
I have post this question for a few day without any reply and don't know why.
You are the first one, I am very appreciated for your reply, hope you can
give me any suggestions on how to change the existing codes or your written
code for accessing the data based on the web link.

For example,
In cell A2 under List worksheet, there is a web link
http://www.stata.com/help.cgi?macro
Once I load this web link into Temp worksheet, and I would like to copy cell
A10 under Temp worksheet into cell B2 under List worksheet.
The value is "Title" in cell B2 under List worksheet

In cell A3 under List worksheet, there is a web link
http://www.stata.com/statalist/archive/2007-09/msg00915.html
Once I load this web link into Temp worksheet, and I would like to copy cell
A10 under Temp worksheet into cell B3 under List worksheet.
The value is "to follow-up on my last message, I found some time today to
write a" in cell B3 under List worksheet.

Repeat the same tasks until the end of the list

Do you have any suggestions?
Thank you very much for any suggestions
Eric

:

It is better to open each workbook and copy the data. You don't need a temp
worksheet


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
myfilename = myCell.Text
Workbooks.Open Filename:=myfilename
ThisWorkbook.Sheets("Lists").Range("Z" & myCell.Row) = _
ActiveWorkbook.ActiveSheet.Range("A10")
ActiveWorkbook.Close savechanges:=False
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


:

Does anyone have any suggestion on how to load a web link by modifying
following codes? 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, any web links
In cell A3, any web links


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

VBA Coding Help for Beginner 0
Please help on coding the macro. 0
Please help on coding 0
Macro adds "=" to each cell in defined range across multiple worksheets 5
How to add condition to coding? 1
Copy a Formula in a Macro 3
Word macro that can call cells out of Excel for a find and replace 0
Setting Range with a Loop 1

Top