Copying Data from one sheet to another... issue using End(xldown) in subroutine

The attached file is a crude solution to take data from one worksheet and paste it into another worksheet that I wrote for another excel forum user. To the intended recipient of the attached file, please continue our discussion here.

If anyone else cares to take a look or offer any feedback of how something may be done more efficiently, I welcome your feedback as well. For example, one potential improvement I would like to make is to not re-write over existing data. Instead of using a clever/effective use of the .End(xldown) method, I deleted data and passed a counter: debugCT into a subroutine that is called in a do-while loop. I commented out some lines of code in the following subroutine which approached that solution.

Please Login or Register  to view this content.

So I am writing data to cells: Range(Cells(8,4),Cells(8,9) during the first time the above subroutine is called and I wish to write to row 9, 10, etc. through subsequent passes. I also wish to design the code so that if there is existing data in rows 8 through N, an arbitrary row number, I begin writing data to row N + 1. Again, any feedback is welcome.

why does it not show Monday 21 December TR data "8:00 AM TR Consumer Confidence DEC"?

it seems to start with GB data at 11.00 AM? but TR data at 8.00 AM is before - see attached file


also when you try to populate again, it goes into a loop asking "overwrite data" for every entry. can it refresh the data without asking when you populate.

Even better, remove the populate button altogether and have it automatically populate when the sheet is refreshed for external data retrieval.

Overall I'm very pleased with it, it's exactly what I was after

many thanks

PopulateTable1.xlsm

I can revisit this in about 24 hours.

John,

In order to debug the code, I need the data you're running the code on in order to figure out why it's not capturing those data points. Please post both data sets you ran the code on:
where it missed:
"12:05 AM GB GfK Consumer Confidence DEC"
"09:30 AM GB Public Sector Net Borrowing NOV2"
&
where it missed:
"Monday 21 December TR 8:00 AM TR Consumer Confidence DEC"

Joe

Joe,

The excel document fetches the data from the site automatically (the site has fresh data daily). So just refresh the document to get the latest data and then populate the table. The error (missing data) is consistent, in other words it always fails to pick up the data at the beginning.

I just ran it now (16/01/2016) and it failed to pick up the US data for 18/01/2016 - "Martin L. King Day" - instead it started to read the calendar from 19/01/2016.

So the missing data I quoted you earlier ("12:05 AM GB GfK Consumer Confidence DEC", etc..) belonged to the day that information was published on the site (http://www.tradingeconomics.com/calendar), all you have to do to get the error again, is refresh the sheet and you will see the missing data for the current day.

The problem appears to relate to way the POPULATE function interrogates the list from the calendar that's downloaded - for some reason it ALWAYS misses the data at the beginning of the list, for US, UK and TR.

Thanks... I'll take a look by the end of the week. I've learned a lot since I worked on your project.

Hi Joe,

sorry to pester you again but any chance you could continue? you've written such a good code so far be a waste to leave it now, please please finish it.

sorry again.

thanks

Sure... I've learned a lot about excel VBA since then.

Thanks for the reminder. I'll take a look tonight.

John,

I took another look. You're too kind to say my previous code is good. In my opinion, this code is crap but it should do what you want. And it's fast so you will not notice any differences in speed between optimal code and what is in the attached. I re-wrote the part that almost certainly caused the macro to miss the first part of the data in my previous version, but I kept part of it that may make it difficult to follow.

Improvements to the end result in terms of formatting are possible.

Let me know if there are any issues with the attached.

That is because the attached file was using the old code I corrected in the last file I attached.

It requires you reference the Microsoft Scripting Runtime library because it uses a dictionary object.

I included code in the attached document that does not require you reference that library. If you copy and paste the macro in the attached, it will work. It picked up the set of data you mentioned was missing.

John,

Sorry for the confusion. It looks like my last reply was a response to one of your previous posts.

I did not change any external data links (nor did I see any), so I'm not sure that is on my end. To be sure, here are the changes I made to the original .xlsm document I posted in this thread (months ago). In the attached document, I took no code away. I only added code. Though I didn't see that you had any code...

Changes:
1: I appended the name _Old to the old macro so you can distinguish between the two subroutines (all the code that was there is there now) -- this was & is located in ThisWorkbook
2: I added a new module and re-named it.
3: I added the subroutine identical to what I last posted (so you don't need to reference the Microsoft Scripting Runtime library)
4: I linked the macro button on your Data From The Web worksheet to the subroutine in the new module
5: I created a new worksheet: Copy Of Table so you can check to see the macro works as expected.

Note: the improvement I made eliminates the prompt to overwrite data.

John,

Did you try the file I uploaded today at 10:44 AM? I took the same file and only added a module with the code that doesn't miss any data.

I can work on the other requests, but I want to make sure the code works properly in terms of the data connection. I don't understand the data connection you're referring to. Is it code? If so, where is it? Please describe the data connection and upload a file with a data connection.

Yep... I was able to get it working. Sorry, I've never worked with Data Connections.

I would take that file and add the following code in place of the existing code. If there are any issues with that, let me know. I can work to automatically refresh the data so no button is required, but don't you want add to existing data rather than overwrite it (excluding repeated data points, of course)?

I'll also want to clean up the code below to eliminate unnecessary variables, unnecessary functions, and inefficiently convoluted logic.

Please Login or Register  to view this content.

I can't comment on the code because I don't understand it.

I WOULD WANT IT TO OVERWRITE EXISTING DATA WITH THE NEW DATA ON THE SITE - SO THAT MY EXCEL DOCUMENT ONLY CONTAINS THE EXACT DATA ON THE SITE CURRENTLY - I DON'T WANT IT HOLDING ONTO OLD DATA NOT DISPLAYED ON THE SITE.

BEST NOT TO DO AUTO REFRESH (AUTO REFRESH FOR THE ETERNAL DATA) - I CAN PRESS THE FRESH BUTTON ON EXCEL TOOLBAR WHEN I NEED TO.

BUT IT SHOULD POPULATE THE TABLE WHEN EVER NEW DATA IS RETRIEVED EXTERNALLY. I DON'T WANT A BUTTON TO POPULATE THE TABLE - THIS SHOULD BE AUTOMATIC NOT GETTING EXTERNAL DATA VIA THE DATA CONNECTION TO THE SITE.

John,

I'm sorry if there was some confusion, but I do have it completed up to the point of your basic request. I'll give you my e-mail in a private message so you can get a hold of me more reliably.

I re-designed the portion of the code that does not miss any data and there are no issues with the external connection. All you have to do is copy-and paste the last code I posted into attachment 436203 that you referenced above. If you have any issues doing this, I'll send you a private e-mail with the working attachment.

All further work I'd like to do will be completed approximately once a week. Improvements will include: better formatting, not overwriting data, and automatically populating the table so you don't have to push a button to run the macro. I think we should abandon this thread. Please respond to my private message and we'll take this project further because it interests me and it seems like a very small effort on my part to make a substantial impact for you.

Joe

John,

Yes. Please send me a private message with your e-mail and I'll send you a working file. This thread can no longer help the forum.

Joe

Note that the most-recent format about how it downloads has changed so I edited the code to be able to accommodate spaces in the downloaded data.

did you get my email address?

Yep. Check your inbox.