Macro help required for getting data from multiple excel files.

[saranyas366]

Dear All,

I have 1000s of same structure excel files in a folder.

I want to extract some common details to one single excel file.

Ex. in excel file book1.xls, page a1 relates to name of customer 1, a2 relates to his address, a3 relates his contact number. etc..
As well as in book 2.xls, page a1 relates to name of customer 2, a2 relates to his address, a3 relates his contact number. etc..

I want to extract all these particulars to one single excel page.

I need some expert advice from all of you

Thanks in advance.

Ms. Saranya, Kerala, India

 

[spremkishan]

Please share both the files..
A VBA Macro could solve the issue.

Regards
Prem

 

[sai85]

Dear All,

I have 1000s of same structure excel files in a folder.

I want to extract some common details to one single excel file.

Ex. in excel file book1.xls, page a1 relates to name of customer 1, a2 relates to his address, a3 relates his contact number. etc..
As well as in book 2.xls, page a1 relates to name of customer 2, a2 relates to his address, a3 relates his contact number. etc..

I want to extract all these particulars to one single excel page.

I need some expert advice from all of you

Thanks in advance.

Ms. Saranya, Kerala, India

Hi Saranya,

I guess below code will help you to resolve your issue.

Let me know for any further clarification.

Sub Consolidation()

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Reports.xls"
ThisWorkbook.Activate
Range("C6").Select
Do While ActiveCell.Value <> ""


Workbooks.Open Filename:=ActiveCell.Value
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Reports").Activate
ActiveCell.PasteSpecial xlPasteAll
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ThisWorkbook.Activate
ActiveCell.Offset(1, 0).Select
Loop




End Sub

Kindly Enter the path of source files in MacroFile

For Example: [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]C:\Users\Desktop\Sai\Test1[/TD]
[/TR]
</tbody>[/TABLE]

 

[saranyas366]

[TABLE="width: 480"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[TD]Address 3[/TD]
[TD]Address 4[/TD]
[TD]Address 5[/TD]
[TD]Phone Number[/TD]
[/TR]
[TR]
[TD]Pinson[/TD]
[TD]PADASSERIL HOUSE[/TD]
[TD]KAPLIPPARA[/TD]
[TD]MUNDUR POST[/TD]
[TD]Palakkad[/TD]
[TD]16 kerala[/TD]
[TD="align: right"]9447943121[/TD]
[/TR]
[TR]
[TD]RAMAKRISHNAN M[/TD]
[TD]SREENIVAS'[/TD]
[TD]KALLEKKAD[/TD]
[TD]NEAR AR CAMP[/TD]
[TD]Palakkad[/TD]
[TD]16-Kerala[/TD]
[TD="align: right"]9497963119[/TD]
[/TR]
[TR]
[TD]P A ABDUL GAFOOR[/TD]
[TD]ASHIK MANZIL[/TD]
[TD]KANAYAD[/TD]
[TD]VADAVANNOOR PO[/TD]
[TD]PALAKKAD[/TD]
[TD]16-KERALA[/TD]
[TD="align: right"]9995280130[/TD]
[/TR]
</tbody>[/TABLE]

 

[saranyas366]

Dear Sir,

I tried in VBE...Reports.xls created but one error occured while pressing Run button.

Can you explain with screenshots?

Thanks in advance.