Data automatic download

[Adar]

Greetings,

Commodity Futures Trading Commission weekly publicize market data which can be downloaded from the static link. However, I am not a good enough specialist in order to make an automatic (or semi-automatic by clicking the button) download of data.

Data is held in file named "annualof.xls" which is archived in "dea_com_xls_2011.zip". dea_com_xls_2011.zip can be always downloaded from http://www.cftc.gov/files/dea/history/dea_com_xls_2011.zip

Does anyone know how to get data from the above mentioned source? I need to get downloaded data on a certain sheet.

Thank you

P.S. I have some script made by a Leith Ross (another excel forum moderator) however it doesn't work...as I understood it downloads the file and unzip it but do not open annualof.xls... Nevertheless I will post the code:

 Private Declare Function FindWindow _
     Lib "user32.dll" _
     Alias "FindWindowA" _
       (ByVal lpClassName As String, _
        ByVal lpWindowName As String) _
     As Long
         
  Private Declare Function GetWindowText _
     Lib "user32.dll" _
     Alias "GetWindowTextA" _
       (ByVal hWnd As Long, _
        ByVal lpString As String, _
        ByVal aint As Long) _
     As Long
          
  Private Declare Function GetWindow _
     Lib "user32.dll" _
       (ByVal hWnd As Long, _
        ByVal wCmd As Long) _
     As Long
        
   Private Declare Function SendMessage _
     Lib "user32.dll" _
     Alias "SendMessageA" _
       (ByVal hWnd As Long, _
        ByVal Msg As Long, _
        ByVal wParam As Long, _
        ByVal lParam As Long) _
     As Long

  Private Declare Function ShellExecute _
    Lib "Shell32.dll" _
    Alias "ShellExecuteA" _
      (ByVal hWnd As Long, _
       ByVal lpOperation As String, _
       ByVal lpFile As String, _
       ByVal lpParameters As String, _
       ByVal lpDirectory As String, _
       ByVal nShowCmd As Long) _
    As Long

 Private Declare Function URLDownloadToFile _
   Lib "urlmon.dll" _
   Alias "URLDownloadToFileA" _
     (ByVal pCaller As Long, _
      ByVal szURL As String, _
      ByVal szFileName As String, _
      ByVal dwReserved As Long, _
      ByVal lpfnCB As Long) _
   As Long
    
 Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
Sub DownloadFilefromWeb()

  Const E_OUTOFMEMORY As Long = &H8007000E
  Const E_DOWNLOAD_FAILURE As Long = &H800C0002
  Const E_INVALID_LINK As Long = &H800C000D
  
  Dim Filespec As String
  Dim FolderPath As Variant
  Dim filename As String
  Dim RetVal As Long
  Dim xlFile As String
  
      URL = "http://www.cftc.gov/files/dea/history/dea_com_xls_2011.zip"
      filename = "dea_com_xls_2011.zip"
   
        FolderPath = Environ("TEMP")
      
        Filespec = FolderPath & "\" & filename
        If Dir(Filespec) <> "" Then Kill Filespec
        
        xlFile = FolderPath & "\" & Left(filename, Len(filename) - 4) & ".xls"
      
        RetVal = URLDownloadToFile(0&, URL, Filespec, 0&, 0&)

          Select Case RetVal
            Case 0
              'OK - No Error
            Case E_OUTOFMEMORY
              MsgBox URL & vbCrLf & "Error - Out of Memory"
            Case E_DOWNLOAD_FAILURE
              MsgBox URL & vbCrLf & "Error - Bad URL or Connection Interrupted"
            Case E_INVALID_LINK
              MsgBox URL & vbCrLf & "Error - Invalid Link or Protocol Not Supported"
            Case Else
              MsgBox URL & vbCrLf & "Error - Unknown = " & Hex(RetVal)
          End Select
        
        If RetVal <> 0 Then
           Exit Sub
        Else
           Unzip Filespec, FolderPath
        End If
          
End Sub

Private Function Unzip(ByVal Zip_Archive_Name As String, ByVal Dest_Folder As String) As Boolean

  Dim bytes() As Byte
  Dim flen As Long
  Dim file_list As String
  Dim filename As String
  Dim fnum As Integer
  Dim i As Long
  Dim Timeout As Long
  
     fnum = FreeFile
     flen = FileLen(Zip_Archive_Name)
     ReDim bytes(flen - 1)
     
     Open Zip_Archive_Name For Binary As #fnum
       Get #fnum, 1, bytes
     Close fnum
     
       If bytes(0) = 80 And bytes(1) = 75 And bytes(2) = 3 And bytes(3) = 4 Then
          FileNameLength = (bytes(27) * 256) Or bytes(26)
          For i = 30 To 30 + FileNameLength - 1
            filename = filename & Chr(bytes(i))
          Next i
       End If
     
     ' Command line String to Unzip a file
       cmdLine = "-min -e -o " & Chr$(34) & Zip_Archive_Name & Chr$(34) & " " & Dest_Folder
        
     ' UnZip the file and save it in the archive
       RetVal = ShellExecute(0&, "", "WinZip32.exe", cmdLine, Zip_Archive_Name, 1&)
     
  ' Stop this thread for 1 second to allow Winzip time to close
    Sleep 1000
    CloseTempFile
    Workbooks.Open Dest_Folder & "\" & filename
    
         
End Function

Private Sub CloseTempFile()

  Const HWND_NEXT As Long = 2
  Const SC_CLOSE As Long = &HF060
  Const WM_SYSCOMMAND As Long = &H112
  
  Dim cch As Long
  Dim Folder As String
  Dim hWnd As Long
  Dim Title As String
  
    hWnd = FindWindow("CabinetWClass", vbNullString)
    
      Do Until hWnd = 0
        Title = String(512, Chr(0))
        cch = GetWindowText(hWnd, Title, 512)
        If Left(Title, cch) = "Temp" Then
           RetVal = SendMessage(hWnd, WM_SYSCOMMAND, SC_CLOSE, 0)
           Exit Do
        End If
        hWnd = GetWindow(hWnd, HWND_NEXT)
      Loop
  
End Sub

 

[Adar]

Thread on this topic is also opened here: http://www.ozgrid.com/forum/showthread.php?t=156952&p=570128