Copying data from multiple excel files and integrating into a single excel file with a few clicks
Introduction
Sometimes, there is a requirement of collating information from different excel files into a single report. For example, it can be something like reading some entries from a DPR prepared for each day of the month and then collating the information in a single report. This snapshot can give an executive summary and gives a bird’s eye view of the various trends which may be visibly evident.
The figure below shows such a picture, imagine if a hundred files are there, from where the data shown in the positions is to be copy, pasted. The preparation of such reports, say, a monthly report is bit tedious with the user having to open, close, copy, paste multiple entries and which can be error prone, unless multiple level of checking is done.
http://myspace.ongc.co.in/files/app/file/69e85663-d020-4d20-83a3-277643d60727
Ideally, if your job involves making such ‘monthly’ reports, then, it is about time you have a system with a frontend user interface which can be used to enter data and prepare reports and a backend database where the data is stored. However, it requires software development effort to customize and not easily available.
Therefore, the solution presented here would meet the requirement by staying within the excel platform. The solution presented is actually a ‘Macro ‘written in Microsoft Visual basic for application inside the excel environment.
Salient Features
A screen-shot of the macro is shown here which can be launched from excel. All the steps are labelled in the screen shot.
http://myspace.ongc.co.in/files/app/file/c1af28c3-7f57-4e71-a9bb-bc2ce1a8b7ed
- Enter the drive (Ex, C, D etc) where the files are stored and then double click anywhere on the form.
- All the folders contained in the form are listed. Double click on the folder of your interest, the corresponding path is listed in #3 and all the files are listed in #4. However, folders like C:\users and desktop can’t be accessed. Their content can be copied to any new folder in the drive and then proceed further.
- Select the Path in #3 and select the file in #4. Immediately you can see the full path shown in #5.
- Enter the starting row of the excel file report in #6. The report will start appearing from this row onwards. For example, we have written 4. The rows above this row, you can put other information like heading etc.
- In #7, enter the cell-column pairs separated by comma. For example, if you want to copy an entry from cell B5 to cell B4, from B6 to C4, from D7 to D4, from E7 to E4 and A2 to F4, the cell-column pairs would be written as: b5-b,b6-c,d7-d,e7-e,a2-f
Notice, that in the destination cell, only the column# is given (b5-b). The row number remains the same as given in step #4.
- Click on the filename in #4 and then click #9. Repeat the process with all the files in the folder. all the information would be available in C:\test1\test.xlsx
- Now open the directory C:\test1\test.xlsx , the complete information to be copied as per step #5 is available as shown below:-
http://myspace.ongc.co.in/files/app/file/77e1d388-9ac1-47fc-a6c2-abbe4c8ccbd2
The excel file can be downloaded here, copy some source files to a directory in the C drive and then use the application.
Technical Learning :
1. testing the presence of directory
if len(Dir("C:\test1",vbDirectory)=0 then directory does not exist
2. creating a directory
mkDir("directory_path)
3. checking if a file is there or not
filecheck=Dir("C:\test1\test.xlsx")
if filecheck <> "test.xlsx" then file not there
4. creating an excel file using code
Dim ExcelApp as Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Set ExcelWorkbook = ExcelApp.Workbooks.Add
Set ExcelSheet = ExcelWorkbook.Worksheets(1)
ExcelWorkbook.SaveAs "C:\test1\test.xlsx"
ExcelWorkbook.Close savechanges:=False
ExcelApp.Quit
Set ExcelApp = Nothing
Set ExcelWorkbook = Nothing
Set ExcelSheet = Nothing
5. developed code for detecting and removing space (or any ascii char) in a string
6.developed code for extracting source and destination cells from a combination of source-destination cells seperated by comma and then routing the source cells to source array and destination cells to destination array.