Dear all,
This would be my first encounter with people - geniuses in excel
programming. I have 1% knowledge in visual basic, specifically in
excel.

Help req for the following:
There is one destination file named Sales Tracker (EXCEL FILE)
There are 'n' no of source file, for e.g. Company XYZ, Company ABC,
Company PQR, Company XXX (EXCEL FILES)

Task is to capture data from source file {Company XYZ , Company ABC,
Company PQR, Company XXX (EXCEL FILES)} - Range ("C6") sales amt along
with the name of the company appearing in range ("C2") and transpose it
to destination file Sales Tracker.

Sample: Source File 'Xyz file'
A B C
1
2 XYZ
3
4
5
6 100

Sample: Destination File 'Sales Tracker
A B C
Name Sales
1 XYZ 100
2 ABC 200
3 PQR 250
4 XXX 30

The way automation req is:
Destination file shall always be open .
1} Once we show a location of folder where all source files are stored.
It should count the no of files stored in it and a loop that many no of
times, it should be devised in such a manner that it opens a source
file, copies from the currently opened source file and pastes data in
destination file and closes the source file. This, loop should
continues till all the files present in folder are tracked in
destination file Sales tracker.

It has been a tedious task for me.
Request for help....

Thnx ^ n

Smita

Hi Smita,

See Ron De Bruin's sample code at:

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


---
Regards,
Norman



"Smits" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Dear all,
> This would be my first encounter with people - geniuses in excel
> programming. I have 1% knowledge in visual basic, specifically in
> excel.
>
> Help req for the following:
> There is one destination file named Sales Tracker (EXCEL FILE)
> There are 'n' no of source file, for e.g. Company XYZ, Company ABC,
> Company PQR, Company XXX (EXCEL FILES)
>
> Task is to capture data from source file {Company XYZ , Company ABC,
> Company PQR, Company XXX (EXCEL FILES)} - Range ("C6") sales amt along
> with the name of the company appearing in range ("C2") and transpose it
> to destination file Sales Tracker.
>
> Sample: Source File 'Xyz file'
> A B C
> 1
> 2 XYZ
> 3
> 4
> 5
> 6 100
>
> Sample: Destination File 'Sales Tracker
> A B C
> Name Sales
> 1 XYZ 100
> 2 ABC 200
> 3 PQR 250
> 4 XXX 30
>
> The way automation req is:
> Destination file shall always be open .
> 1} Once we show a location of folder where all source files are stored.
> It should count the no of files stored in it and a loop that many no of
> times, it should be devised in such a manner that it opens a source
> file, copies from the currently opened source file and pastes data in
> destination file and closes the source file. This, loop should
> continues till all the files present in folder are tracked in
> destination file Sales tracker.
>
> It has been a tedious task for me.
> Request for help....
>
> Thnx ^ n
>
> Smita
>

Norman,

One 'Compile Error: Sub or Function Not Defined' is invoked while
running suggested macro.
This msg is prompted for 'LastRow' in macro

Abstract from macro.

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

Thnx
Smita

===================================
Norman Jones wrote:
> Hi Smita,
>
> See Ron De Bruin's sample code at:
>
> http://www.rondebruin.nl/ado.htm#folder
>
>
> ---
> Regards,
> Norman
>
>
>
> "Smits" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Dear all,
> > This would be my first encounter with people - geniuses in excel
> > programming. I have 1% knowledge in visual basic, specifically in
> > excel.
> >
> > Help req for the following:
> > There is one destination file named Sales Tracker (EXCEL FILE)
> > There are 'n' no of source file, for e.g. Company XYZ, Company ABC,
> > Company PQR, Company XXX (EXCEL FILES)
> >
> > Task is to capture data from source file {Company XYZ , Company ABC,
> > Company PQR, Company XXX (EXCEL FILES)} - Range ("C6") sales amt along
> > with the name of the company appearing in range ("C2") and transpose it
> > to destination file Sales Tracker.
> >
> > Sample: Source File 'Xyz file'
> > A B C
> > 1
> > 2 XYZ
> > 3
> > 4
> > 5
> > 6 100
> >
> > Sample: Destination File 'Sales Tracker
> > A B C
> > Name Sales
> > 1 XYZ 100
> > 2 ABC 200
> > 3 PQR 250
> > 4 XXX 30
> >
> > The way automation req is:
> > Destination file shall always be open .
> > 1} Once we show a location of folder where all source files are stored.
> > It should count the no of files stored in it and a loop that many no of
> > times, it should be devised in such a manner that it opens a source
> > file, copies from the currently opened source file and pastes data in
> > destination file and closes the source file. This, loop should
> > continues till all the files present in folder are tracked in
> > destination file Sales tracker.
> >
> > It has been a tedious task for me.
> > Request for help....
> >
> > Thnx ^ n
> >
> > Smita
> >

Norman,
One 'compile error: Sub or Function not defined' is invoked while
running the suggested macro.

This error is prompted for 'LastRow' word

'Find the last row with data
rnum = LastRow(sh)
===========================
Can you pls help me with this.

There are few more queries related to this macro. Just to understand
what function does some keywords perform.

1} Application.ScreenUpdating = False


