Fetching 2 values from 20 excel files
 

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.

Fetching 2 values from 20 excel files
 

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/micro...e0e63dbc344bae

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!



On Aug 31, 10:30 am, Thulasiram wrote:
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.

Fetching 2 values from 20 excel files
 

Hi Thulasiram

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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Thulasiram" wrote in message oups.com...
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/micro...e0e63dbc344bae

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!



On Aug 31, 10:30 am, Thulasiram wrote:
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.

Fetching 2 values from 20 excel files
 

On Sep 1, 4:27 pm, "Ron de Bruin" wrote:
HiThulasiram

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

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Thulasiram" wrote in ooglegroups.com...
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/micro...rogramming/bro...

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!

On Aug 31, 10:30 wrote:
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.

Hi Ron,

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

Regards,
Thulasiram

Fetching 2 values from 20 excel files - CODE given
 

On Sep 3, 10:22 am, Thulasiram wrote:
On Sep 1, 4:27 pm, "Ron de Bruin" wrote:



HiThulasiram

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

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Thulasiram" wrote in ooglegroups.com...
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/micro...rogramming/bro...

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!

On Aug 31, 10:30 wrote:
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.

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", "D7:D8",
destrange, False, False
Next
End If

Fetching 2 values from 20 excel files - CODE given
 

On Sep 4, 11:14 am, Thulasiram wrote:
On Sep 3, 10:22 am, Thulasiram wrote:



On Sep 1, 4:27 pm, "Ron de Bruin" wrote:

HiThulasiram

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

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Thulasiram" wrote in ooglegroups.com...
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/micro...rogramming/bro...

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!

On Aug 31, 10:30 wrote:
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.

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", "D7:D8",
destrange, False, False
Next
End If

Got it!

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

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

Ron! Your code helped me solve the problem!

Fetching 2 values from 20 excel files - CODE given
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Thulasiram" wrote in message ups.com...
On Sep 4, 11:14 am, Thulasiram wrote:
On Sep 3, 10:22 am, Thulasiram wrote:



On Sep 1, 4:27 pm, "Ron de Bruin" wrote:

HiThulasiram

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

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Thulasiram" wrote in ooglegroups.com...
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/micro...rogramming/bro...

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!

On Aug 31, 10:30 wrote:
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.

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", "D7:D8",
destrange, False, False
Next
End If

Got it!

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

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

Ron! Your code helped me solve the problem!

Fetching 2 values from 20 excel files - CODE given
 

On Sep 4, 12:14 pm, "Ron de Bruin" wrote:
See also this example to create linkshttp://www.rondebruin.nl/summary2.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Thulasiram" wrote in oglegroups.com...
On Sep 4, 11:14 am, Thulasiram wrote:
On Sep 3, 10:22 am, Thulasiram wrote:

On Sep 1, 4:27 pm, "Ron de Bruin" wrote:

HiThulasiram

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

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Thulasiram" wrote in ooglegroups.com...
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/micro...rogramming/bro...

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!

On Aug 31, 10:30 wrote:
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.

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", "D7:D8",
destrange, False, False
Next
End If

Got it!

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

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

Ron! Your code helped me solve the problem!

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