VBA Copy Website + Paste to worksheet works on Computer 1 but not Computer 2

Photomofo

Active Member
Joined
Aug 20, 2012
Messages
259
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:

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top