none
VBA: A better solution required for downloading multiple files RRS feed

  • Question

  • I am using a code to download a csv file from a website. At first I tried the traditional approach of creating an InternetExplorer.Application and so On.. This was the slowest method . Later I figured out the use Of selenium Wrapper and Created this following code :

    'Option Explicit
    
    Sub ScripHistoryDownloader()
    Flag5 = 0
    
    Dim selDriver As Object
    Dim URL As String, Scripcode As String
    Dim StartDate As String, EndDate As String
    Dim ScripHistPATH As String, DownloadedScripHistFILE As String, ScripHistFILE As String
    
    ScripHistPATH = "R:\DataStore\003__ScripHistory\"
    
    Scripcodez = "500010"
    
    ScripHistFILE = ScripHistPATH & Scripcodez & ".csv"
    
    StartDate = "01/01/1990"
    URL = "http://www.bseindia.com/markets/equity/EQReports/StockPrcHistori.aspx?expandable=7&scripcode=" & Scripcodez & "&flag=sp&Submit=G"
    ChromeDownloadsURL = "chrome://downloads/"
    
    
    Set selDriver = CreateObject("SeleniumWrapper.WebDriver")
    selDriver.Start "chrome", "http://www.google.com/"
    selDriver.Open URL
    
    selDriver.Type "id=ctl00_ContentPlaceHolder1_txtFromDate", StartDate
    selDriver.findElementById("ctl00_ContentPlaceHolder1_btnSubmit").Click
    selDriver.Click "id=ctl00_ContentPlaceHolder1_btnSubmit"
    selDriver.clickAndWait "ctl00_ContentPlaceHolder1_btnDownload"
    selDriver.Open ChromeDownloadsURL
    
    'Checking if download is Completed.
    downloadChecker:
        Application.Wait (Now + TimeValue("0:00:05"))
        DownloadedSHFileName = selDriver.findElementByClassName("name").Text
        If DownloadedSHFileName = "" Then
        GoTo downloadChecker
        End If
    
    'Finding out where the file is downloaded and moving it to the desired location.
    ChromeDownloadsHtml = selDriver.getHtmlSource
    PathStartPosition = InStr(ChromeDownloadsHtml, "file:///")
    PathStartPosition = PathStartPosition + 8
    TempPathText = Mid(ChromeDownloadsHtml, PathStartPosition)
    PathEndPosition = InStr(TempPathText, "/" & Scripcodez)
    ScrambledPath = Left(TempPathText, PathEndPosition)
    DownloadedScripHistFILE = Replace(ScrambledPath, "/", "\") & DownloadedSHFileName
    selDriver.stop
    MoveOrRenameFile DownloadedScripHistFILE, ScripHistFILE
    
    Set selDriver = Nothing
    
    Flag5 = 1
    EndOfBhavCopyDownloader:
    End Sub
    
    
    'Function to Move or rename a file or Folder
    Sub MoveOrRenameFile(SourcePath As String, DestinationPath As String)
        Name SourcePath As DestinationPath
    End Sub

    The code runs totally OK. I use this code to download about 3000 files daily and is triggered whenever my computer is ON. My problem is that whenever this code is triggered the chrome browser pops up and also a cmd window.I dont want this popups to happen when using chrome. There is no way to hide the browser and the cmd window like we hide IE using ie.Visible = False in theInternetExplorer.Application. Also opening browser, navigating... makes the process very slow.Is it possible to perform the above code operations using Microsoft.XMLHTTP object ? I have used(with assistance of examples) Microsoft.XMLHTTP object but I haven't filled forms on a website to generate a file and then download it.(I don't have much knowledge about using it)... Can anyone show me a way? Any help is appreciated.

    Note: The posted code is on of the modules from my project. This question is also posted at StackOverflow haven't received any reply so far... Need help badly on this one.. :) Pls help.

    StackOverflow link : http://stackoverflow.com/questions/21767184/vba-a-better-solution-required-for-downloading-multiple-files?noredirect=1#comment32929929_21767184

    My Project link : https://docs.google.com/file/d/0BwoM8DdKUllZVFIxVFN3RWptMjg/edit

    Saturday, February 15, 2014 9:34 PM

All replies

  • Hi,

    Thank you for posting in the MSDN Forum.

    This forum for developers disscussing the issuse about Excel, like using Excel Object model to automate Excel application. Since the issue is more relate to Visual Basic for Applications, I'd like to move it to Visual Basic for Applications forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Best regards

    Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, February 17, 2014 7:22 AM
    Moderator