User Profile
Michael63
Copper Contributor
Joined 6 years ago
User Widgets
Recent Discussions
What's the best way to collect data from a number of Excel spreadsheets within a Sharepoint folder?
Hello community. I don't know whether this is the right space to post this question, but I haven't found a better place. Here is my problem statement: I need to collect inputs from a variable number of people through an Excel spreadsheet. Each person will get an individual Excel file to fill in the requested data. The structure of this file is the same for all because it is based on a template. Basically, there is a table that people should populate. After people returned the Excel files, they are stored in a Sharepoint folder (with arbitrary filenames!). Now, I would like to collect the data from all files in that Sharepoint folder and copy it into one single table in a separate Excel master spreadsheet automatically. The master spreadsheet is used to analyze the data, run statistics, create dashboards etc. How would you do this? Thanks for your suggestions. Michael.Solved9.2KViews0likes4CommentsRe: What's the best way to collect data from a number of Excel spreadsheets within a Sharepoint folder?
Thanks, Allan. I finally found a solution by this video: https://www.youtube.com/watch?v=3GIz50pftZ0 It shows how to apply data transformation on multiple Excel files with arbitrary filenames in a Sharepoint folder. Thanks so much, Michael8.8KViews0likes0CommentsRe: What's the best way to collect data from a number of Excel spreadsheets within a Sharepoint folder?
Thanks, Allan. I probably should have been more explicit in the problem description. 1) I am collecting data from different teams with their own objectives, and I want to keep the spreadsheets for those teams separate to allow some customization. Another reason why I did not consider this option is that, in my experience, Sharepoint often fails to sync the changes of multiple editors at the same time. So option 1) does not work. 2) Power AUtomate seems to be a powerful tool. However, I have zero experience with that. I would need to deep-dive into it to evaluate. Any further hints are highly appreciated. 3) I have thought about something similar to this option because I use PowerQuery a lot. I know how to import data from an external Excel file by using PowerQuery. However, the challenge is to determine an unknown number of files with arbitrary filenames in a directory. If I could resolve that problem, this approach would be my preferred one. 4) I already tried MS Forms, but I decided this is not the right tool for this problem. It might be good to run surveys but I am collecting a quite huge amount of quantitative data. Thanks, Michael8.6KViews0likes2CommentsCreate a chart based on a subset of the data in a table
Hi, My workbook contains two worksheets, one with the data and another one with charts. Let's say I have a scatter chart that needs to display the data based on specific filter criteria, e.g. a particular month or quarter. I know that I can apply a filter on the data table and the chart would automatically ignore the hidden values. However, I don't want to touch the worksheet with the data table directly. Is there any way how to achieve this? I have experimented also with Advanced Filter but could not get this right.12KViews0likes3CommentsVBA Runtime Error 1004 while assigning formula to a cell
I have created a small macro to apply a currency conversion with a fixed factor on selected cells. Sub ApplyUSD_ConversionRate() ' ' Devides through USD_ConversionRate on selected cells ' Dim cel As Range Dim selectedRange As Range Set selectedRange = Application.Selection For Each cel In selectedRange.Cells Debug.Print cel.Address, cel.Value, cel.HasFormula, cel.Formula If Not cel.HasFormula Then cel.Formula = "=" & cel.Value & "/USD_ConversionRate" >> VBA Runtime Error 1004 “Application-defined or Object-defined error” Else cel.Formula = "=(" & Right(cel.Formula, Len(cel.Formula) - 1) & ")/USD_ConversionRate" End If Next cel End Sub I figured out that I get a Runtime Error 1004 only if the cell contains decimals. Note: I have set the regional settings to German on my computer so the decimal character is comma! Example: In case of success: $T$3 65850 False 65850 In case of error: $T$2 218924,886839899 False 218924.886839899 Notice the difference in the number format of .Value and .Formula property of the cell. I suppose that this might be the reason of the Runtime error. Btw, the code is in a module of the workbook. Any suggestions how I could avoid the Runtime error?14KViews0likes2Comments
Recent Blog Articles
No content to show