+ Reply to Thread
Results 1 to 20 of 20

VBA to submit Button to download file

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    VBA to submit Button to download file

    Hi Guys,

    I want to save the streaming file to this site:
    http://fm.advfn.com/exchanges/PSE/TE...ect=1417161600

    I am using this code to authenticate:

    Please Login or Register  to view this content.
    Currently the file is in csv format. I want the file to be save in .txt format.

    Thanks.
    Last edited by ermengard; 11-28-2014 at 01:30 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: VBA to submit Button to download file


    Hi !

    A csv file is a text file ‼ So you must well explain …

    And it's at a very beginner level to open a csv file in Excel and to save as you need !
    Last edited by Marc L; 11-28-2014 at 02:18 PM.

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to submit Button to download file

    Marc L,

    Since the file being downloaded is in csv format, what I want is to change the extension name of the file in txt.
    In that case I can import or link the txt file in excel and automatically tab delimited.

    Thank you.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA to submit Button to download file


    Why can't you rename the file ?

    Also read VBA inner help of Workbooks.Open and OpenText methods, beginner level …

  5. #5
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to submit Button to download file

    Hi Marc,

    I think we're not aligned. Yes renaming the file manually is possible.
    What I'm currently pointing is that a macro to download the CSV file from the website. I want to use VBA to send the a submit command to the download button.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA to submit Button to download file


    Why not explain it at first post ?!

    But even by clicking on button, you will have the window to confirm where the text file is saved …

  7. #7
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to submit Button to download file

    Apologies for not explaining it completely.
    All I need is to download the file using VBA.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA to submit Button to download file



    All by VBA : no 'cause you must confirm by key or mouse the text file download …

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Re: VBA to submit Button to download file



    I successfully download the file directly in Excel, are you interested in ?

  10. #10
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to submit Button to download file

    Okay sure. Can you share it?

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this demonstration !


    Code works like a breeze on my side with Excel 2003, Seven and Internet Explorer 9
    but may not work on other Windows or IE versions …

    Code workbook must be opened alone to execute the procedure.
    IE must be visible during the procedure to simulate keystroke.

    Website csv text file is opened in a new workbook, event Deactivate procedure detects it.

    If the file is not splitted in columns, use for example TextToColumns method (or Macro Recorder)
    by amending the event procedure, beginner level …

    Code to paste directly to workbook module ThisWorkbook :
    Please Login or Register  to view this content.
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: VBA to submit Button to download file

    Another way:
    PHP Code: 
    Sub test()

        
    Dim request As Object
        Dim strm 
    As Object
        
        Set strm 
    CreateObject("ADODB.Stream")
        
    Set request CreateObject("winhttp.winhttprequest.5.1")
        
        
    With request
            
    .Open "POST""https://secure.advfn.com/login/secure""False"
            
    .SetRequestHeader "Content-Type""application/x-www-form-urlencoded"
            
    .Send "redirect_url=aHR0cDovL2ZtLmFkdmZuLmNvbS9leGNoYW5nZXMvUFNFL1RFTC90cmFkZXM/dHJhZGVfc2V0PTE0MTcxNTAxOTkmZHRfc2VsZWN0PTE0MTcxNjE2MDA=&site=fm&login_username=jaysixela&login_password=12qwaszx"
            
    Debug.Print .GetAllResponseHeaders()
            .
    Open "GET""http://rpc.advfn.com/latest/trades/history/getFile.jsonp?symbol=PSE%5ETEL&from=1417161600&viewID=26&callback=%3F"False
            
    .Send
        End With
        
        With strm
            
    .Type 1
            
    .Open
            
    .write request.ResponseBody
            
    .savetofile "C:\Temp\Test.txt"2
            
    .Close
        End With
        
    End Sub 

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA to submit Button to download file


    Efficient Kyle, well done !

    Now I have to take time to compare your code with my webbrowser inspector tool
    to see if I could reuse your way on another site …

  14. #14
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to submit Button to download file

    Thank you guys for helping me. Both codes works!!

  15. #15
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to submit Button to download file

    Hi Kyle,

    I'm just wondering, how to find this URL in a webpage?

    I have no knowledge in php code.

    Please Login or Register  to view this content.
    Is the code applicable also in .ASP?

    Thanks.

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: VBA to submit Button to download file

    The server language is irrelevant, this is just HTTP.

    To look at the parameters being passed to the server, you use developer tools. Usually F12 in your browser then in the Network tab (Chrome is probably easiest for this) - this will show you all the requests made by the browser and the response from the server.

    It's then just a case of replicating the request from Excel. That way, you don't need to use a web browser at all - so it speeds things up very significantly

  17. #17
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to submit Button to download file

    Thank you very much kyle,.

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: VBA to submit Button to download file


    Hi Kyle !

    As I yet know how to make some request, easy to reuse your kind of request !
    The only issue I have is with accented characters : "é" instead of "é" or "ö" instead of "ö" for example …
    I have my own UTF8 convert function but when I read the inspector tool response window, either from IE or Firefox,
    accented characters are well converted … I try some request parameter like Charset=UTF8 but with no luck …
    If you have any tip, thanks.
    Last edited by Marc L; 12-09-2014 at 06:53 AM. Reason: typo …

  19. #19
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to submit Button to download file

    Kyle,

    Is it possible also that the data will be directly written on a specific sheet?

  20. #20
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: VBA to submit Button to download file

    That would be an extra step, you need to open the downloaded file and read the data into Excel, it's also a different question so it would be better if you started a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to download a file by click a button on internet link
    By NoreenDeasy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 12:19 PM
  2. [SOLVED] Clicking that open button from file download. My project contd...
    By Zaf01 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2012, 08:02 AM
  3. Replies: 1
    Last Post: 02-05-2012, 09:31 PM
  4. disable save and saveas but have submit button to save file as work
    By briant97 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2010, 12:32 PM
  5. Create a Submit button save filename as A1, submit email unc path, etc.
    By briant97 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-30-2009, 09:52 AM

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