+ Reply to Thread
Results 1 to 3 of 3

Download .xls from web then import it in a sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    06-26-2013
    Location
    France
    MS-Off Ver
    Excel 2013
    Posts
    2

    Question Download .xls from web then import it in a sheet

    Hello Excel Gurus,
    I currently have a VBA macro in a Excel xlsm file which opens a webpage then copy&paste the content of the webpage into a cell
    But it's not very practical.

    I would like to modify the following code to change the copy and paste function TO a download xls from web and import function, can you
    help me to do it, because for downloading a xls file, and pasting the content in a cell.. i'm lost and even with much searches on the web
    I can't find the way to do it ..

    The code I'm actually using :
    
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    
    Sub GetTable()
    
    Application.DisplayAlerts = False
       
        Dim ieApp As InternetExplorer
        Dim ieDoc As Object
        Dim ieTable As Object
        Dim clip As DataObject
       
        Set ieApp = New InternetExplorer
       
        ieApp.Visible = True
       
        ieApp.Navigate "https://website/login.jsp"
        Do While ieApp.Busy: DoEvents: Loop
        Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
               
        Set ieDoc = ieApp.Document
       
        With ieDoc.forms(0)
            .login.Value = "LOGIN"
            .Password.Value = "PASSWORD"
            .submit
        End With
        Do While ieApp.Busy: DoEvents: Loop
        Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
        
        Application.Wait DateAdd("s", 3, Now)
        
        Sheets("Sheet1").Delete
        Sheets.Add.Name = "Sheet1"
        Sheets("Sheet1").Activate
        
        OpenClipboard 0
        EmptyClipboard
        CloseClipboard
    
           Do While ieApp.Busy: DoEvents: Loop
           Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
        
            ieApp.Navigate "https://website/htmlstat/userstat?=test"
            Set ieDoc = ieApp.Document
            Range("A100").Select
            ieApp.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
            ieApp.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
            ActiveSheet.Paste
           
        OpenClipboard 0
        EmptyClipboard
        CloseClipboard
    Then the last part of the code ( ieApp.Navigate.... userstat) repeats for each user...

    So, how I can modify the part of this code for downloading a .xls file from web then importing in a cell ?

    Best regards
    Endorfyne

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Download .xls from web then import it in a sheet

    without being able to see the actual website you're using (not "https://website/) and the xls file you try to import, it's pretty hard to give a decent answer to your request....
    Please click the * below if this helps

  3. #3
    Registered User
    Join Date
    06-26-2013
    Location
    France
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Download .xls from web then import it in a sheet

    Hello,

    Here is an example file:
    http://edgepoint.fr/Abonnement_2013-06-25-1.xls

    I just want to import multiple files like that and put
    each one in a specific cell of a unique sheet.

    Sorry about the anonymized URL but it's used in a intranet environement
    that's why I put https://website

    EDIT : The login part is working (for your information)

    Thank you
    Last edited by Endorfyne; 06-26-2013 at 09:12 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1