+ Reply to Thread
Results 1 to 6 of 6

Online data retrieval – can you help me debug my file?

  1. 10-30-2013, 02:20 PM #1
    GriffinCarpenter
    GriffinCarpenter is offline
    Registered User
    Join Date
    07-05-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    13

    Online data retrieval – can you help me debug my file?

    Hi excelforum, I’m hoping you can help me out with an issue.

    I’m new to VBA coding but it’s a skill I would like to develop. Reading solved issues on this forum I have learned a bit and written some code to automatically download fish quota data (TACs) from a European Commission webpage and paste the data files in my workbook. (Once the data is downloaded the output is used in a bioeconomic model in another workbook.) The reason for creating this code is that the online data is updated every year so I’ve created this code so that in the future the data can be easily and automatically downloaded.

    I have attached the file in question. The “source” tab is the one that really matters. The issue is that when I click “get data” the code seems to get stuck after downloading and adding anywhere from 0-15 files to the workbook. I am not sure if this is a coding issue or a web issue or something different entirely.

    Can someone please have a look and provide me with advice?

    Many thanks,
    Griffin
    Attached Files Attached Files
    Last edited by GriffinCarpenter; 10-31-2013 at 02:44 PM.
    Reply With QuoteRegister To Reply
  2. 10-31-2013, 02:49 AM #2
    patel45
    patel45 is offline
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Online data retrieval – can you help me debug my file?

    your code works, but must wait for the loading of the file before loading another, try it with this change
    Please Login or Register  to view this content.
    Last edited by patel45; 10-31-2013 at 02:57 AM.
    If solved remember to mark Thread as solved
    Reply With QuoteRegister To Reply
  3. 10-31-2013, 03:01 AM #3
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Online data retrieval – can you help me debug my file?

    Hi, patel45,

    having a Msgbox puts the code execution to a hold until acknowledged - doing so for 35 times will not speed up the performance of the code.

    I´d try out something like
    Please Login or Register  to view this content.
    @Griffin
    I strongly recommend not to name Variables to the same name as VBA Properties like row. You should gather the variables at the top of the code opposed to declare them next to the code lines where they will be used for the first time.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you
    Reply With QuoteRegister To Reply
  4. 10-31-2013, 11:14 AM #4
    GriffinCarpenter
    GriffinCarpenter is offline
    Registered User
    Join Date
    07-05-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Online data retrieval – can you help me debug my file?

    Hi Holger,

    Thanks so much for taking the time to help. I've change the code with what you've recommended but there seems to be an error now opening up the downloaded file(s). Debugging the error points to the "If Not xlwkb Is Nothing Then" command.

    Was the change you suggested working for you? I've attached the new workbook to this message.

    Thanks again for your help with the code and your formatting suggestion.
    Griffin

    TAC data 2.xlsm
    Reply With QuoteRegister To Reply
  5. 10-31-2013, 11:46 AM #5
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Online data retrieval – can you help me debug my file?

    Looking at the different sheets I would sett the calculation to manual. As far as I can see you don’t need any calculations to be done before you have imported all the xls files.

    So at the start add the line

    Please Login or Register  to view this content.
    and after importing all the files the line

    Please Login or Register  to view this content.
    should set things back to normal.

    Still there is something fishy about the site (pardon the pun). I've managed to loop through all files on two occasions. Having added error trapping I can manage to get away with one error i.e. file not found but if it happens twice macro stops.

    Checking at the site using the row number to find the "faulty" file I see this error message

    Client Failure Limit Exceeded (client_failure_limit_exceeded)


    Too many requests from your ip address (XX.XXX.XXX.XXX) have failed.
    Please verify that the server you are attempting to contact is correct. Continued failures will result in a complete revocation of access.

    For assistance, contact your network support team.
    When that happens I can get a full run if I wait for about half an hour before I do another test.

    Alf

    Ps Oh yeah by the way the last time I tested I got all files without any error so I'll try to upload this file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-31-2013, 02:44 PM #6
    GriffinCarpenter
    GriffinCarpenter is offline
    Registered User
    Join Date
    07-05-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Online data retrieval – can you help me debug my file?

    Wow, you're a star, Alf!

    There is definitely a problem with the website. Running the exact same code twice may stop at different points and return non-existent data deficiencies. It looks like we've come as far as VBA code can take us so I'm happy with the result.

    Thanks Alf, Holger and patel45 for taking the time to respond.
    Griffin
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Build a Macro that can go online and export a data file to excel?
    By WillyBanks in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-06-2013, 10:58 PM
  2. Transferring a defined set of data from an an offline excel file to a shared online file
    By ES79 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-12-2013, 07:38 AM
  3. Very Specific Problem Involving Online Data Retrieval
    By dramatical in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2012, 04:15 PM
  4. Data Retrieval
    By bach in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2005, 10:43 AM
  5. [SOLVED] Automated file name retrieval and updates
    By Ginger in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-07-2005, 04:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1