Excel Help

[Shaun]

I am trying to use this code to update my workbook, problem i have is when it updates it moves cells can some one add whats needed so the data is overwriten and no new cells added.


Public dTime As Date
Sub MyMacro()

dTime = Now + TimeValue("00:05:00")
Application.OnTime dTime, "MyMacro"
BackgroundQuery = True
RefreshStyle = xlOverwriteCells
ActiveWorkbook.RefreshAll
End Sub

[Shaun]

I have made some changes to the way i want to import webpages but need some help.


Below is the code i will use to import a web query but what i need to do is link it to a cell that will use a vlookup formula what i intend to do is in column A i will have a list of races in column B i will have a weblink to the races then in cell C1 i will have the vlookup formula i need the macro to run and return the contenst of the webpage in excel.

I have looked at a few examples but and still not getting the result i need

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://www.betchoice.com/racing/betting.asp?eventid=275684", Destination _
:=Range("D1"))
.Name = "betting.asp?eventid=275684"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.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:=False
End With
End Sub

[Shaun]

Ok i have worked out how to link it to a cell now i am trying to get it to work on a calculation event so it will run automatic.


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;" & Range("B1").Value, Destination:=Range("D1"))
.Name = "betting.asp?eventid=275684"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.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:=False
End With
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Shaun]

Problem solved