Giri
05-02-2011, 05:38 AM
Hi Guys,
I have been working on the following code and need assistance with the following:
1) Inserting an ErrorHandler to cancel the macro when "Esc" is pressed on the keyboard. I've tried doing this myself but it isn't quite working.
2) Also, the aim of this macro is to post stock information from a website. After the macro has gone through twice (i.e the two stocks), I would like the next posting to be 11 columns to the right of the first posting for each stock. However, my fCellCol value is constantly being reset to 6 after this part of the code. If x = 2 Then
fCellCol = fCellCol + 11
runTime = Now + TimeValue("0:0:30")
Application.OnTime runTime, "StockInfoDL"
End If
Any help on these two matters would be greatly appreciated!
Kind Regards,
Giri
Option Explicit
Option Base 1
Public Sub StockInfoDL()
Dim URL As String
Dim stockSymbol1 As String
Dim myStock
Dim Col
Dim fCell As Range
Dim fCellRow As Integer
Dim QT As QueryTable
Dim x As Integer
Dim fCellCol As Integer
Dim y As Integer
Dim runTime As Date
fCellCol = 6
ActiveSheet.Name = "Stocks"
myStock = Array("MQG", "CBA")
Range("B1").Value = myStock(1)
Range("B5").Value = myStock(2)
For x = 1 To 2
Set fCell = Sheets("Stocks").Range("B1:B20").Find(myStock(x)) 'Stocks listed in Column B
fCellRow = fCell.Row 'The row number of the found cell
URL = "http://www.asx.com.au/asx/research/companyInfo.do?by=asxCode&asxCode=" & myStock(x)
'URL to ASX page with information about the stock
Set QT = Sheets("Stocks").QueryTables _
.Add(Connection:="URL;" & URL, _
Destination:=Sheets("Stocks").Cells(fCellRow, fCellCol))
'Query Table defining which URL to go to and where the information will be copied to in Excel
With QT
.WebSelectionType = xlSpecifiedTables
.WebTables = "2"
.Refresh BackgroundQuery:=False
End With
If x = 2 Then
fCellCol = fCellCol + 11
runTime = Now + TimeValue("0:0:30")
Application.OnTime runTime, "StockInfoDL"
End If
Next x
'After macro has looped twice, the macro will pause for 30secs after which, _
the macro will be re-run And the information will be posted 11 columns To the right _
of where they are currently being posted.
End Sub
I have been working on the following code and need assistance with the following:
1) Inserting an ErrorHandler to cancel the macro when "Esc" is pressed on the keyboard. I've tried doing this myself but it isn't quite working.
2) Also, the aim of this macro is to post stock information from a website. After the macro has gone through twice (i.e the two stocks), I would like the next posting to be 11 columns to the right of the first posting for each stock. However, my fCellCol value is constantly being reset to 6 after this part of the code. If x = 2 Then
fCellCol = fCellCol + 11
runTime = Now + TimeValue("0:0:30")
Application.OnTime runTime, "StockInfoDL"
End If
Any help on these two matters would be greatly appreciated!
Kind Regards,
Giri
Option Explicit
Option Base 1
Public Sub StockInfoDL()
Dim URL As String
Dim stockSymbol1 As String
Dim myStock
Dim Col
Dim fCell As Range
Dim fCellRow As Integer
Dim QT As QueryTable
Dim x As Integer
Dim fCellCol As Integer
Dim y As Integer
Dim runTime As Date
fCellCol = 6
ActiveSheet.Name = "Stocks"
myStock = Array("MQG", "CBA")
Range("B1").Value = myStock(1)
Range("B5").Value = myStock(2)
For x = 1 To 2
Set fCell = Sheets("Stocks").Range("B1:B20").Find(myStock(x)) 'Stocks listed in Column B
fCellRow = fCell.Row 'The row number of the found cell
URL = "http://www.asx.com.au/asx/research/companyInfo.do?by=asxCode&asxCode=" & myStock(x)
'URL to ASX page with information about the stock
Set QT = Sheets("Stocks").QueryTables _
.Add(Connection:="URL;" & URL, _
Destination:=Sheets("Stocks").Cells(fCellRow, fCellCol))
'Query Table defining which URL to go to and where the information will be copied to in Excel
With QT
.WebSelectionType = xlSpecifiedTables
.WebTables = "2"
.Refresh BackgroundQuery:=False
End With
If x = 2 Then
fCellCol = fCellCol + 11
runTime = Now + TimeValue("0:0:30")
Application.OnTime runTime, "StockInfoDL"
End If
Next x
'After macro has looped twice, the macro will pause for 30secs after which, _
the macro will be re-run And the information will be posted 11 columns To the right _
of where they are currently being posted.
End Sub