Forum Discussion
Import data from excel files with specific file name into another excel sheet
Hi, I need a function/Macro that allows me to import data from files with a specific file name.
More background:
I am downloading a large amount of excel files from an webpage containing Agricultural data for different countries (all files have the same structure in terms of rows and columns). This is done with an Python code that names the files according to a standard of: Country + Crop + Irrigation level + Management level. For instance: GhanaMaizeIrrigationLow.
I have created a excel with drop down menus to select each of these parameters and through the TEXTJOIN function merges it in accordance with the naming of the files.I now need some function/Macro that will import data from specific cells in the file that has the corresponding name. Is there a not too complicated way of doing this?
Thank you so much in advance!
- Hi, your first sentence could be achieved by using Power Query folder connection. 1. All files can be in a folder. 2. In Power Query use the Get Data/From Files/From Folder 3. Select Folder. then Edit the connection and Filter by file name for the files with the names you want. Then all you need to do is refresh the connection and it will pick up the files that match your file name filter. The only bit i cannot tell you how to do is the "...import data from specific cells". However once you have the files you want you can do wonders with the transformation features.
