How to automatically update a database file from new Excel files stored in year/month folders

zacuk

Board Regular
Joined
Dec 22, 2016
Messages
60
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,223,108
Messages
6,170,140
Members
452,304
Latest member
Thelingly95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top