How to Compile the Excel file

[aparvez007]

Hi Ninja & All,


Can any1 give me the code or trick to compile 13 excel file in one. (All excel file have same header). Please help me...


Thanks a lot.

Regards,

Pavi

 

[nazmul_muneer]

@aparvez007,

May be some days ago you posted this problem. Have you seen my response?

You can solve this problem by 3-D References in Excel

You can get help from the link below

http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx

or in Excel Press F1 and search for 3D References. By 3D Reference you can compile data from various sheets as you described in your problem.


Regards,


Muneer

 

[aparvez007]

Dear nazmul_muneer,


Yes i seen ur post but this is different problem.

I m not able to install ASAP due to Administator restriction.


i want to compile 13 file not worksheet.

all 13 file contain 1 sheet only namely(Vehicle Expenses_2000,Vehicle Expenses_2001 till Vehicle Expenses_2012).


Regards,

Pavi

 

[kaushik03]

Hey Pavi,


Thank you for your question.


Can you plz tell us your end requirement of this exercise (combining 13 workbooks into one)?


Would that be wrong if I say you are trying to collate data from 13 excel files( each file contains one sheet) into one master excel workbook, that too in one sheet?


Plz clarify..


Regards,

Kaushik

 

[Debraj]

Hye Pavi!


In my daily I use this file minimum 5 Times a day.. with lots of condition.. Dont worry.. I have removed all the conditioned from it.. and just sharing the basic for you..


https://dl.dropbox.com/u/78831150/Excel/CombinedMultipleFile.xlsm


Just click on the last Icon in QAT.. and select all your 13 files..


I know what are the conditions of my files and how to handle them.. so I have exclude some of the validation's also..

If you need any customization.. don't hesitate to ask for it.. HTH


Regards,

Deb

_____________________________________________________________

[pre]

Option Explicit

Sub ConsolidateMultiplFile()
Dim FileNames, File
Dim lastrow As Long
FileNames = Application.GetOpenFilename(Filefilter:="Excel File (*.xlsx), *.xlsx", MultiSelect:=True)
For Each File In FileNames
With ThisWorkbook.Sheets(1)
lastrow = .Range("A" & .Cells.Rows.Count).End(xlUp).Row
End With
Workbooks.Open File
With ActiveSheet
.UsedRange.Copy ThisWorkbook.Sheets(1).Range("A" & lastrow + 1)
.Parent.Close False
If lastrow > 1 Then
ThisWorkbook.Sheets(1).Rows(lastrow + 1).Delete xlShiftUp
End If
End With
Next File
Sheets(1).Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "Importable CSV File", xlCSV
ActiveWorkbook.Close True
ThisWorkbook.Sheets(1).Cells.Clear
MsgBox "File Consolidation Complete"
End Sub

[/pre]

 

[melzjm]

Hi Debraj Roy,


I just have a question with this code.After clicking the icon in Quick Access Toolbar, then choosing the files to consolidate, it will generate a ****.csv file with all the sheets combined in it but then it leaves the CombinedMultiple.xlsm blank. Is this file's purpose only to run the macro that will generate the .csv?

 

[Debraj]

Hi Melzim,


Yes, I used this file, to combine multiple File and then convert the combined file to CSV., You can change it to any format open able by EXCEl, by changing below line..

ActiveWorkbook.SaveAs ThisWorkbook.Path & "Importable CSV File", xlCSV

Read the Documentation for SAVEAS, and change the last parameter to desired format.


https://dl.dropbox.com/u/78831150/Excel/FileFormatSaveAs.pdf


Regards,

Deb

 

[melzjm]

Hi Deb,


Thanks for your suggestion and the reading. I've learned a lot. I will incorporate this into my final sheet.


-Mel