Hi,
I want to automate my database Excel file so that it automatically extracts information from the data files stored year/month-wise, but encountering problems because of path name. Hope someone can help please.
Briefly, We receive data in many Excel files each month which are named, say, EXCEL_TEMPLATE_NAME. We store these files in folders which are organised by year (2015, 2016, 2017 etc) and within each year by month (01-January, 02-February.... etc). I am interested in copying just a single column from these Excel files and pasting it as a row (transposed) in my database file. My database Excel file is a single Excel file.
So far, it has been done manually, i.e., we copy the column and then paste it in the database file file as numbers+transpose. It works, but if something is changed in the original file, it won't get updated in the database. That's why I want to 'link' the database to the original files.
I tried the following formula which works (and extracts the value in cell C2 of the EXCEL_TEMPLATE_NAM file. (I extend it to obtain the data from the rest of the rows in the column of interest):
='C:\2017\10-October\[EXCEL_TEMPLATE_NAME.xlsm]TAB_NAME'!$C2
However, the problem occurs when I want to create a new a row for the data from a different month and try to 'replace' the month or the file name in the new row (in the above formula). When I click 'Replace All', a windows (to replace month or the new file name) an explorer dialogue box is opened and I have to specify the file location to make it work. The windows explorer box keeps appearing for each cell in the column!!
Hope there is a better way to day it and I welcome suggestions, please? Thanks
PS: I prefer a formula over macro unless there is no choice. Thanks
I want to automate my database Excel file so that it automatically extracts information from the data files stored year/month-wise, but encountering problems because of path name. Hope someone can help please.
Briefly, We receive data in many Excel files each month which are named, say, EXCEL_TEMPLATE_NAME. We store these files in folders which are organised by year (2015, 2016, 2017 etc) and within each year by month (01-January, 02-February.... etc). I am interested in copying just a single column from these Excel files and pasting it as a row (transposed) in my database file. My database Excel file is a single Excel file.
So far, it has been done manually, i.e., we copy the column and then paste it in the database file file as numbers+transpose. It works, but if something is changed in the original file, it won't get updated in the database. That's why I want to 'link' the database to the original files.
I tried the following formula which works (and extracts the value in cell C2 of the EXCEL_TEMPLATE_NAM file. (I extend it to obtain the data from the rest of the rows in the column of interest):
='C:\2017\10-October\[EXCEL_TEMPLATE_NAME.xlsm]TAB_NAME'!$C2
However, the problem occurs when I want to create a new a row for the data from a different month and try to 'replace' the month or the file name in the new row (in the above formula). When I click 'Replace All', a windows (to replace month or the new file name) an explorer dialogue box is opened and I have to specify the file location to make it work. The windows explorer box keeps appearing for each cell in the column!!
Hope there is a better way to day it and I welcome suggestions, please? Thanks
PS: I prefer a formula over macro unless there is no choice. Thanks
