Use VBA to export CSV from webpage

[tryan812]

Hi,

I'm using VBA to access a webpage and run a report that requires user defined fields. After the report generates on the website, I want to export CSV. The website is a SQL server site that contains pre-built queries for non-coders. I successfully wrote code below that takes me to the site and enters date values ("12/21/16") and ("2155"). The report generates, but I don't know how to have it export to CSV. I know you can write the website code and then simply type ".csv" at the end of the URL, but that doesn't work here because first the code needs to run and enter the date parameter and "2155" into fields on the SQL server site.

How can I extract data from a website using VBA, OTHER than using the ".csv" after the URL approach?

The javascript code on the site uses this to export to CSV. On the front end, there is a button I can click to export to CSV, but it has no ID code, so I can't reference it in VBA to click and download (in my VBA code below, I successfully do this in the Click the "View Report" button section).

Also, for security reasons, I did not include the real website URL below.

JavaScript from site that allows user to export data to CSV:

function *******()
{
$find('ReportViewerControl').exportReport('CSV');
}

My VBA that successfully allows me to access the site, enter user inputs, and generate data on the SQL webpage:

Sub test()

' open IE, navigate to the desired page and loop until fully loaded
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
my_url = "http://XXXXXXXXXXX/ReportServer/Pag...Finance_MO/Transaction_List&rs:Command=Render"

With ie
.Visible = True
.Navigate my_url
.Top = 50
.Left = 530
.Height = 400
.Width = 400
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
End With

' Input the start date, end date, and relationship number

Call ie.Document.getElementById("ReportViewerControl_ctl04_ctl03_txtvalue").SetAttribute("value", "12/21/16")
Call ie.Document.getElementById("ReportViewerControl_ctl04_ctl05_txtvalue").SetAttribute("value", "12/21/16")
Call ie.Document.getElementById("ReportViewerControl_ctl04_ctl07_txtvalue").SetAttribute("value", "2155")

' Click the "View Report" button

ie.Document.getElementById("ReportViewerControl_ctl04_ctl00").Click
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

'Download data (NEED HELP FROM MR. EXCEL TO FINISH THIS SECTION

End Sub


Does anyone know how to help me complete the final section? I've looked and can't find anything already posted. It's essentially data sitting on the webpage above, but the data only exists on the original URL (my_url variable above) after the Call inputs have been added.

 

[Ombir]

I would suggest you use Subscription Services in SSRS. You can configure the parameters and email id of recipients. That would be a robust and pretty easy solution.

 

[tryan812]

Thanks Ombir. Yes, our IT department has talked about setting us up with SSRS in the past, but not everyone in my department (Finance) wants to learn a new program. Is there a way to enter input into a Report Server, generate a report, and then export that to csv? Using the method of adding ".csv" to the end of my_url does not work, because none of the user input had occurred yet to generate data. The program needs to first enter the start date, end date, and relationship number, and then download data as a CSV. I was thinking of creating some code that refers to the open active Explorer window and somehow trying to download the data after the user inputs have been entered.

 

[Ombir]

You can also pass the parameters along with report format in the URL as given below:

http://[B]xxxxxxxxxxx[/B]/ReportServer/Pages/ReportViewer.aspx?%2fFinance_MO%2fTransaction_List&sdate=12/21/16&edate=12/21/16&relationno=21555&rs:Command=Render&rs:format=CSV

Change the words highlighted in bold as per your report and server specifications.

 

[tryan812]

No luck. I get this error:

[h=1]Reporting Services Error

---

[/h]

• An attempt was made to set a report parameter
  'ReportViewerControl_ctl04_ctl03_txtvalue' that is not defined in this report.
  (rsUnknownReportParameter) Get Online Help

---

SQL Server Reporting Services

That works for other sites where the URL changes based on the inputs you enter. I successfully created the code below that downloads stock data from google finance and saves it in a folder as "file.csv", which is similar to what you're suggesting except that in my code below, the user needs to enter variables like start date and stock ticker into an "Input" tab in the Excel workbook before running the macro. I think my original request may not be possible due to limitations of the SQL Server reporting services site. It doesn't function like a typical website, such as google.com/finance, because when I enter inputs into the SQL server site, it generates a new report on the screen, but the URL never changes. With google finance, I can see the start date, end date, and ticker in the URL when I generate historical data with my own user inputs (i.e. there is a custom URL string with the specific variables that I requested). I may need to just speak with my IT department to link into our SQL warehouse using the Data tab in Excel, instead of trying to use VBA to access the SQL server site. Thanks for your attempts to help though!

Sub DownloadFile2()
Dim StartMonth As String
Dim StartDay As String
Dim StartYear As String
Dim EndMonth As String
Dim EndDay As String
Dim EndYear As String
Dim Ticker As String
'Get variable inputs for dates and ticker
Workbooks("Google Finance Testing.xlsm").Activate
StartMonth = Worksheets("Input").Cells(11, "C").Value
StartDay = Worksheets("Input").Cells(12, "C").Value
StartYear = Worksheets("Input").Cells(13, "C").Value
EndMonth = Worksheets("Input").Cells(15, "C").Value
EndDay = Worksheets("Input").Cells(16, "C").Value
EndYear = Worksheets("Input").Cells(17, "C").Value
Ticker = Worksheets("Input").Cells(6, "C").Value
Dim myURL As String
myURL = "https://www.google.com/finance/historical?q=NYSE:" & Ticker & "&startdate=" & StartMonth & "+" & StartDay & "%2C+" & StartYear & "&enddate=" & EndMonth & "+" & EndDate & "%2C+" & EndYear & "&num=30&ei=xGtdWLiLBMatmAGD1ZUQ&output=csv"
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.send
myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile "J:\Fin_Acct\Internal\Strategy & Planning\Finance Team\TRyan\Testing\file.csv", 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If
'Refresh data tab
Workbooks("Google Finance Testing.xlsm").Activate
Sheets("Data").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

End Sub

 

[Ombir]

This is not the parameter name "ReportViewerControl_ctl04_ctl03_txtvalue".

You need to find exact parameter name of start data, end date and relationship number. Parameter names are not visible in HTML source of webpage. You can find them in rdl file of your report. Check with the person who designed this report. Parameter names are visible only in the back end not in the front end of report.