2} Below mentioned procedure fills filename in array or assigns file
no. for performing loop.

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

3} 'Loop through all files in the array(myFiles)
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), "Sheet1", "A1:C1",
destrange, False, False
Next
End If

Norman thnx once more coz somebody informed me prior to posting this
msg that it is impossible to perform this act in macro.


===========================

Norman Jones wrote:
> Hi Smita,
>
> See Ron De Bruin's sample code at:
>
> http://www.rondebruin.nl/ado.htm#folder
>
>
> ---
> Regards,
> Norman
>
>
>
> "Smits" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Dear all,
> > This would be my first encounter with people - geniuses in excel
> > programming. I have 1% knowledge in visual basic, specifically in
> > excel.
> >
> > Help req for the following:
> > There is one destination file named Sales Tracker (EXCEL FILE)
> > There are 'n' no of source file, for e.g. Company XYZ, Company ABC,
> > Company PQR, Company XXX (EXCEL FILES)
> >
> > Task is to capture data from source file {Company XYZ , Company ABC,
> > Company PQR, Company XXX (EXCEL FILES)} - Range ("C6") sales amt along
> > with the name of the company appearing in range ("C2") and transpose it
> > to destination file Sales Tracker.
> >
> > Sample: Source File 'Xyz file'
> > A B C
> > 1
> > 2 XYZ
> > 3
> > 4
> > 5
> > 6 100
> >
> > Sample: Destination File 'Sales Tracker
> > A B C
> > Name Sales
> > 1 XYZ 100
> > 2 ABC 200
> > 3 PQR 250
> > 4 XXX 30
> >
> > The way automation req is:
> > Destination file shall always be open .
> > 1} Once we show a location of folder where all source files are stored.
> > It should count the no of files stored in it and a loop that many no of
> > times, it should be devised in such a manner that it opens a source
> > file, copies from the currently opened source file and pastes data in
> > destination file and closes the source file. This, loop should
> > continues till all the files present in folder are tracked in
> > destination file Sales tracker.
> >
> > It has been a tedious task for me.
> > Request for help....
> >
> > Thnx ^ n
> >
> > Smita
> >

Have you read this
(Don't forget to copy the Functions and the GetData macro)

You must copy the function lastrow also in the module


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Smits" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Norman,
> One 'compile error: Sub or Function not defined' is invoked while
> running the suggested macro.
>
> This error is prompted for 'LastRow' word
>
> 'Find the last row with data
> rnum = LastRow(sh)
> ===========================
> Can you pls help me with this.
>
> There are few more queries related to this macro. Just to understand
> what function does some keywords perform.
>
> 1} Application.ScreenUpdating = False
>
>
> 2} Below mentioned procedure fills filename in array or assigns file
> no. for performing loop.
>
> 'Fill the array(myFiles)with the list of Excel files in the folder
> Fnum = 0
> Do While FilesInPath <> ""
> Fnum = Fnum + 1
> ReDim Preserve MyFiles(1 To Fnum)
> MyFiles(Fnum) = FilesInPath
> FilesInPath = Dir()
> Loop
>
> 3} 'Loop through all files in the array(myFiles)
> 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), "Sheet1", "A1:C1",
> destrange, False, False
> Next
> End If
>
> Norman thnx once more coz somebody informed me prior to posting this
> msg that it is impossible to perform this act in macro.
>
>
> ===========================
>
> Norman Jones wrote:
>> Hi Smita,
>>
>> See Ron De Bruin's sample code at:
>>
>> http://www.rondebruin.nl/ado.htm#folder
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Smits" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Dear all,
>> > This would be my first encounter with people - geniuses in excel
>> > programming. I have 1% knowledge in visual basic, specifically in
>> > excel.
>> >
>> > Help req for the following:
>> > There is one destination file named Sales Tracker (EXCEL FILE)
>> > There are 'n' no of source file, for e.g. Company XYZ, Company ABC,
>> > Company PQR, Company XXX (EXCEL FILES)
>> >
>> > Task is to capture data from source file {Company XYZ , Company ABC,
>> > Company PQR, Company XXX (EXCEL FILES)} - Range ("C6") sales amt along
>> > with the name of the company appearing in range ("C2") and transpose it
>> > to destination file Sales Tracker.
>> >
>> > Sample: Source File 'Xyz file'
>> > A B C
>> > 1
>> > 2 XYZ
>> > 3
>> > 4
>> > 5
>> > 6 100
>> >
>> > Sample: Destination File 'Sales Tracker
>> > A B C
>> > Name Sales
>> > 1 XYZ 100
>> > 2 ABC 200
>> > 3 PQR 250
>> > 4 XXX 30
>> >
>> > The way automation req is:
>> > Destination file shall always be open .
>> > 1} Once we show a location of folder where all source files are stored.
>> > It should count the no of files stored in it and a loop that many no of
>> > times, it should be devised in such a manner that it opens a source
>> > file, copies from the currently opened source file and pastes data in
>> > destination file and closes the source file. This, loop should
>> > continues till all the files present in folder are tracked in
>> > destination file Sales tracker.
>> >
>> > It has been a tedious task for me.
>> > Request for help....
>> >
>> > Thnx ^ n
>> >
>> > Smita
>> >
>