从使用VBA代码的excel中的不同日期的网页导入数据 [英] Importing data from web page with diffrent Dates in excel using VBA code

问题描述

我不知道如何从网站导入数据，我需要从2012年7月1日起直到现在。任何想法家伙？ url更改后，我不知道该怎么做。我想从2012年7月开始导入所有数据，直到现在，我可以通过网页的HTML源码来实现吗？

i don't know how to import data from a web site ,which i need to do it since 1st of July 2012 until present. Any ideas guys?? I don't know how to do it since url changes. I want to import all the data since July 2012 until now so can i do it through html source of the web page?

Sub websitee()


With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.epexspot.com/en/market-data/intraday", Destination:=Range( _
    "$A$1"))
    .Name = "intraday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
     Union(Columns(3), Columns(4), Columns(5), Columns(7), Columns(8),           Columns(9)).Delete

     End With

End Sub

推荐答案

下一个迭代：现在可以调用DownloadPeriod，它应该将数据放在2012年1月的每天的一个工作表中。请测试，我们可以继续到下一次代码的迭代。

Next iteration: You can now call DownloadPeriod and it should drop the data on to a number one worksheets per day in January 2012. Please test and we can go on to the next iteration of code.

Sub DownloadDayFromUser()
     Dim sInput as String
     sInput = InputBox("Enter a date in YYYY-MM-DD format")
     Call websitee(sInput)
End Sub


Sub DownloadPeriod()
     Dim DownloadDay as Date
     DownloadDay = #1/1/2012#

     Do While DownloadDay < #1/2/2012#
         ' Create a new workbook to put the data into
         ActiveWorkBook.Worksheets.Add
         ' Call the web service for today
         Call websitee(Format(DownloadDay,"YYYY-MM-DD"))
         ' Increment the day
         DownloadDay = DownloadDay + 1
     Loop
End Sub

Sub websitee(sDate as String)


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.epexspot.com/en/market-data/intraday/" & sDate & "/", Destination:=Range( _
"$A$1"))
.Name = "intraday"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
 Union(Columns(3), Columns(4), Columns(5), Columns(7), Columns(8),           Columns(9)).Delete

 End With

结束子

这篇关于从使用VBA代码的excel中的不同日期的网页导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！