+ Reply to Thread
Results 1 to 31 of 31

Data on Web Query Changes at Midnight (new Day starts) however still require previous data

  1. #1
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Post Data on Web Query Changes at Midnight (new Day starts) however still require previous data

    Need some help please. My excel sheet reads off data from a dynamic webpage that is frequently updated with data. (Web Query)
    Basically, orders/deliveries that are scheduled during the day and the updates consist of the whether the order has been fulfilled or not, etc.
    At Midnight, a whole new data tables come into place (future scheduled orders for the new day).
    However, as we are a night shift team, we need to still use the data from the previous day.
    The URL of the webpage is exactly the same when the dates are changed from the menu of the webpage.
    Is there any way that I can still utilise the previous days data when the day changes at midnight?
    (Note: that my excel sheet has a refresh orders macro which refreshes the sheet to check the webpage if there are any updates)

    Any help would be greatly appreciated

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    you said:
    Quote Originally Posted by ENick View Post
    At Midnight, a whole new data tables come into place (future scheduled orders for the new day).
    However, as we are a night shift team, we need to still use the data from the previous day.
    however, your question was:
    Quote Originally Posted by ENick View Post
    Is there any way that I can still utilise the previous days data when the day changes at midnight?
    you said something about a refresh being set up automatically.

    well of course you probably can get around that. I'm sure there are many ways, but many details are missing, for me anyway, to help you out. what else can you provide. impossible to know what you've done based on what you said.

  3. #3
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Apologies Adam, my english isn't the best.
    I'll try and break it down into points.
    1.) On a day to day basis we get a list of orders arriving into the facility through a webpage
    2.) Since we work night shift, we start from 7pm to 7am.
    3.) At Midnight, the webpage changes to the next day, hence we see orders, etc all scheduled for the next day.
    4.) But as a shift, we still need to have previous days data (since we working from 7pm to 7am)
    5.) Using web query, as you know, we use the URL of the webpage.
    6.) At Midnight the data changes, but the URL remains the same. Hence all the data changes and I lose my previous days data.

    So my qs is, How do I still keep the data of my previous day and carry on adding the new days data (until shift end time) on my excel sheet(ie 7am).
    It's a challenging one

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by ENick View Post
    1.) On a day to day basis we get a list of orders arriving into the facility through a webpage
    ok.

    2.) Since we work night shift, we start from 7pm to 7am.[/quote]
    ok.

    3.) At Midnight, the webpage changes to the next day, hence we see orders, etc all scheduled for the next day.[/quote]
    ok.

    4.) But as a shift, we still need to have previous days data (since we working from 7pm to 7am)[/quote]
    ok. you said that before.

    5.) Using web query, as you know, we use the URL of the webpage.[/quote]
    ok.

    6.) At Midnight the data changes, but the URL remains the same. Hence all the data changes and I lose my previous days data.[/quote]

    Quote Originally Posted by ENick View Post
    So my qs is, How do I still keep the data of my previous day and carry on adding the new days data (until shift end time) on my excel sheet(ie 7am).
    It's a challenging one
    there's only one answer to your issue. you have to store the data in your xl file on another sheet outside of the one that receives the data from the webpage. obviously. Am I right on with that? that's exactly what is sounds like you are doing. are you using power query to get the data?

  5. #5
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Yes, I am using power query to get the data off the webpage and store it on excel.
    The data is being stored on the excel sheet tracker. But it gets updated and the whole data changes at midnight, that's the problem.
    Thinking out aloud, if we could somehow use a screenshot of that data and not refresh that data from 11:59pm? And then add on the following days data to the tracker?
    Not sure? Any ideas on what to do?
    Been struggling with this for a while now

  6. #6
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by ENick View Post
    The data is being stored on the excel sheet tracker.
    I have no idea what that is. what is it? is a ""tracker"" an actual feature of excel?

    Quote Originally Posted by ENick View Post
    But it gets updated and the whole data changes at midnight, that's the problem. if we could somehow use a screenshot of that data and not refresh that data from 11:59pm? And then add on the following days data to the tracker?
    you could possibly, yes, but I can't give ideas unless I know what this tracker thing is that you're talking about. and no, you can't do anything with a screenshot, because a screenshot is not data, it's an image.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    I would suggest that you will need a macro that archives each day's data from the PQ table in your Excel tracker workbook (no, tracker is not an Excel feature, it's just a name used to describe what's going on in the workbook). You would need the macro to run and extract the data, appending it to your archive, each day before the rollover. I am not proficient in VBA, but it must be pretty straightforward for someone who is.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by AliGW View Post
    I would suggest that you will need a macro that archives each day's data from the PQ table in your Excel tracker workbook
    this is essentially what I've been trying to say since we started the discussion, Nick. per Ali, I don't see any other solution to your issue other than this too.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    If that's not what the OP wants, then he isn't explaining his requirements very clearly.

    @ENick - what is your perceived problem with the solution suggested? How will it adversely affect your workflow? Please focus on this now so that we understand what the real issue is.

  10. #10
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Yes a macro that can archive this data from the PQ table in my Excel tracker workbook sounds like a good idea. But then can someone provide me with a sample VBA code for that?
    So basically when the webpage is updated with data entries, this data is stored somewhere in my excel sheet and then the tracker can be updated based on this saved/archived data.
    Then when the day rolls over, we can carry on adding data to the tracker?

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    There are instructions at the top of the page explaining how to attach your sample workbook.

  12. #12
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Apologies, It's difficult to provide a sample workbook as the webpage is only accessible if you're connected to the workplaces private server.
    So can't make a duplicate of that without skewed data.
    Is there any other way?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    No need for the connection - your VBA helper will just want to see the layout of the table generated by the query so that they can suggest code to move its data into an archive tab at the end of each day. So, provide a sample workbook, please.

  14. #14
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Alright will provide a sample workbook within the next 14 hours

  15. #15
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by AliGW View Post
    your VBA helper
    what do you mean Ali? you're not talking about me are you?

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    It's not all about you, Adam!

    I simply mean whichever VBA expert decides to help.

  17. #17
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by AliGW View Post
    It's not all about you, Adam!
    I never said it was.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Anyway, I know that you are really good at VBA (it's in your chosen user name, after all, and you have often hinted at your high level of competence), so I am hoping that you will be able to help.

  19. #19
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by AliGW View Post
    so I am hoping that you will be able to help.
    of course I will be able to. Until I get kicked out of here, that is.

    Quote Originally Posted by AliGW View Post
    Anyway, I know that you are really good at VBA (it's in your chosen user name, after all, and you have often hinted at your high level of competence)
    How do you know that I just don't copy code from the internet every time I help someone? I was paid a bit of money by a 70 year old woman in California 2 weeks ago to give her advice on how to stay away from fake developers that advertise services for high prices, who have no skill whatsoever, but rather just copy code from github and pawn it off as their own. Ever encountered someone like that? They're everywhere.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Until I get kicked out of here, that is.
    Is this your intention? People only get thrown out for being SPAMMERS or completely obnoxious, and I am sure you have no desire to be either.

    As for your credentials: I have no knowledge of your background, just as you have no knowledge of mine. All I know is you keep mentioning how skilled you are. I have to take this at face value.

    Are you able to help the OP with a solution, or are you just clogging up his thread with 'clever' quips?

  21. #21
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by AliGW View Post
    Is this your intention?
    oh come on now Ali, of course not. Please take it in Jest. and by the way, I will be visiting England for the first time this winter or next year. I know many folks over there. Will be meeting them for the first time.

    Quote Originally Posted by AliGW View Post
    All I know is you keep mentioning how skilled you are.
    did I really say that!? More than likely, I will be living in a monastery in a few years at the max, so why would I say anything about how great I am? If I did, point me to the thread so I can take a look. thanks.

    Quote Originally Posted by AliGW View Post

    Are you able to help the OP with a solution
    of course I can. I said that in my last post. we are waiting on the OP to get back to us, per this and this:

    https://www.excelforum.com/excel-pro...ml#post5420452

    https://www.excelforum.com/excel-pro...ml#post5420455

    don't worry about it. rarely has there been a time here where I have NOT come through for you, or the ones who ask questions here. this is how I provide value to this community of yours.

  22. #22
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Hi apologies for the delay.
    This is the best I could manage for a sample workbook.
    This is read off the webpage using PQ.
    If this data could be archived (as suggested) using a macro at 23:59:59 (Just before midnight)
    And then the table could carry on reading data from the next day (using PQ).
    Any help is appreciated
    Attached Files Attached Files

  23. #23
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    put the books in the same dir, Nick. push the button. how close is that? something you're looking for?

    Please Login or Register  to view this content.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Ok, so Adam, it's now been archived but how do we instruct the power query to carry on adding data to this archived data. Because I don't want to disrupt the flow of data.
    How do I ensure this "backup" happens at precisely 23:59:59?

  25. #25
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by ENick View Post
    but how do we instruct the power query to carry on adding data to this archived data.
    I'm sure you can capture a trigger event before PQ downloads the next day's data and attempt's to put it in the same sheet that you have data in, to be moved to the archive book, however I've never done anything like that. Ali, the mod here, knows PQ, but she doesn't know code, per her own words. so, I'd go here:

    https://www.google.com/search?q=exce...werquery+event

    that's the only search I can think of.

    Quote Originally Posted by ENick View Post
    How do I ensure this "backup" happens at precisely 23:59:59?
    you can't run a scheduled task inside of excel, only through windows. VBA can only execute if a book is open. VBA is proprietary to ms office products. set a scheduled task in windows, perhaps?

  26. #26
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Unsure on how to solve this issue :/
    Maybe excel PQ isn't the right way for this

  27. #27
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by ENick View Post
    Maybe excel PQ isn't the right way for this
    more than likely there's more than one way to do it. if you want further assistance from me, I would see everything ur working with.

  28. #28
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Thought of another idea, Can we use an excel macro to enter data into that webpage (date) so we can execute that macro, and then we can get those tables (as per the date) read across into the excel page.

  29. #29
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by ENick View Post
    Thought of another idea, Can we use an excel macro to enter data into that webpage (date) so we can execute that macro, and then we can get those tables (as per the date) read across into the excel page.
    not really following that one, sir. yes, you can enter data into webpages with VBA. however, if the company that owns the page knows anything at all and/or they're a large firm, they will block automation from an external coding source like that. and you will see a javascript dialog from the page if you try to do it. can you explain what you've just said any better? more detail?

  30. #30
    Registered User
    Join Date
    11-04-2020
    Location
    London, England
    MS-Off Ver
    2015
    Posts
    11

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    So if you remember, I told you that the data changes at midnight. I thought I could use a macro that can re-enter my previous date on that webpage and collect the data from there.
    Yes the company owns the page and can only be accessed by it through the server. Does that mean even if I'm using excel on the same server my external coding source will be blocked?

    Sample code by any chance so I could try?

  31. #31
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Data on Web Query Changes at Midnight (new Day starts) however still require previous

    Quote Originally Posted by ENick View Post
    Yes the company owns the page and can only be accessed by it through the server. Does that mean even if I'm using excel on the same server my external coding source will be blocked?
    well probably not. More than likely, your company doesn't know near the amount of stuff that corporations in NYC do. it terms of something to try, look here first:

    https://www.access-programmers.co.uk...ng-vba.176968/

    that article is pretty much a moot point nowadays because MS has foregone IE for Edge. and now they're literally forcing people to switch, and have for a while now. soooo....you can use that article to do what you want, as a sample, if you still, by some miracle, have IE installed. this is the close as i think you're going to get, with regard to what is available inside windows and ms office to manipulate any browser:

    https://codingislove.com/browser-aut...xcel-selenium/

    firefox doesn't have anything, last I read. neither does chrome. and IE has been deprecated. but then again, after running a test myself, it seems that IE is still around on win 10 machines. obviously it is, otherwise I wouldn't have been able to do this (see image below). you can use the code in that image, combined with the link to the ms access forum I gave you above to create your own routine. can you? or do you need more of an example than I've given here?
    Attached Images Attached Images

+ 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. Mail merge that starts a new page when one field is different to the previous record
    By MoreMailMoreProblems in forum Word Formatting & General
    Replies: 6
    Last Post: 04-01-2020, 11:05 PM
  2. [SOLVED] How do I subtract time over midnight the previous day from a vlookup result
    By Titches1 in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 10-27-2019, 11:56 AM
  3. Replies: 4
    Last Post: 08-12-2014, 01:24 PM
  4. Replies: 0
    Last Post: 04-16-2013, 07:41 PM
  5. [SOLVED] Formula that starts where previous row left off
    By jwortsman in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-06-2012, 09:47 AM
  6. Copy old Data from web query while keeping previous days data
    By DRobidoux in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-22-2006, 10:00 AM
  7. QUERY ABOUT DATA REQUIRE
    By RASHESH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2005, 12:06 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