+ Reply to Thread
Results 1 to 4 of 4

Excel file in sharepoint - how to auto update or auto export when data is populated

  1. #1
    Registered User
    Join Date
    10-29-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    11

    Excel file in sharepoint - how to auto update or auto export when data is populated

    Hi

    I'm not sure if anyone can help with this issue, but here goes. i will label each element (X)

    I have Forms (A) collecting data based on a few questions, which in turn populates an excel file (B) within sharepoint.

    the data from this file (B) is then required to populate a separate excel file in SharePoint (C)

    so far i can Xlookup from (C) into (B) and pull the data required, but i can only generate fresh data to populate (C) if (B) has been opened and saved, after more entries from (A)

    so far i have tried automate to export the file at intervals, but can not get this to work on adjusted or updated files.

    i have set up sync to one drive to try and gather (C) but again only captures last manual save.

    i'm at a little bit of a loss for what else to try to effectively update (C) without having to open (B)

    suggestions or help would be appreciated.

    KR

    Sam

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    566

    Re: Excel file in sharepoint - how to auto update or auto export when data is populated

    So once more forms have been filled in/updated etc. you want to go straight to File C to pick up changes but you use File B as a middle man to get the form data first?
    If so then C will never be able to update unless you update B and save.

    Not sure what exactly you are doing with File B before its goes to File C but can't you just skip File B (or copy the elements of this into C) and get the form data pulled straight into C?

    Best way to pull data from another file would be to use PowerQuery - depends on what you are doing exactly I guess. PowerQuery allows you to pull data from a file, or even from a folder if you have several files with same structure, and populate in your current workbook (File C) as a table. You can then manipulate the source data as needed. Once there are more forms or data changes (A) you simply refresh the data in File C. Don't need to open the source files - just need them to be save with data changes of course.
    Say thanks, click *

  3. #3
    Registered User
    Join Date
    10-29-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    11

    Re: Excel file in sharepoint - how to auto update or auto export when data is populated

    thanks for the reply Harribone.

    the reason for the middle man sheet (File B) is the file i need to display the end result is used for numerous other stings of data and information.
    currently I am unable to convince people who control the document to allow me to change it.
    also the creator of the forms which gather the responses was not me, so I am gathering the information from already set positions.

    how would I set up a PowerQuery in the fashion you have suggested.
    honestly I have no experience with this type of action.

  4. #4
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    566

    Re: Excel file in sharepoint - how to auto update or auto export when data is populated

    So I have attached 2 files to show you how PowerQuery can make things quick for you. It might look complicated but once you've got your head around it you will realise it's not to much work and the ease of updating is well worth it.

    First though you need to set things up for them to work:

    1) create a folder somewhere, on your desktop for example. Just to contain these files and you can quickly delete later. I will call it Folder01 for the below.
    2) in this folder save the File B.
    3) Create a folder called Data in Folder01.
    4) With Example01 open it and change the data on the first sheet. Save it in the Data folder.
    5) Repeat step 4 so you have several files.

    Right so hopefully you already understand that the Data folder contains the File A's as per your original post. File B of course is File B.
    Open File B. There is a table with one row of data. Right click on it and choose refresh. After a short moment the table will magically show each File A you created with data entered on each.

    At this point you need to take the to to understand that there is a table on File A which is used to create one 'big' table to pull into File B. If that makes sense and you can see the benefits read on for a basic how to.
    This how to guide is a basic start from scratch and you will need to apply to your actual work. File C will be mentioned at the end.


    1) Assuming the File A is to remain unchanged create a new sheet which pulls the information you need into a row. Give each column a heading And then convert to an Excel table (Select the area and hit Ctrl+T)
    2) Save the file. Create a folder where you what to keep the files (when you go live with this all files in this folder need to be the same format - keeping old audits here will result in errors)
    3) Create you File B which will pull the data form your audit files.
    4) In file B select Data>Get Data>From File>From Folder. Might be slightly different with different versions of Excel.
    5) with the pop up choose the folder location where your new File A's are saved.
    6) you should see your files listed, choose Combine>Combine and Load.
    7) A new sheet will appear with a table showing all your File A's.

    Now delete one or more File A's
    ON File B right click on the table and hit refresh. Those files will disappear. Add some File A's and refresh the table. They will appear.
    Once the setup is done it's as simple as that to refresh.

    File C. You can instead use Data>Get Data>From File>From Workbook on this one. Just find File B and then select the sheet or Table which has your data.
    Note you may want to choose Transform instead of Load which will allow you to modify the File B data before it is loaded into File C (doesn't physically change File B).

    I will leave it at that for now to get your head around all this. YouTube and general searches on PowerQuery for Excel will give you the basic starter guides which will help compliment the above and make things clearer.
    Attached Files Attached Files

+ 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. Replies: 0
    Last Post: 10-28-2020, 02:32 PM
  2. Excel file(xlsx or xlsm) auto upload on SharePoint link
    By PaulM100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2018, 02:38 AM
  3. Replies: 3
    Last Post: 01-29-2018, 11:34 AM
  4. Replies: 0
    Last Post: 10-05-2015, 10:45 PM
  5. Auto-install & auto-update add-in using executable file
    By bambaataa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-20-2013, 12:17 AM
  6. Excel data auto update from .dbf file
    By avk in forum Excel General
    Replies: 1
    Last Post: 12-14-2012, 03:10 AM
  7. Auto-Install/Auto-Update Add-In Using Executable File
    By bambaataa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2012, 10:36 AM

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