VBA: Download Excel file from website.

[ssingh75]

Hi All,

Their is one excel file contains on below website and i want to donwload the same and place in my local drive.

https://www.ccilindia.com/Research/Statistics/Pages/NDSOMTradeAnalysisReport.aspx

However, I am using below code to open the website but unable to find the right code to download the same.

Dim strTempName As String
Dim strNDS, strNSE, strRBI As String
Dim ie, ie2, Shell, objWSHShell, objCollection, objElement, objHttp As Object
Dim FileData() As Byte
Dim strInput1, strInput2, strInput3 As String
Dim strOutput1, strOutput2, strOutput3 As String
Dim iRow, iCol, i As Integer
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlColl As MSHTML.IHTMLElementCollection
Dim lRow, lCol, l As Long
Dim strURL As String


On Error Resume Next
Application.EnableCancelKey = xlDisabled

strTempName = ActiveWorkbook.Name
strURL = "https://www.ccilindia.com/Research/Statistics/Pages/NDSOMTradeAnalysisReport.aspx"

Windows.Item(strTempName).Activate
Sheets(1).Select

'*********Input File name
strNDS = Trim(Range("B3").Text)
strNSE = Trim(Range("B4").Text)
strRBI = Trim(Range("B5").Text)
'**********Iput Path
strInput1 = Trim(Range("C3").Text) 'NDS OM
strInput2 = Trim(Range("C4").Text) 'NSE File
strInput3 = Trim(Range("C5").Text) 'RBI Nds File

Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
Set Shell = CreateObject("Shell.Application")
Set ie = CreateObject("InternetExplorer.Application")
' Set ie2 = CreateObject("InternetExplorer.Application")

ie.navigate strURL

While ie.readyState = READYSTATE_LOADING
delay 2.5
Wend

apiShowWindow ie.hwnd, SW_MAXIMIZE

Do While ie.Busy = True
DoEvents
Loop

delay 2.5

 

[GermanPath]

Hello.

The tricky part will be to overrun the Save-dialog in IE:

This will call the webpage and click 'Export to Excel'-iconbutton. Then they Save-dialog open and unfortunately the buttons are not referred. You need to implement your maximize-line. I have done this manual in my test. Not a complete solution but may give some new ideas to you.

Sub Press_Button()
     
    Dim objIE As Object
    Dim htmlDoc As MSHTML.HTMLDocument
    Dim htmlInput As MSHTML.HTMLInputElement
    Dim htmlColl As MSHTML.IHTMLElementCollection
     
    Set objIE = CreateObject("InternetExplorer.Application")
     
    With objIE
        .Navigate "[URL]https://www.ccilindia.com/Research/Statistics/Pages/NDSOMTradeAnalysisReport.aspx[/URL]" ' Main page
        .Visible = 1
        Do While .READYSTATE <> 4: DoEvents: Loop
        Application.Wait (Now + TimeValue("0:00:02"))
             
        Set htmlDoc = .document
        Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
        Do While htmlDoc.READYSTATE <> "complete": DoEvents: Loop
                 
        'click ExportToExcel
        Set htmlDoc = .document
        Set htmlColl = htmlDoc.getElementsByTagName("input")
        Do While htmlDoc.READYSTATE <> "complete": DoEvents: Loop
            For Each htmlInput In htmlColl
                If Trim(htmlInput.ID) = "ctl00_m_g_159d37cd_01de_41af_9f14_06a470dca959_imgBtnExportToExcel" Then
                    htmlInput.Click
                    Exit For
                End If
            Next htmlInput
    End With
                 
End Sub