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" wrote:
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
"Eric" wrote:
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/archi.../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
"Joel" wrote:
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
"Eric" wrote:
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