Manually updating Tables?

[ColinJHarrison]

Hi,

I have created a tool to be used by people who are not excel pros.

The tool accepts data from 2 or more csv-style reports (output from other programs) and uses it to calculate a data presentation that corresponds to my users' needs. What I want is a painless way for my users get that csv data into the "back end" of the tool I have made for them.

Because of how I have set up this tool, the tabs that accept the raw data are formatted as Tables. Problem is, for now, copying the data into these Tables is messy. Each csv report will always have the same column order and header names, but the number of rows in each report will be different every time. Just copying the whole csv report and overwriting the whole table doesn't work, as it deletes the Table, which will break the calculations in the main tab.

But any other copy-paste routine is also fraught, (as in the case where the incoming data has less rows, and the result of the copy-paste exercise leaves a few rows of old data at the bottom of the Table...). Also, at least one of these csv reports is liable to have anything up to 5,000 rows. I don't want to require my users to manually select that many rows for copying and pasting if I can avoid it.

I'm sure there's a smarter way of doing this, that is easy for my non-technical users. For now, I do not want to get into Power Queries etc. and I can't use VBA (not permitted on my client's system). Users need to be able to update the tool whenever they want to, and need to be able to understand what they are doing.

I would appreciate any suggestions.

Thanks.

[#2]

@ColinJHarrison Well, Power Query is the tool for you. Set it up correctly once and teach your users to press one button, every time the CSV data changes. No manual process, whatsoever.

[#3]

Hi Riny! Yeahhh, I know... In principle... And in the long run that will probably be the solution I work towards, but my desire to provide a slightly more "hands on" solution in the short term is partially a change management thing, partially an issue with network accessibility (for me).

[#4]

Hi again Riny. OK, so I tried setting up a query, and here's the problem I keep hitting with this client. I use their Sharepoint system. I sync files locally, but I am actually working on their system. Nevertheless, when I set up a power query, it only works locally. When someone else accesses the file (which is saved on Sharepoint) and tries to call the refresh function, they get a [Datasource.error] which clearly is looking for a local path ("C:\Users..."). How do I stop that from happening? :)

[#5 · Accepted solution]

@ColinJHarrison Hi Colin. 'The article in the link below, demonstrates how you can connect PQ to files on Sharepoint or an entire Sharepoint folder.

https://www.myonlinetraininghub.com/get-data-from-onedrive-or-sharepoint-with-power-query