•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Accessing data from a separate, closed workbook in a distinct folder tree, but without knowing the workbook name
Our take
Accessing data from a dynamically named CSV file can streamline your automated data collection process. If each result is stored in a unique folder based on a defined structure—like Region, Person, and Department—you can leverage VBA or Power Query to extract data efficiently. By utilizing cell values to specify the folder path, you can programmatically identify and access the latest CSV file within that directory, ensuring that you seamlessly retrieve the necessary data without needing to know the exact filename.
Hi, sorry if the title is confusing, but I am wondering if there's a way to automate accessing data from a .csv workbook when I won't know the workbook's name ahead of time.
I have some automated data collection happening, and it generates workbooks in .csv format, with the timestamp of the data as the filename. Because the filename is a timestamp, I cannot predict it ahead of time, but I do have a unique folder for every result genrated.
Is there a way to use VBA or Power Query along with some of my cell values to provide a folder path and have my sheet automatically extract the desired values from whichever .csv is contained in that folder?
For example, my columns might be:
Region1, Person1, Department1, <Value Array>
And my folder structure would be
.../Region1/Person1/Department1/<timestamp>.csv Is there any way to automatically grab one row's worth of data from that CSV given that it's the only file in that folder?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power query multiple workbooks and sheetsI am trying to combine multiple workbooks that have multiple sheets but I am missing a step. Get data, from file, from folder and open. Transform. Add custom column (=Excel.Workbook([Content]). I select data, item and kind from the custom drop down. Then I filter item to select the specific sheets I want. Close and load. When I do this, I only get the filepaths of the sheets I selected. I tried the same steps, but I clicked on the "Table" link in the custom data column before close and load. It brings up the data for that worksheet and it's correct. But when I then close and load, it will bring up the actual data, but only for that worksheet that I looked at. How do I get the *data* from all the worksheets that I selected in custom data to load. Or am I completely off base? Thanks! This is my first foray into power query and it's a bit daunting. submitted by /u/Choice_Intention_778 [link] [comments]
- I’m researching how people automate repetitive Excel/CSV workflows in day-to-day work.I’m building a desktop tool to set up these processes visually, but before going further I want to understand what actually hurts today. I’m not selling anything in this post, just trying to learn from real-world cases. If you work with this kind of thing, I’d really appreciate your input: - What was the last annoying Excel/CSV workflow you automated? - How often does it run? - What was the most frustrating part: combining files, cleaning data, standardizing columns, deduplicating, exporting, etc.? - Are you the only one who runs it, or do other people need to run it too? - What do you use today: Power Query, VBA, Python, Power BI, something else? Thanks! submitted by /u/Remote-Ad-6629 [link] [comments]
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- Converting VBA scripts to Office scripts for easier automationhey everyone, currently i have a VBA script that i manually run everytime a certain file is sent from a particular email address. for context the flow of the automation is done this way: file received through email -> automatic download -> manually trigger the VBA automation -> data from the sent file is transformed and kept in multiple different excel files. the automation itself is slightly lengthy and complicated. It basically converts bunch of data from the file sent, breaks it into different files with the required pieces of data for upload. what im trying to look at is, is there any way to basically eliminate this need to manually tiggering this vba automation myself? i get this report sent multiple times a month and as convenient, my vba automation has made this process its still a hassle 😂. I've heard of Office scripts being an option but can anyone please let me know if its possible? any resources would also be beneficial. thank you. submitted by /u/unlucky_ko [link] [comments]