G
Guest
I have a spreadsheet that has two macros in excel. I use the first marcro to
retrieve data from a web page (stock quotes) using . The second macro is
used to copy the data and paste selective parts of the retrieval from the
query.
My problem is that I have a large list of data to go through and I would
like to start the process and have excel go through each line and complete
the process without me having to run each macro individually. When I tried
to combine the two queries into one, the second query will not wait for the
web page data to flood before it runs. When I place a loop or wait action in
place, the data does not come over from the web.
Can someone point me in the right direction? I am not extremely proficient
in this but can follow directions and appreciate any help.
===This is the first Macro===
Sub Stock001()
Dim strStock As String
Dim DesiredRow As Long
Dim PositionOfSpace As Long
'Init Vars:
DesiredRow = Application.ActiveCell.Row
strStock =
Workbooks(Application.ActiveWorkbook.Name).Sheets("StockData").Range("A" &
DesiredRow)
If strStock <> "" Then
'Start by clearing the data area:
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
'Now use the Web Query to fetch the data:
With
ActiveSheet.QueryTables.Add(Connection:="URL;http://finance.yahoo.com/q?s=" &
strStock, Destination:=Sheets("StockData").Range("i1"))
'With
ActiveSheet.QueryTables.Add(Connection:="URL;http://finance.yahoo.com/q?s=",
Destination:=Sheets("StockData").Range("i1"))
.Name = "http://finance.yahoo.com/q?s=" & strStock
'.Name = "http://finance.yahoo.com/q?s=" & strStock
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False 'was True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "12"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=True
End With
Else
MsgBox "Please select the row you are trying to update before clicking
the button."
End If
Range("A" & DesiredRow).Select
End Sub
===This is the second macro which then leads back to the first===
Sub CompleteProcess()
Dim DesiredRow As Long
DesiredRow = Application.ActiveCell.Row
'Paste the values where they belong
Sheets("StockData").Select
Range("I1").Select
Selection.Copy
Range("E" & DesiredRow).Select
ActiveSheet.Paste
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Range("F" & DesiredRow).Select
ActiveSheet.Paste
Columns("E:F").Select
Columns("E:F").EntireColumn.AutoFit
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
'Clear the garbage left behind
Columns("I:L").Select
Selection.Delete Shift:=xlToLeft
Range("A" & DesiredRow + 1).Select
Application.Run "Stocks.xls!Stock001"
End Sub
retrieve data from a web page (stock quotes) using . The second macro is
used to copy the data and paste selective parts of the retrieval from the
query.
My problem is that I have a large list of data to go through and I would
like to start the process and have excel go through each line and complete
the process without me having to run each macro individually. When I tried
to combine the two queries into one, the second query will not wait for the
web page data to flood before it runs. When I place a loop or wait action in
place, the data does not come over from the web.
Can someone point me in the right direction? I am not extremely proficient
in this but can follow directions and appreciate any help.
===This is the first Macro===
Sub Stock001()
Dim strStock As String
Dim DesiredRow As Long
Dim PositionOfSpace As Long
'Init Vars:
DesiredRow = Application.ActiveCell.Row
strStock =
Workbooks(Application.ActiveWorkbook.Name).Sheets("StockData").Range("A" &
DesiredRow)
If strStock <> "" Then
'Start by clearing the data area:
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
'Now use the Web Query to fetch the data:
With
ActiveSheet.QueryTables.Add(Connection:="URL;http://finance.yahoo.com/q?s=" &
strStock, Destination:=Sheets("StockData").Range("i1"))
'With
ActiveSheet.QueryTables.Add(Connection:="URL;http://finance.yahoo.com/q?s=",
Destination:=Sheets("StockData").Range("i1"))
.Name = "http://finance.yahoo.com/q?s=" & strStock
'.Name = "http://finance.yahoo.com/q?s=" & strStock
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False 'was True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "12"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=True
End With
Else
MsgBox "Please select the row you are trying to update before clicking
the button."
End If
Range("A" & DesiredRow).Select
End Sub
===This is the second macro which then leads back to the first===
Sub CompleteProcess()
Dim DesiredRow As Long
DesiredRow = Application.ActiveCell.Row
'Paste the values where they belong
Sheets("StockData").Select
Range("I1").Select
Selection.Copy
Range("E" & DesiredRow).Select
ActiveSheet.Paste
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Range("F" & DesiredRow).Select
ActiveSheet.Paste
Columns("E:F").Select
Columns("E:F").EntireColumn.AutoFit
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
'Clear the garbage left behind
Columns("I:L").Select
Selection.Delete Shift:=xlToLeft
Range("A" & DesiredRow + 1).Select
Application.Run "Stocks.xls!Stock001"
End Sub