Emulate Excel Web Query in .net

Question

I need to emulate an Excel Web Query in .net Below is the sample code. I get an Error500 when I attempt to do this in .net, however in Excel it works fine. Any ideas on what I am doing wrong? When I change the URI to a normal website it works fine, and returns the html from the page, which i what i am after. I wonder if the problem lies from the fact that I am trying to return a datatable

 Dim oHttpWebRequest As System.Net.HttpWebRequest
    Dim oStream As System.IO.Stream
    Dim sChunk As String
    oHttpWebRequest = (System.Net.HttpWebRequest.Create("http://somesite/foo.jsp"))
    Dim oHttpWebResponse As System.Net.WebResponse = oHttpWebRequest.GetResponse()
    oStream = oHttpWebResponse.GetResponseStream
    sChunk = New System.IO.StreamReader(oStream).ReadToEnd()
    oStream.Close()
    oHttpWebResponse.Close()

Here is the Query from Excel

WEB
1
http:/somesite/foo.jsp

Selection=DataTable
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

Edit I am getting the error when I getReponse from the server

Answer 1

I found the problem I was having.

I used fiddler to figure out the headers that were being sent via excel and compared those with the headers .net was sending http://www.fiddler2.com/Fiddler2/version.asp

I had to add the following lines of code to add these two headers in order for it to work

oHttpWebRequest.Headers.Add(HttpRequestHeader.Pragma, "no-cache")
oHttpWebRequest.Headers.Add(HttpRequestHeader.AcceptLanguage, "en-us")

Answer 2

nice solution, thanks.