0

I am using the below code to download an excel file from a url. The URL (which I can not share here) is for a ticketing system. I used the search button in the website to get all open and in progress tickets. After that there is a Export drop down button and from the drop down I selected Excel. The url I used is the code is the url of this Export to Excel button.

The code is exporting the excel file to given path, but there is only the page header and footer data to show the time of download and two lines saying "Displaying 0 issues at 07/Jul/14 3:14 PM." and "No Issues Found". in the file.

If I use the url directly on any website, it opens the file with data. If I use Workbooks.open "myUrl" same blank file is opening.

Can anyone please check what is wrong?

Sub downloadFile()

Test = Dir("C:\Users\" & Environ("username") & "\" & "SearchResult.xls")
If Not Test = "" Then
    Kill ("C:\Users\" & Environ("username") & "\" & "SearchResult.xls")
 End If

 mylink = ""
 myresult = 0

mylink = "http://mylink.com/issueviews:SearchResult-excel-all-fields/temp/SearchResult.xls?jqlQuery=status+in+%28Open%2C+%22In+Progress%22%29&tempMax=1000"
myresult = URLDownloadToFile(0, mylink, "C:\Users\" & Environ("username") & "\" & "SearchResult.xls", 0, 0)

 If myresult <> 0 Then
    MsgBox "Error downloading " & mylink & Chr(10) & Error(myresult)
 Else
    MsgBox "File has been downloaded"
 End If
End Sub
Community
  • 1
  • 1
user3812755
  • 1
  • 1
  • 1
    There are JavaScript and PHP behind the HTML. Most likely, if "myresult" value (from your code) is 0 and the downloaded file is blank or does not contain the required information, then "mylink" value is incorrect. – StandardDeviation Jul 07 '14 at 16:13
  • Thank you for checking this issue, but I am taking the link from "Right click on the Export button, select Properties and Address(URL). Protocol is shown as HTTP. I can use this URL address directly on the IE and the file is getting downloaded with data. "myresult" value is showing as Zero. – user3812755 Jul 08 '14 at 08:31
  • Not enough information for troubleshooting. Without access to the exact page and without seeing what trafic [Microsoft Sysinternals TCPView](http://technet.microsoft.com/en-us/sysinternals/bb897437) shows. Can you download your file without problems using command line CURL client? – xmojmr Jul 08 '14 at 08:52
  • Could you please let me know how to use CURL client? Please don't mind, I can't share the exact url in this forum because of security reason. – user3812755 Jul 08 '14 at 09:40
  • I've just Googled 2 StackExchange articles that should help you get started with CURL. See http://stackoverflow.com/questions/2710748/run-curl-commands-from-windows-console and http://superuser.com/questions/134685/run-curl-commands-from-windows-console – xmojmr Jul 08 '14 at 10:13
  • Hi, I don't have admin rights to download CURL. Could you please suggest any other way to resolve the issue. If I past the url on a web browser, it is downloading the file with all data. – user3812755 Jul 08 '14 at 10:19
  • Contact administrator of your office and administrator of the webserver "mylink.com" to help you diagnose what is going on from the perspective of network transmissions and what are the requirements (e.g. http response redirects, required authentication headers) for the "Export to Excel" button to work. As it stands now it is not well-defined programming problem (we event don't know what software runs on the "mylink.com" server). It looks like some kind of network problem better suited for site http://superuser.com – xmojmr Jul 08 '14 at 10:41
  • ok, i will try for that, but don't know how much it would help – user3812755 Jul 08 '14 at 13:11
  • see my answer below. If you can talk to the admins they should be able to help you finding out what is going on rather quickly. Otherwise search for some http networking tutorial.. – xmojmr Jul 08 '14 at 13:28

1 Answers1

0

There is not enough information available from the data you have provided.

Basically there is some difference between what happens (on the HTTP protocol query/response level) when you use IE manually and when you run the code.

Typical things are redirecting status codes (see http://en.wikipedia.org/wiki/List_of_HTTP_status_codes#3xx_Redirection) and missing authentication headers (see http://en.wikipedia.org/wiki/HTTP_header)

If you can not install tools (e.g. cURL) it will be difficult to guess as I don't know what kind of tools you have available.

There are some network diagnostics tools built right into web browsers (in Internet Explorer press F12, in Google Chrome press Ctrl+Shift+I)

(EDIT after comments) You can find what is the exact sequence of http requestes/responses exchanged between the web browser and the web server using the network tab in the browser's built-in developer tools.

Once you'll know what is the right sequence of messages try to reproduce it using cURL.

Once you succeed you can try to reproduce the sequence using Excel VBA functions with similar meaning or you can just call cURL command line tool from Excel as described here: Execute a command in command prompt using excel VBA (or in some of its linked or related questions)

Community
  • 1
  • 1
xmojmr
  • 8,073
  • 5
  • 31
  • 54
  • Hi, I managed to download cURL without admin rights, and tried cURL "mylink" on cmd. I am getting some html code on the cmd window, but the file is not getting downloaded. – user3812755 Jul 08 '14 at 14:26
  • 1
    I just had a closer look at the html code on cmd, and it is also saying "No Issues Found" for mylink. It is same as the file download using code. Is there any way to find the exact URL to download the file if the url i am using is wrong. – user3812755 Jul 08 '14 at 14:34
  • @user3812755 yes there is a way to find the URL, see the edited answer. When you make some progress and have some new question leave a comment here (good luck and welcome to Stack Overflow :) – xmojmr Jul 08 '14 at 16:29
  • I don't find network tab on pressing F12 in IE. I am using IE version 8. – user3812755 Jul 09 '14 at 12:12
  • Then upgrade to a [modern browser](http://browsehappy.com/). Google Chrome does not need administrator's rights but will eat A LOT out of your C drive. Mozilla Firefox is reasonable, Ctrl+Shift+Q will open up the network tab – xmojmr Jul 09 '14 at 12:30