Sharepoint and Excel - combining multiple workbooks into one

[si3po]

Hi all,

I'm not particularly au fait with this, but as the most accomplished Excel user in my office i've been given a task - trend weekly stats on a single Excel report graphically.

Situation:
We take download from our server that creates an *.xlsx file called "yyyymmdd-KPC Report.xlsx" (where yyyymmdd is the current date group for that particular day). This is then uploaded to a central SharePoint folder where it can be accessed by all in our organisation. The process is repeated weekly and a new file created in addition to the existing one, with only the date group portion of the filename being altered to reflect the current date.

Within each "yyyymmdd-KPC Report.xlsx" file there are multiple worksheets (25+ I believe), but always the same worksheet names and all are always present, whether they contain data or not.

Requirement:
Using the ever growing collection of "yyyymmdd-KPC Report.xlsx" files:

• combine all "yyyymmdd-KPC Report.xlsx" files into a single workbook - i would assume this requires the same named worksheets to be present on the combined workbook
• each time a new "yyyymmdd-KPC Report.xlsx" is added to the SharePoint folder, add its data into the combined workbook
• using the data from the combined workbook, create trending analysis graphical displays.

My Initial thoughts:
First, I think, looking at various Google links, i need to create a PowerQuery... although i can't see this at all on my version of Excel (O365, ver 16.xxxxxx) - it may be an organisational thing as nobody in my office has this available? I've also come across PowerBI, and this looks liek it will do what i need, however i do not have a Desktop version, and am advised that we can't request a copy of it locally. I do however have the O365 online version of PowerBi, bat haven't even looks at this yet - it scares me.

Help...
So, here we are. If there are any folk out there able to point me in the right direction, chuck a few ideas this way or suggestions that might push me down the right hill toward a solution - i'm all ears!! Honestly, i could do with a lifebuoy here, because i think i'm in over my depth...

Regards,
Si3PO

 

[Jeffrey Mahoney]

I have done this many times. A macro could be written to look at that SharePoint folder and retrieve the files with "-KPC Report" in the name. A list could be maintained in the master workbook of previously imported reports so that it would duplicate the data. Other macros or Pivot tables could be already setup for the ever expanding tables with no problem.

 

[si3po]

I have done this many times. A macro could be written to look at that SharePoint folder and retrieve the files with "-KPC Report" in the name. A list could be maintained in the master workbook of previously imported reports so that it would duplicate the data. Other macros or Pivot tables could be already setup for the ever expanding tables with no problem.

Interesting, thanks Jeffrey, i'll look into it.

I've been speaking with my IT team and they have agreed to look into obtaining a licence for PowerBI for my and my small team. I wonder if that would be a more streamlined solution and one to look at for longer term use maybe?

 

[Alex Blakenburg]

I have done this many times. A macro could be written to look at that SharePoint folder and retrieve the files with "-KPC Report" in the name

I have seen very little reliable code to access sharepoint folders and files. Can you post some sample code and / or point me to some references ?

 

[si3po]

I have seen very little reliable code to access sharepoint folders and files. Can you post some sample code and / or point me to some references ?

after a fair few hours on the ol' Google today - i too am of this opinion; would really like some assistance here

 

[Alex Blakenburg]

@Jeffrey Mahoney - re: your post #5. Saving & retrieving files from Sharepoint is a bit of a recurring theme in this and other Forums, do you have a reliable way of doing it ?