DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: Download a file over HTTP with VBA

  1. #1
    Join Date
    Sep 2007
    Posts
    1

    Download a file over HTTP with VBA

    I am trying to create a macro in Excel 2003 that will download a text file over the internet. I need it to be in string form so that I can pull out certain information. Everything I have found only downloads the visible content of the web page (web queries, workbooks.open, etc), but this won't work for me because the numbers I need to retrieve are declared as constant variables in a javascript. In VB .NET it is pretty easy to do this:

    Dim wc As New System.Net.WebClient
    URL = "http://www.yahoo.com"
    s = System.Text.Encoding.ASCII.GetString(wc.DownloadData(URL))


    I am not sure about how to do this in VBA. Any suggestions? Thanks.

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Nov 2005
    Posts
    14
    XMLHTTP is very flexible(and fast to write)
    Code:
    Dim objHttp As Object
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    Call objHttp.Open("GET", "http://www.yahoo.com/", False)
    Call objHttp.Send("")
    Call MsgBox(objHttp.ResponseText)

    *
    Last edited by sarun101; 09-16-2007 at 05:29 AM.

  4. #4
    Join Date
    Sep 2007
    Posts
    2

    Question How to put response in a worksheet and response type as datatable with hyperlinks

    I would like to download information from a web page to exel. The information is in a table format and some data in the tables have hyperlinks which I would like to keep with the data.

    How do I modify the response type to denote that it is a table with rich text or html formatting?

    How do I paste the reponse in a worksheet instead of putting it in a message box.

    Please help. Thank you very much.

  5. #5
    Join Date
    Nov 2005
    Posts
    14
    ResponseText is a property of objHttp.
    you can use it like
    text1.text
    yourName.text
    (textbox).text
    etc.

    if you want to show it like a webPage
    you have to use Microsoft Internet Control (shdocvw.dll)
    or Mozilla ActiveXControl
    you can use some of javascript here with object Document
    like
    varObject=WebBrowser1.Document.getElementById("table01")



    **

  6. #6
    Join Date
    Sep 2007
    Posts
    2
    What to do if the page to be dnwloaded is "hidden" behind an ASP request, like in this page?
    http://www.cinematocasa.it/palinsesti.asp

    How can I retrieve pages for the various dates?

  7. #7
    Join Date
    Sep 2007
    Posts
    2
    Thanks sarun101. You've pointed me in the right direction. I added Microsoft Internet Controls and Microsoft Html object library to the vba reference list.

    somehow the WebBrowser.navigate "URL" did not work for me, maybe my system didn't know which web browser I wanted to use. Also I could not dim as HtmlDocument or set to HtmlDocument. When I am in the website I can go file>SaveAs> as webpage, but I could not figure it out from within vba, so instead I copy the info from the webpage and paste it in excel. So here is the code in a workaround. At least it works:

    Sub GetInfo()
    Dim IEApp As InternetExplorer 'now we know which browser is to be used
    'Dim theDoc as HtmlDocument does not work.

    Set IEApp = New InternetExplorer
    With IEApp
    IEApp.Navigate "http:/whatever.com"
    IEApp.Visible = True

    Do
    DoEvents
    Loop Until IEApp.readyState = READYSTATE_COMPLETE
    'here is where I wanted to set theDoc = IEApp.document or _IEApp.htmldocument and then save theDoc, but i cannot figure it out _
    so instead I use select all on the webpage and then copy it.

    Application.SendKeys "^a"
    Application.SendKeys "^c"

    End With
    Windows("myExceldocument").Activate
    Sheets(1).Select
    Range("A1").Select
    ActiveSheet.Paste

    IEApp.Quit
    Set IEApp = Nothing

    End Sub


    This works if the the parameters are embedded in the url. it does not work when in the case of the page that jumpjack has posted there are parameters that has to be selected/entered first before the data is displayed on the web site.

  8. #8
    Join Date
    Nov 2005
    Posts
    14
    with this object U can use some javascript

    like this

    Project1.vbp
    Code:
    Type=Exe
    Reference=*\G{00020430-0000-0000-C000-000000000046}#2.0#0#..\..\..\WINDOWS\system32\STDOLE2.TLB#OLE Automation
    Form=Form1.frm
    Object={EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}#1.1#0; shdocvw.dll
    Startup="Form1"
    Command32=""
    Name="Project1"
    HelpContextID="0"
    CompatibleMode="0"
    MajorVer=1
    MinorVer=0
    RevisionVer=0
    AutoIncrementVer=0
    ServerSupportFiles=0
    VersionCompanyName="Microsoft"
    CompilationType=0
    OptimizationType=0
    FavorPentiumPro(tm)=0
    CodeViewDebugInfo=0
    NoAliasing=0
    BoundsCheck=0
    OverflowCheck=0
    FlPointCheck=0
    FDIVCheck=0
    UnroundedFP=0
    StartMode=0
    Unattended=0
    Retained=0
    ThreadPerObject=0
    MaxNumberOfThreads=1
    
    [MS Transaction Server]
    AutoRefresh=1
    Form1.frm
    Code:
    VERSION 5.00
    Object = "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}#1.1#0"; "shdocvw.dll"
    Begin VB.Form Form1 
       Caption         =   "Form1"
       ClientHeight    =   5085
       ClientLeft      =   60
       ClientTop       =   450
       ClientWidth     =   7695
       LinkTopic       =   "Form1"
       ScaleHeight     =   5085
       ScaleWidth      =   7695
       StartUpPosition =   3  'Windows Default
       Begin SHDocVwCtl.WebBrowser WebBrowser 
          Height          =   3015
          Left            =   600
          TabIndex        =   0
          Top             =   1800
          Width           =   6615
          ExtentX         =   11668
          ExtentY         =   5318
          ViewMode        =   0
          Offline         =   0
          Silent          =   0
          RegisterAsBrowser=   0
          RegisterAsDropTarget=   1
          AutoArrange     =   0   'False
          NoClientEdge    =   0   'False
          AlignLeft       =   0   'False
          NoWebView       =   0   'False
          HideFileNames   =   0   'False
          SingleClick     =   0   'False
          SingleSelection =   0   'False
          NoFolders       =   0   'False
          Transparent     =   0   'False
          ViewID          =   "{0057D0E0-3573-11CF-AE69-08002B2E1262}"
          Location        =   ""
       End
    End
    Attribute VB_Name = "Form1"
    Attribute VB_GlobalNameSpace = False
    Attribute VB_Creatable = False
    Attribute VB_PredeclaredId = True
    Attribute VB_Exposed = False
    Private Sub Form_Load()
    Call WebBrowser.Navigate("http://www.xe.com/")
    End Sub
    
    Private Sub WebBrowser_DocumentComplete(ByVal pDisp As Object, URL As Variant)
    If (URL = "http://www.xe.com/") Then
    WebBrowser.Document.quick.Amount.Value = "43.45"
    WebBrowser.Document.quick.From.selectedIndex = 19
    WebBrowser.Document.quick.To.selectedIndex = 18
    Call WebBrowser.Document.quick.submit
    End If
    End Sub

  9. #9
    Join Date
    Sep 2007
    Posts
    1

    Download a file over HTTP with VBscript and HTML

    Is it possible to use vbscript to do something similar to:

    Dim objHttp As Object
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    Call objHttp.Open("GET", "http://www.example.com/", False)
    Call objHttp.Send("")
    Call MsgBox(objHttp.ResponseText)

    and output into the html with document.write()

    a client of mine creates golf league schedule on his server @ www.example.com that i want to basically "include" to insert it into my site as well?

    thanks in advance,
    Mark

  10. #10
    Join Date
    Nov 2005
    Posts
    14

    yes

    ASP can createobject too.

Similar Threads

  1. File Download
    By NileshNaik in forum Web
    Replies: 1
    Last Post: 11-28-2006, 11:29 AM
  2. File Download
    By mikester20 in forum ASP.NET
    Replies: 0
    Last Post: 10-30-2006, 06:25 PM
  3. wav file fade in out help
    By jase_dukerider in forum C++
    Replies: 2
    Last Post: 04-14-2005, 07:48 PM
  4. Replies: 2
    Last Post: 07-26-2002, 09:23 AM
  5. Replies: 0
    Last Post: 08-22-2000, 10:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center