+ Reply to Thread
Results 1 to 8 of 8

Open XLS file from URL with GET parameters

  1. #1
    Registered User
    Join Date
    11-24-2014
    Location
    Earth
    MS-Off Ver
    15, Excel
    Posts
    6

    Open XLS file from URL with GET parameters

    I can open the XLS file if I subtract the GET parameters but with the GET parameters I can't seem to retrieve the Excel sheet. Any help would be greatly appreciated!

    Here is an example link that I want to open in Excel with VBA,

    http://www.otcmarkets.com/common/Vie...tryIdentifier=

    This is the code I have so far, not sure if I am going the right direction with this or if there is a simpler way.

    Please Login or Register  to view this content.
    Last edited by VBAasdf; 11-24-2014 at 05:09 PM.

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

    Re: Open XLS file from URL with GET parameters

    I suspect that you'll need to write it to disk before trying to open it. The Syntax would then be Workbooks.Open("file Location")

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Open XLS file from URL with GET parameters

    I had a similar problem to yours. See solved thread here http://www.excelforum.com/excel-prog...oft-excel.html

    HTH
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  4. #4
    Registered User
    Join Date
    11-24-2014
    Location
    Earth
    MS-Off Ver
    15, Excel
    Posts
    6

    Re: Open XLS file from URL with GET parameters

    Quote Originally Posted by Kyle123 View Post
    I suspect that you'll need to write it to disk before trying to open it. The Syntax would then be Workbooks.Open("file Location")
    That is what I was suspecting. Can I use WinHttp.WinHttpRequest.5.1 to accomplish this? I can't seem to figure out how to save the server GET response as an .xls

    http://www.otcmarkets.com/common/Vie...tryIdentifier=

    I will try a few ideas and check back tomorrow to tell you if I can get these solutions to work. Thank you both for the assistance!

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

    Re: Open XLS file from URL with GET parameters

    You can used an ADODB stream object to write the binary data to disk, or use the native file writing, though I prefer the ADODB syntax - feels a bit cleaner.

    Winhttp won't make any difference, it's just a more modern request library - if you've got the binary stream anyway there's no real advantage swapping out the requesting library

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Open XLS file from URL with GET parameters

    I had a few minutes free on my hands. Try this. I have already tested it and I get an array of all the raw data in the workbook you want to download.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-24-2014
    Location
    Earth
    MS-Off Ver
    15, Excel
    Posts
    6

    Re: Open XLS file from URL with GET parameters

    mc84excel thanks for the code, it works great except one problem, I need to open the file with the GET parameters so it will open a different Excel sheet, the problem is that site will only let you download the first 2000 columns into the raw Excel Sheet.

    Open this link
    http://www.otcmarkets.com/common/Vie...tryIdentifier=

    Then open this link and compare
    http://www.otcmarkets.com/common/ViewStockScreener.xls

    there is other searches I will want to do with the GET parameters as well but this one is a good test one.

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Open XLS file from URL with GET parameters

    Quote Originally Posted by VBAasdf View Post
    mc84excel thanks for the code, it works great except one problem, I need to open the file with the GET parameters so it will open a different Excel sheet, the problem is that site will only let you download the first 2000 columns into the raw Excel Sheet.
    Sorry 'bout that. I didn't notice it wasn't returning all the data. Unfortunately I cant help you any further as I am not familiar with using winHttpReq. Maybe another Forum User can solve it for you.

+ 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. open web page with required parameters
    By mummy114 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2012, 02:56 PM
  2. [SOLVED] open variably named file, copy/paste data into consolidation file, open next file in list
    By sllawrence1968 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-29-2012, 09:49 PM
  3. Excel for Mac: Open screen sharing with parameters
    By BSoD in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-17-2012, 10:37 AM
  4. [SOLVED] workbooks.open with parameters
    By aceExcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-30-2012, 02:32 AM
  5. workbooks.open with parameters
    By aceExcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-30-2012, 01:56 AM

Tags for this Thread

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