0

I have a URL that consists of https:// servername/parameterList. If I enter the URL in IE, I'm prompted for username/password, and then have the option to open/save a text file of query results.

What I'm trying to do is get the query results automatically using VBA, either saving it to a file or returning the contents to a variable. I've tried several methods of downloading a file from a URL, including

How do i download a file using VBA (Without internet explorer)

However, this saves a file containing HTML (about the request?), rather than the results of the query that come in the downloadable file. So what I'm getting back is !DOCTYPE html etc., instead of a table of search results in tab-delimited columns.

I think the examples in the link above all have some file you want to download at the end of the URL so that it's a direct link to the file/image.

Sorry if there are questions that address this. I don't think I know what's happening in the process well enough to find meaningful results. Is the initial URL redirecting? If the results file is dynamically generated based on the query, is there any way to get to it? Thanks.

Community
  • 1
  • 1
SteelReyn
  • 53
  • 9

1 Answers1

0

I had the same problem as you, and have gotten the file to download with SendKeys. It's not a robust solution, and I'm having issues with getting it to work with multiple versions of IE, but it gets the job done.

'YourLink is a string of the URL in the form "http://link.com/"

Dim IE As Object
Dim myURL As String

myURL = YourLink

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

IE.navigate myURL

SetWindowPos IE.HWND, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE

'Wait until the web page is fully loaded.

Sleep 1500 'delay in milliseconds

'If they click out of IE, it won't work. This makes sure that if they did, it just stops right there.
If GetForegroundWindow() <> IE.HWND Then
    MsgBox "Please try again and do not click out of the internet explorer window."
    Exit Sub
End If

Sleep 1500

If GetForegroundWindow() <> IE.HWND Then
    MsgBox "Please try again and do not click out of the internet explorer window."
    Exit Sub
End If

'Press keys to download (tabs to save button and then clicks it)

SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True

I'm very much a beginner at programming, and different parts of this code were taken from others' answers to other questions (unfortunately I don't have them written down to properly credit). To use the Sleep, GetForegroundWindow() and SetWindowPos functions, you will need to find the references to put in the module (which should be pretty easy to find). If you have found a better solution since posting this question, please let me know!

aizondy
  • 1
  • 2