Quote:
Originally Posted by BobBridges
1) Well, wait; of course the content of the web pages changes. But are you saying the layout changes on a regular basis as well? Because if the content changes, but the data are in the same cells each time, then your program can still work with it.
Oh, wait, you mean the URL itself changes? Sure, that makes sense; but still, isn't it better to update the link manually and have the routine do all the reformatting afterward, than to do both jobs by hand?
2) The fact that you have to pull more than one page is more reason to use a VBA routine, not less.
|
By replying to your post, I just found out that the brackets can be replaced!
On the website, [= %5B & ]= %5D
I now have the first part of the code which works:
Code:
Sheets("Temp").Select
URL = "URL;" & Range("K4").Value
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("L5"))
.Name = Range("K5").Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Here are the next problems...
How do I automate it to have the correct amount of pages each time? For instance, last 3 months have 19 pages. That means 19 web querys and 19 different webpages? What if next time it's 23 pages?
And how do I make the data to follow suit? For instance, the first page of data copied from the website end on L259 with the first query. The second page should start on L260, but this will change each 3 months. How do I make the query to look for last row of page 1 of data and then choose its destination to follow suit? I'm not sure if I'm clear? lol :P