VBA: A better solution required for downloading mu

2019-08-13 19:57发布

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 the InternetExplorer.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: My Entire Project for reference

0条回答
登录 后发表回答