OZmium Sports Betting and Horse Racing Forums

27th May 2009, 02:50 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,457

Excel Help

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.

Code:


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

Last edited by Shaun : 27th May 2009 at 02:55 PM.

29th May 2009, 01:01 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,457

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

Code:


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

29th May 2009, 03:20 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,457

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.

Code:


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

29th May 2009, 08:45 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,457

Problem solved

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

	To advertise on these forums, e-mail us.