Fetching 2 values from 20 excel files

[Thulasiram]

Happy Friday to all the members!

I have a text box in the excel file where the user enters the path in
which many excel files are located. For example, if the user has 20
excel files in C:\files\data, then user would enter C:\files\data in
the text box and would click a command button.

If the command button is clicked, I am looking for the code that
would:
1. Locate all the files with *.xls extension
2. Fetch the data from the cells D7 and D8 from the first worksheet of
each excel workbook.
3. Paste those data in the new excel file in columns A and B starting
with A2 and B2.

i.e. D7 and D8 values from first excel file will go into A2 and B2; D7
and D8 values from second excel file will go into A3 and B3 and so
on.....

Can someone give some tips or code to accomplish this? I would deeply
appreciate this help.

Thank you very much.

 

[stevebriz]

this should get you started:


http://www.rondebruin.nl/copy3.htm

 

[Thulasiram]

I think these topics answer my questions.. I am giving this just for a
reference if someone else end up facing the problem that I have..

http://groups.google.com/group/micr...es+with+xls+extension&rnum=3#37e0e63dbc344bae

http://www.rondebruin.nl/ado.htm

keywords: fetch data, collate data, *.xls extension

If someone can give me some other tips like links to other topics
similar to my case or external links, it would be great.

Thanks!

 

[Ron de Bruin]

Hi Thulasiram

Look also here
http://www.rondebruin.nl/copy3.htm

Test the add-in also
http://www.rondebruin.nl/merge.htm

 

[Thulasiram]

HiThulasiram

Look also herehttp://www.rondebruin.nl/copy3.htm

Test the add-in alsohttp://www.rondebruin.nl/merge.htm

Hi Ron,

Thank you for your reply. I will give them a try and explore the
possibilities of solving my problem.

Regards,
Thulasiram

 

[Thulasiram]

Hi Ron,

Thank you for your reply. I will give them a try and explore the
possibilities of solving my problem.

Regards,Thulasiram

Hi Ron,

I got almost everything correct except but this. The code given below
copies D7 and D8 and copies on A1 and A2 on the new worksheet. How
should the code be tweaked in order to copy the D7 and D8 values to A1
and B1? Please clarify.

If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

'Find the last row with data
rnum = LastRow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "A")

' Copy the workbook name in Column E
sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData MyPath & MyFiles(Fnum), "Cross Axis", "D78",
destrange, False, False
Next
End If

 

[Thulasiram]

Hi Ron,

I got almost everything correct except but this. The code given below
copies D7 and D8 and copies on A1 and A2 on the new worksheet. How
should the code be tweaked in order to copy the D7 and D8 values to A1
and B1? Please clarify.

If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

'Find the last row with data
rnum = LastRow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "A")

' Copy the workbook name in Column E
sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData MyPath & MyFiles(Fnum), "Cross Axis", "D78",
destrange, False, False
Next
End If

Got it!

GetData MyPath & MyFiles(Fnum), "Cross Axis", "D77", destrange,
False, False

Set destrange = sh.Cells(rnum + 1, "B")
GetData MyPath & MyFiles(Fnum), "Cross Axis", "D88",
destrange, False, False

Ron! Your code helped me solve the problem!

 

[Ron de Bruin]

See also this example to create links
http://www.rondebruin.nl/summary2.htm

 

[Thulasiram]

See also this example to create linkshttp://www.rondebruin.nl/summary2.htm

Hi Ron.. That's a cool example. Definitely something I have to look
into.. Thanks!