+ Reply to Thread
Results 1 to 7 of 7

Refreshing data from a file live

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Refreshing data from a file live

    hi all,

    I got a little python script running that takes data from a website every 2-3s. This data is then transformed to just a small table 10x10 and saved in a csv form. This csv file is obviously being refreshed all the time.
    Is there a good way to somehow link an excel file to this table so that I can see the changes live? I've thought that maybe i create a file with mere links to this one and vba as in:

    Please Login or Register  to view this content.
    compared to python though, this code is very performance heavy (takes one core of an i7) and blocks excel from doing anything else (throwing in doevents perhaps?). Perhaps any of you know of a more sophisticated way to get a live feed of that data into excel?

    Thanks !
    If you think that my answer was helpful, please click on the "Add to this user's Reputation" button.

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Refreshing data from a file live

    hi, first of all, why do you open and close the workbook for every update. if you want to watch the updates "live" just open the workbook and then only refresh the query from the csv file every 2-3 seconds.
    However, given that you have external python script, it would be much better (efficient?) if you integrate the python with excel, i.e. python script to update the file itself, instead of writing to csv file.
    Have a look at xlwings module or excelpython for example.
    If you are pleased with a member's answer then use the Star icon to rate it.

  3. #3
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Refreshing data from a file live

    Here some links, that might help

    https://www.pyxll.com/, http://pyxll.com/blog/tag/excelpython/
    http://ericremoreynolds.github.io/excelpython/
    http://xlwings.org/

  4. #4
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Re: Refreshing data from a file live

    Hi Buran,

    thanks so much for your reply!

    The reason why I'd like it to be refreshed in excel automatically is because the source file (updated by python) will be used by multiple people. Most of them don't know anything about python nor vba but its easier to send them an excel file than get a python script running
    I tried the following: open a new workbook; in a formula enter: =ReferenceToPythonFileA1. This obviously worked. When I closed the pythonfile and refreshed it (it has to be closed to write data to it), the excel file does not update the values. Thus the idea about opening and closing the file.
    I found that I can do it with a data connection in excel and simply push "RefreshAll" via vba. this works but is not live - it only refreshes when Refreshall is triggered.

    I'mma read through the linkes provided and see whether I can use one of those. If anyone has a better idea then please dont hesitate

  5. #5
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Refreshing data from a file live

    hi, of course, have a look at the links provided and I'm sure you can get some ideas.
    I don't know how this =ReferenceToPythonFileA1 is supposed to work.

    edited 05.03. the last part from my original post (follows) is not quite correct

    regarding your current workflow:
    1. I assume you have connection to the csv file where python scrip writes data. From Data -> GetExternal Data - From text
    2. your workbook is opened and there is no need to close it. By clicking refresh all from RibbonX or using VBA code) you should be able to refresh the query data. No need to close/open file. it's sufficient to refresh the connection.
    3. Meanwhile python script updates the csv file in the background.
    Last edited by buran; 03-05-2015 at 12:06 AM.

  6. #6
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Refreshing data from a file live

    Actually, I'm quite wrong. refresh of the connection will ask to select the csv file again, so it's not convenient.
    Really sorry for this mistake

  7. #7
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Re: Refreshing data from a file live

    Hey Buran,

    It actually works with refreshing a connection one of the properties in the data Connection tab is something like "confirm path file on refresh". I unlocked that and its semi-live then. Thanks so much for all your help!

+ 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. live file sharing
    By adharper in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-25-2014, 03:06 PM
  2. [SOLVED] Opening, refreshing data connections, saving and closing file with VBA
    By jtukes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2013, 03:31 PM
  3. Excel Live VLookup Live in spreadsheet - Quick way to find them?
    By CHR15T1N4 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2013, 07:33 AM
  4. Macros for refreshing Live Office objects
    By VD82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2012, 02:37 AM
  5. [SOLVED] Refreshing all data imports on different sheets from the same file at the same time
    By ExcelTricks@gmail.com in forum Excel General
    Replies: 2
    Last Post: 08-07-2006, 08:00 PM

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