Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata
Public FileStr As String
Public DLURL As String
Public Sub GetSheetFromWeb()

FileStr = Environ("USERPROFILE") & "\Downloads\Temp.xlsx"
DLURL = "http://domain.com/file.ext"
Call DL_FILE(DLURL)
Call Open_File
Call DeleteFile(FileStr)

End Sub

Public Sub DL_FILE(DLURL As String)
    Dim DLUrl As String, ObjURL As String
    Dim IEObj As Object, objElement As Object, objCollection As Object
 
    Set IEObj = CreateObject("InternetExplorer.Application")
    IEObj.Visible = True
    
    ObjURL = "http://google.com"
    DLUrl = DLURL
    
    IEObj.navigate ObjURL
    Do Until IEObj.readyState = 4
       DoEvents
    Loop
    IEObj.navigate DLUrl

    'Bring IEObj to Focus
    HWNDSrc = IEObj.HWND
    SetForegroundWindow HWNDSrc

    Application.Wait (Now() + TimeValue("00:00:02"))
    SendKeys "{TAB}{TAB}{ENTER}", True ' Select "Save" in DL Window
    Application.Wait (Now() + TimeValue("00:00:02"))
    SendKeys "%{f4}", True ' Alt + F4 = Close IEObj
    
    Application.StatusBar = "WOW! Brad is Awesome!"
    
    'Unload IE
    Set IEObj = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing
Public Sub Open_File()
Dim wbk1 As Workbook, wbk2 As Workbook

    Set wbk1 = ActiveWorkbook
    Set wbk2 = Workbooks.Add(FileStr)

    wbk2.Worksheets(1).Copy After:=wbk1.Sheets(1)
    wbk2.Close SaveChanges:=False
End Sub

Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = (Dir(FileToTest) <> "")
End Function

Sub DeleteFile(ByVal FileToDelete As String)
   If FileExists(FileToDelete) Then
      ' First remove readonly attribute, if set
      SetAttr FileToDelete, vbNormal
      ' Then delete the file
      Kill FileToDelete
   End If
End Sub
  • No labels