I figure this is a settings issue. When I ran this code on the computer at work I got a nice paste of info on each worksheet - makes it very easy to manipulate the resulting data. When I run the code from home it puts all the data in the "A" column? Any suggestions?
Code:
Sub GET_EEX()
Dim theURL, theFile As String
Dim didItGo As Boolean
Dim Country As String
Dim Cycles As Integer
Dim Filename As String
Dim StartTime As Double
Dim Days_in_Month(1 To 12) As Integer
Dim IE As Object
Dim document, element
Dim WS As Worksheet
Dim Name As String
'Timer is used to monitor speed/bugginess of the simulation
StartTime = Timer
Days_in_Month(1) = 31
Days_in_Month(3) = 31
Days_in_Month(4) = 30
Days_in_Month(5) = 31
Days_in_Month(6) = 30
Days_in_Month(7) = 31
Days_in_Month(8) = 31
Days_in_Month(9) = 30
Days_in_Month(10) = 31
Days_in_Month(11) = 30
Days_in_Month(12) = 31
'Start October 25th, 2010
Month_x = 10
Year_x = 2010
Day_of_Month = 25
Wait_Time = 0
Set IE = CreateObject("internetexplorer.application")
Do While Year_x < 2014
If Year_x = 2012 Then
Days_in_Month(2) = 29
Else
Days_in_Month(2) = 28
End If
Do While Month_x < 13
'Here's where I want to go
If Month_x < 10 Then
If Day_of_Month > 9 Then
theURL = "http://www.eex.com/en/Market%20Data/Trading%20Data/Power/Hour%20Contracts%20%7C%20Spot%20Hourly%20Auction/spot-hours-table/" _
& Year_x & "-" & 0 & Month_x & "-" & Day_of_Month & "/EU"
Else
theURL = "http://www.eex.com/en/Market%20Data/Trading%20Data/Power/Hour%20Contracts%20%7C%20Spot%20Hourly%20Auction/spot-hours-table/" _
& Year_x & "-" & 0 & Month_x & "-" & 0 & Day_of_Month & "/EU"
End If
Else
If Day_of_Month > 9 Then
theURL = "http://www.eex.com/en/Market%20Data/Trading%20Data/Power/Hour%20Contracts%20%7C%20Spot%20Hourly%20Auction/spot-hours-table/" _
& Year_x & "-" & Month_x & "-" & Day_of_Month & "/EU"
Else
theURL = "http://www.eex.com/en/Market%20Data/Trading%20Data/Power/Hour%20Contracts%20%7C%20Spot%20Hourly%20Auction/spot-hours-table/" _
& Year_x & "-" & Month_x & "-" & 0 & Day_of_Month & "/EU"
End If
End If
IE.Navigate theURL
IE.Visible = True
While IE.Busy
DoEvents
Wend
Application.Wait DateAdd("s", Wait_Time, Now)
IE.ExecWB 17, 0 '// SelectAll
IE.ExecWB 12, 2 '// Copy selection
If Month_x < 10 Then
If Day_of_Month > 9 Then
Name = Year_x & "-" & 0 & Month_x & "-" & Day_of_Month
Else
Name = Year_x & "-" & 0 & Month_x & "-" & 0 & Day_of_Month
End If
Else
If Day_of_Month > 9 Then
Name = Year_x & "-" & Month_x & "-" & Day_of_Month
Else
Name = Year_x & "-" & Month_x & "-" & 0 & Day_of_Month
End If
End If
If Wait_Time = 0 Then
Worksheets.Add().Name = Name
ActiveSheet.Columns.ColumnWidth = 20
End If
ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
'ActiveSheet.PasteSpecial Format:="HTML", link:=False, DisplayAsIcon:=False
If Month_x < 10 Then
If Day_of_Month > 9 Then
Name = Year_x & "/" & 0 & Month_x & "/" & Day_of_Month & "|Prices"
Else
Name = Year_x & "/" & 0 & Month_x & "/" & 0 & Day_of_Month & "|Prices"
End If
Else
If Day_of_Month > 9 Then
Name = Year_x & "/" & Month_x & "/" & Day_of_Month & "|Prices"
Else
Name = Year_x & "/" & Month_x & "/" & 0 & Day_of_Month & "|Prices"
End If
End If
If Name <> ActiveSheet.Range("D" & 45) Then
'If Dates don't match the code runs again with a longer wait time
Wait_Time = Wait_Time + 5
If Wait_Time = 20 Then
xxx = xxx
End If
Else
Next_Day_of_Month = Day_of_Month + 7
If Next_Day_of_Month <= Days_in_Month(Month_x) Then
Day_of_Month = Next_Day_of_Month
Else
Day_of_Month = Next_Day_of_Month - Days_in_Month(Month_x)
Month_x = Month_x + 1
End If
'Once the program gets up to the current day it jumps out of the loop
If Year_x = 2013 Then
If Month_x = 2 Then
If Day_of_Month > 14 Then
GoTo Escape_Hatch
End If
End If
End If
Wait_Time = 0
End If
Loop
Year_x = Year_x + 1
Month_x = 1
Loop
Escape_Hatch:
MsgBox Timer - StartTime
End Sub
Last edited: