I finally found the solution to make MSQuery worked with the files in the same folder. What do I mean by that?
MSQuery is a tool that retrieve data from database, either to present the data in an Excel worksheet or to use it with Pivot Table. MSQuery is different from import file function in the way data are updated into the worksheet. Once set up, users just have to click the refresh button or even can allow automatic refresh when the fle is opened. Whereas for import file function, users have to manually activate the import function each time updated data is needed.
When using MSQuery with Excel file as a data source, the path or directory is hardcoded or fixed. This means that if the file is moved to another folder, the data source will remain at the old directory. This is no good for my consolidation as I would like to work with different versions of the data with each set stored in different folders. Therefore, I looked for a solution on the web and found that I could run a simple macro to change the data source path such that it will always refers to source files in the same folder as the consol file. No longer is there a need to change the path of the datasource when a new version is created.
