ales26
05-07-2020, 03:06 PM
Hello,
I am writing a vba code to get data from a web page (https://finance.yahoo.com/quote/MSFT/history?period1=1367798400&period2=1588809600&interval=1wk&filter=history&frequency=1wk)
Here is the code:
'Imports the data
With wsN.QueryTables.Add(Connection:=url, Destination:=wsN.Range("A1"))
.Name = Ticker & " Data"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
The problem is that I do not get all of the data of the web page: in fact I would need to scroll down to see them all. On Excel I only get the first 100 rows of data (while there are much more).
I have tried to add a line "Application.Wait" but the problem is that the data does not load when you wait on the page, you need to actually scroll down.
I have not find any answer or similar question on the web. Do you have any idea on how to solve this issue?
Thank you in advance, wish you all a great day!
I am writing a vba code to get data from a web page (https://finance.yahoo.com/quote/MSFT/history?period1=1367798400&period2=1588809600&interval=1wk&filter=history&frequency=1wk)
Here is the code:
'Imports the data
With wsN.QueryTables.Add(Connection:=url, Destination:=wsN.Range("A1"))
.Name = Ticker & " Data"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
The problem is that I do not get all of the data of the web page: in fact I would need to scroll down to see them all. On Excel I only get the first 100 rows of data (while there are much more).
I have tried to add a line "Application.Wait" but the problem is that the data does not load when you wait on the page, you need to actually scroll down.
I have not find any answer or similar question on the web. Do you have any idea on how to solve this issue?
Thank you in advance, wish you all a great day!