+ Reply to Thread
Results 1 to 14 of 14

Compile Error: Argument not optional

  1. #1
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Compile Error: Argument not optional

    Hi I'm following this video as I'm trying to have a live API feed into excel.

    I've used my API key for the data that I need but when I follow the formula from 5.31 onwards I get a Compile Error on the 'Req.Open line'

    Here is my code:

    Private Sub Btn1_click()
    Dim Req as New XMLHTTP
    Req.open 'GET','API Key here'

    Req.send

    I'm aiming to have the small bit of data in the APi key pulled through every time the refresh button is clicked. Currently my sheet just has a refresh button in and I'm trying to code it to automatically place the live data below it.

    I'm a beginner here but can anyone help me understand why I'm getting the complie error or explain a simpler way to pull API data?

    Many thanks

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Compile Error: Argument not optional

    Are sure the single quotes shouldn't be double quotes?
    If posting code please use code tags, see here.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Compile Error: Argument not optional

    I have not watched the video but did it tell you to include a reference to the whichever library contains the details for XMLHTTP?
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    thanks, that helped, I'm now getting a 'Runtime Error - Automation error and unspecified error

    Is using Visual Basic the best way to feed API key codes into excel? I've had a look at the SDK 2013 - will this help or make things easier?

    Thanks

  5. #5
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    Does anyone know how to have a live API feed in excel?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Compile Error: Argument not optional

    Can you post the rest of the code?

    What exactly are you trying to do?

    What libraries have you referenced?

    PS If you do post code, wrap it in code tags so it's readable.

  7. #7
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    Ok so the aim is to get the data below into Excel and have a button as the video in the first link does which automatically updates it when it refreshes.

    The data required from the API key displayed in web browser:

    Please Login or Register  to view this content.
    And the code I'm using in Excel is:

    Please Login or Register  to view this content.
    *for privacy reasons I've hidden the API key and a few other things as they belong to my company.

    I've referenced the XML v6.0 in tools-references in the Visual and copied the same actions done in the video from 5.31 until Req.Send.

    I want to be able to have a live feed of various API's automatically updating in the simplest way possible. This video is the closest I've found so far.

    The data above is Active Users of an app v Date. I want to be able to import all sorts of data like impressions, CPC, revenue, etc and have them all updating and recording data.

    Many thanks,

  8. #8
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    Any ideas?

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Compile Error: Argument not optional

    as Norie already said you should not be using single quotes-which indicate that what follows is a comment-you need double quotes
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  10. #10
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    my apologies, I am actually using double quotes - that is the version before I changed it. Thanks though - any ideas why I'm still experiencing this problem?

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Can you post the current code and tell us what problems you are having with it?

    By the way, when I tried the code XMLHTTP wasn't recognised by any of the XML libraries I tried.

  12. #12
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    O.K I'm a total beginner at Excel VBA programming so I'm following the steps set out in the Weather Forecast video at the start of the thread from 5.31 in. I've tried to just copy the code which I think I'll need in order to get the Data (active users v dates --- in post 7) to appear live in Excel when I click a refresh button as shown in the video.

    Following the video instructions I've created the Button and then added this code into Visual Basic:

    Please Login or Register  to view this content.
    I've copied the code I think I need from the video just to have the data from the API key go into excel at the click of the refresh button.

    I've just ran the code and there were no errors this time. So I'm assuming it's good. However it's not appearing in my workbook. If I want to click the refresh button and have the data appear in two parellel columns, one for dates and one for active users in B6 and C6 respectively, what do I need to add to the code to make it do that?

    Thanks

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Compile Error: Argument not optional

    Accidental dupe.

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Compile Error: Argument not optional

    Where in the code are you putting data onto a worksheet?

    In a video there's a code to loop through all the 'weather' nodes in the returned XML.

    Can you attach a sample workbook?

    PS There's actually a link to the code http://brettdotnet.posterous.com/exc...ith-xmlhttp-we

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Compile Error: Argument not optional

    I've put together a sample workbook, see the attached.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    Thanks, that's interesting. What code/formula did you use to get it to appear in Cells B7-D8? This is what I need to do with my formula... where should I insert the cells where I want the data to be pasted in my code above to get it like yours?

    On a side note, it does seem to take ages to load when I click 'get weather'

  17. #17
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    Thanks, that's interesting. What code/formula did you use to get it to appear in Cells B7-D8? This is what I need to do with my formula... where should I insert the cells where I want the data to be pasted in my code above to get it like yours?

    On a side note, it does seem to take ages to load when I click 'get weather'

  18. #18
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    Thanks, that's interesting. What code would I have to insert in my code above to get my API key for date and active users to appear in the cells like yours does in cells B7-D8?

    On a side note, it does take a long time to load when I click 'Get Weather'

  19. #19
    Forum Contributor
    Join Date
    06-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Compile Error: Argument not optional

    Thanks, that's really interesting. But how would I edit it for my data to come through on refresh? The data I need is in post 7 but where would I enter it into the code and where do I say what cell it goes into? Thanks

+ 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