Importing data from web page with diffrent Dates in excel using VBA code

Question

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

If the URL changes when you filter by date could you not just use the new URL in your connection? — Rick
– Rick, Commented Feb 7, 2013 at 23:00
At least tell us what format the URL takes for each day! It should just be a matter of generating the URL dynamically. Do you want to continue doing this automatically every day? — Nick.Mc
– Nick.Mc, Commented Feb 8, 2013 at 0:12
its takes like this :epexspot.com/en/market-data/intraday/intraday-table/2013-02-06/… is changing as the day,month, year changes. and i want to import all the data from 1/07/2012 until present.Yes, the ideal would me doing this automatically starting from 1/7/2012.. any ideas @ElectricLlama tnx for your comment — EyeShield21
– EyeShield21, Commented Feb 8, 2013 at 0:15
@Rick how could i do this? i need the url to open the page and import data — EyeShield21
– EyeShield21, Commented Feb 8, 2013 at 0:21
I may be missing something here but in your code where it says "URL;http://www....... change to the url with the date on it URL;http://www.epexspot.com/en/market-data/intraday/intraday-table/2012-07-01/ — Rick
– Rick, Commented Feb 8, 2013 at 0:28

Nick.Mc · Accepted Answer · 2013-02-12 00:50:09Z

2

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

End Sub

edited Feb 12, 2013 at 0:50

answered Feb 8, 2013 at 2:57

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

EyeShield21 Over a year ago

Tnx for your comment , i made a few corrections about the url. it really imports the table when you give as an input the date, i mean the specific date. that's great but i would like to import a specific column (the hours and the average weight, not the whole table). Do you have any ideas how can i do this?? I mean when i give as input the date i want to import the hours column and the weight average column next to each other.If you have any ideas it would be vital @ElectricLlama

Nick.Mc Over a year ago

epexspot will need to provide with a special URL to retrieve just one column. Are you still interested in retrieving a range of data across different days?

Nick.Mc Over a year ago

OK I have updated the code. Test and get back to me and we can go on to the next iteration.

Collectives™ on Stack Overflow

Importing data from web page with diffrent Dates in excel using VBA code

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related