PDA

View Full Version : Solved: Error Handler



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

Giri
05-04-2011, 03:42 AM
Hi Guys,

I still can't seem to get the error handler working correctly. When the MsgBox appears and I press "No", the macro does't resume.

Any help would be greatly appreciated!

Thanks,

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

On Error GoTo ErrorHandler

fCellCol = 6
'Application.ScreenUpdating = False
ActiveSheet.Name = "Stocks"
myStock = Array("MQG", "CBA")
Range("B1").Value = myStock(1)
Range("B5").Value = myStock(2)

For y = 1 To 100


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)


Set QT = Sheets("Stocks").QueryTables _
.Add(Connection:="URL;" & URL, _
Destination:=Sheets("Stocks").Cells(fCellRow, fCellCol))


With QT
.WebSelectionType = xlSpecifiedTables
.WebTables = "2"
.Refresh BackgroundQuery:=False

End With

If x = 2 Then

fCellCol = fCellCol + 9
runTime = Now + TimeValue("0:0:05")
Application.Wait runTime

End If

Next x

Next y

ErrorHandler:

MsgBox "Are you sure you would like to stop this process?" _
, vbYesNo, "Cancel Macro"

If vbYes Then

Exit Sub

Else

Resume Next

End If


End Sub

Bob Phillips
05-04-2011, 04:46 AM
What part of the code drives you into the error handler?

Giri
05-04-2011, 05:02 AM
Hi xld,

When I press Esc, while the macro is running it goes to the Error Handler part of the code. Then the MsgBox pops up. When I click "No", nothing happens.

Is it possible to have the macro resume at the point where Esc was pressed?

Thanks,

Giri

Bob Phillips
05-04-2011, 05:54 AM
I think it can be made to restart, but I see a problem in that the data that is inserted might get repeated.

Bob Phillips
05-04-2011, 06:01 AM
Try making this change



ErrorHandler:
If MsgBox("Are you sure you would like to stop this process?", vbYesNo, "Cancel Macro") = vbYes Then

Exit Sub
Else

Resume Next
End If

Giri
05-04-2011, 06:24 AM
Hi xld,

That's great! It seems to be working.

However I'm a little confused because what you wrote seems to be quite similar to what I wrote in my second post. Am I missing something?

Anyways, thanks for your help!

BTW, in a earlier post of mine you mentioned how to mark a thread as solved... However, I have not been able to find that button. When I click on Thread Tools, the page just scrolls down to the area below the "Quick Reply" box.

Bob Phillips
05-04-2011, 06:55 AM
The change I made tests the MsgBox result for vbYes. Your code issued the MsgBox with Yes/No buttons, but tested If vbYes without any context, so it always passed.

I will mark it as solved for you.

Giri
05-04-2011, 03:58 PM
Oh right... Thanks for all your help xld!

All the best,

Giri