Macro Problem

[Shaun]

I am still having problems getting this to run, this code does pretty much what i need but i have 2 problems with it.

1) the code freezes the sheet so it won't calculate i need it to run once then stop and wait for another change.

2) When an incorrect url is used i get a no data message, i would like it to ignore the message and make no changes.

As you can see i am importing 2 different web pages each time it runs.

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
 With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.betchoice.com/racing/betting.asp?eventid=" & Range("$AZ$55").Value, _
Destination:=Range("$A$50"))
        .Name = "betting"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """HorseTable"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=True
     End With
     With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.way2bet.com.au/form-guide/race/" & Range("$BB$55").Value, _
Destination:=Range("$AP$55"))
        .Name = "Form"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=True
        End With
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub


[dgs2001]

I'm not 100% sure but this may be a problem

Code: Select all

 
Application.Calculation = xlCalculationManual


then

Code: Select all

 
Application.Calculation = xlCalculationAutomatic


I think this causes a recalculation every time the macro exits, this in turn restarts the macro, hence the frozen state as it loops permanently.

try removing these two lines as they are not necessary because you also use

Code: Select all


Application.EnableEvents = False

'''''    other code here    '''''''

Application.EnableEvents = True



Duncan

[Shaun]

I have removed all the calculation part of the code and tried to run the import code by itself but it still hangs, i have some lookup formulas on th sheet but they fail to work untill i exit the sheet.

Code: Select all

Sub Worksheet()

 With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.betchoice.com/racing/betting.asp?eventid=" & Range("$AZ$55").Value, _
Destination:=Range("$A$50"))
        .Name = "betting"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """HorseTable"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=True
     End With
     With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.way2bet.com.au/form-guide/race/" & Range("$BB$55").Value, _
Destination:=Range("$AP$55"))
        .Name = "Form"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=True
        End With


End Sub



[Ian]

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Try reversing these statements.

[Shaun]

I am a bloody idiot, i still need to test it some more but i found the problem of the freezing, it was due to a space in a formula this is what i had.
=VLOOKUP(AY53,$BE$56:$BK$102,6, FALSE)

this is what it should have been

=VLOOKUP(AY53,$BE$56:$BK$102,6,FALSE)

I have been trying to sort this out for 4 days, lets see if i can get things working now.

[dgs2001]

yes as ian says reverse the statements.

my previous post was not very clear, sorry.

To explain the

Code: Select all

Application.EnableEvents = false

statement prevents excel from re-calculating the sheet or indeed as it states prevents any application event from triggering, thus allowing your macro code to complete without calling any other macro's or routines.
At the end of your macro the

Code: Select all

 Application.EnableEvents = True

statement then resets excel to allow re-calculating or any other application event.

So this is really the only statement you needed out of the two you had in your original code.

hope this is clearer.

As a by the way - If you stop your macro from completing at any time ie it has gone passed the Application.enableEvents = false but has not reached the Application.enableEvents = true then excel will hold the value of false for application enable events the same is true of Application.Calculation = manual.

I used to be forever pulling my hair out when VBA would not work and seemed to stall etc when really it was because I left Application level events set to manual or off
Duncan

[Roger]

Re: extra space in the formula, Excel is not sensitive to any extra spaces as far as I am aware - I often add spaces when testing to make the structure clearer.