Evening all,
First post from a VBA newbie, so thanks in advance for your help.
I have spent the day importing European Soccer League tables into Excel by following the instructions found in Mr Excel's Learn Excel videos #664 and #665 on the YouTube channel. It all worked a treat and was easy to follow.
I even figured out how to rename the worksheets with a short text string that I pulled out of the weblinks that I extracted from video.
I now want to add some functions to each sheet to allow for some statistical analysis of the footy data, which I know how to do.
My question is how can I update the worksheets that I have just created (which I would like to do on a weekly basis) with new data from the same website using VBA rather than simply creating new ones?
I attach the code for those who are not familiar with the videos.
There are one or two minor additions to the original code.
I hope that this is clear
Many thanks
michael
First post from a VBA newbie, so thanks in advance for your help.
I have spent the day importing European Soccer League tables into Excel by following the instructions found in Mr Excel's Learn Excel videos #664 and #665 on the YouTube channel. It all worked a treat and was easy to follow.
I even figured out how to rename the worksheets with a short text string that I pulled out of the weblinks that I extracted from video.
I now want to add some functions to each sheet to allow for some statistical analysis of the footy data, which I know how to do.
My question is how can I update the worksheets that I have just created (which I would like to do on a weekly basis) with new data from the same website using VBA rather than simply creating new ones?
I attach the code for those who are not familiar with the videos.
Code:
Sub loopthrough() Dim WSO As Worksheet
Set WSO = ActiveSheet
For Each cell In WSO.Range("D1:D23")
ActiveWorkbook.Worksheets.Add After:=ActiveSheet
ThisURL = "URL;" & cell.Value
URLName = Replace(Mid(ThisURL, 42, 20), "/", " ") 'country extracted
With ActiveSheet.QueryTables.Add(Connection:= _
ThisURL _
, Destination:=Range("$A$2"))
.Name = "table"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
ActiveSheet.Name = URLName
End With
Next cell
End Sub
Sub GetLinks()
For Each hl In ActiveSheet.Hyperlinks
Cells(hl.Parent.Row, 4).Value = hl.Address & "/table"
Next hl
End Sub
There are one or two minor additions to the original code.
I hope that this is clear
Many thanks
michael