Capture data in one 'destination' file from varied 'source' file stored in one single folder.

[Smits]

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 Jones]

Hi Smita,

See Ron De Bruin's sample code at:

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

 

[Smits]

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

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

 

[Smits]

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.


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

 

[Ron de Bruin]

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