Download File from reporting services using VBA

Question

I have this script file. I am attempting to download from reporting services using XMLHTTP. If i paste the url in my browser after logging in, the file pops up for download. However when running the code below, it does not work (The code does however work for downloading direct file links"

How can i post the parameters and download the file from the report server? Really appreciate help on this one guys. Here is the code i am working with:

myURL = "http://example.com/reportviewer.aspx?id=1&date=1/3/12&format=PDF"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "user", "pwd"
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 "C:\file.pdf", 2 
    oStream.Close
 End If

By the way the file that is returned (File.pdf) if i rename it to file.html and double click it. Internet explorer automatically popups up with a download notification. I tried to view the source of the file, but it is a link with a unique report execution id, so it will only be good for that one report. Also, the link in there is not to a pdf file but back to the report server.

It's going to be difficult to assist you since you have not provided the minimum code required to replicate your problem condition: the URL you provided is not a real URL, and it also appears (based on your `WinHTTPReq.Open`) that the real URL may be password-protected. A very similar question was asked [here](http://stackoverflow.com/questions/17224915/download-files-from-a-web-page-using-vba-html) and it may not be possible to automate this based on how the data is being served to the user. — David Zemens, May 17 '14 at 17:47
I dont understand your suggestion. Because, first of all the real url is an intranet site which you will not be able to verify from the internet. Secondly as stated above, i am trying to retrieve a file from a reporting server. From my example above it shows that the report server expects parameters. I just need some guidance on how to retrieve the file that is returned. IE how to use the XMLHTTP object to download a report viewer file. — m8L, May 17 '14 at 18:10
My suggestion is that unless we are able to *attempt* the same thing, on the same domain, it makes troubleshooting your problem very difficult or impossible to do. Specifically as pertains to the previous question (linked in my comment), if the "Save As" box is launched from javascript/etc., (it is not the normal Windows "Save As" dialog which you could grab with a WinAPI call...) it may not be possible to automate it from VBA. — David Zemens, May 17 '14 at 19:39
Sorry, but that answer does not pertain to the subject. The link that you pointed to does not deal with reporting services. Alternatively, One does not need to have access to the LAN or the domain/credentials etc. to troubleshoot downloading from reporting services, since it will involve calling the same objects etc. The only difference will be the parameters, which obviously i can modify and insert by myself. — m8L, Jul 13 '14 at 21:53
The issue is about the "download notification" and how *specifically* that is being served to you, irrespective of the object you're using. If this is served from a javascript function (for example) you may not be able to automate it. That is the point of my previous comments: unless we *know* (and you're correct, we need not actually *do* it ourselves) how this is served to you, it is difficult to troubleshoot the specific issue you're having. — David Zemens, Jul 14 '14 at 04:03

score 1 · Answer 1 · answered May 17 '14 at 22:56

The URL has to be 100% correct. Unlike a browser there is no code to fix urls.

The purpose of my program is to get error details.

Try this way using xmlhttp. Edit the url's etc. If it seems to work comment out the if / end if to dump info even if seeming to work. It's vbscript but vbscript works in vb6.

 On Error Resume Next
 Set File = WScript.CreateObject("Microsoft.XMLHTTP")
 File.Open "GET", "http://www.microsoft.com/en-au/default.aspx", False
 'This is IE 8 headers
 File.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 1.1.4322; .NET CLR 3.5.30729; .NET CLR 3.0.30618; .NET4.0C; .NET4.0E; BCD2000; BCD2000)"
 File.Send
 If err.number <> 0 then 
    line =""
    Line  = Line &  vbcrlf & "" 
    Line  = Line &  vbcrlf & "Error getting file" 
    Line  = Line &  vbcrlf & "==================" 
    Line  = Line &  vbcrlf & "" 
    Line  = Line &  vbcrlf & "Error " & err.number & "(0x" & hex(err.number) & ") " & err.description 
    Line  = Line &  vbcrlf & "Source " & err.source 
    Line  = Line &  vbcrlf & "" 
    Line  = Line &  vbcrlf & "HTTP Error " & File.Status & " " & File.StatusText
    Line  = Line &  vbcrlf &  File.getAllResponseHeaders
    wscript.echo Line
    Err.clear
    wscript.quit
 End If

On Error Goto 0

 Set BS = CreateObject("ADODB.Stream")
 BS.type = 1
 BS.open
 BS.Write File.ResponseBody
 BS.SaveToFile "c:\users\test.txt", 2

Also see if these other objects work.

C:\Users>reg query hkcr /f xmlhttp

HKEY_CLASSES_ROOT\Microsoft.XMLHTTP
HKEY_CLASSES_ROOT\Microsoft.XMLHTTP.1.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.3.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.4.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.5.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.6.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.3.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.4.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.5.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.6.0
End of search: 12 match(es) found.

Also be aware there is a limit on how many times you can call any particular XMLHTTP object before a lockout occurs. If that happens, and it does when debugging code, just change to a different xmlhttp object

How I get a correct URL is to type my url in a browser, navigate, and the correct URL is often in the address bar. The other way is to use Properties of a link etc to get the URL. — GoughW, May 17 '14 at 23:04
Also Microsoft.XMLHTTP maps to Microsoft.XMLHTTP.1.0. Msxml2.XMLHTTP maps to Msxml2.XMLHTTP.3.0. Try a later one. — GoughW, May 17 '14 at 23:09
Thanks,but i keep getting the same file back from reporting services. So with the last line. lets say i do this BS.SaveToFile "c:\users\test.html", 2. It will save a HTML file. When i double click this file IE will open up with a download notification to download the file as a PDF (the rendered format). I inspected the code and it just points back to the reporting server. No direct link — m8L, May 20 '14 at 13:48

score 0 · Answer 2 · answered May 17 '14 at 23:08

0

To pass parameters and download a report you can use the ReportExecutionService.Render method of the ReportExecutionService class. The Render method link has example code in VB on how to run the report and download the results. You might find this a bit more flexible than the XMLHTTP approach you are using.

answered May 17 '14 at 23:08

Chris Latta

20,316
4
62
70

Thanks. This looks very interesting and seems like it might be an alternate method. However, we are presently using vbscript. If i exhaust all possibilities using vbscript. then, we will definitely try this method using VB or C# – m8L May 19 '14 at 18:42

Download File from reporting services using VBA

2 Answers2