| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture data in one 'destination' file from varied 'source' file stored in one single folder.
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 :) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capture data in one 'destination' file from varied 'source' file stored in one single folder.
Hi Smita,
See Ron De Bruin's sample code at: http://www.rondebruin.nl/ado.htm#folder --- Regards, Norman "Smits" wrote in message ups.com... 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 :) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capture data in one 'destination' file from varied 'source' file stored in one single folder.
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" wrote in message ups.com... 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 :) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capture data in one 'destination' file from varied 'source' file stored in one single folder.
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" wrote in message ups.com... 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 :) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capture data in one 'destination' file from varied 'source' file stored in one single folder.
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" wrote in message oups.com... 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" wrote in message ups.com... 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 :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| delete info from source file w/out changing the destination file | New Users to Excel | |||
| Retaining cell reference in destination, when sorting source file | Excel Worksheet Functions | |||
| How to update destination file with source files closed? | Excel Discussion (Misc queries) | |||
| How to capture file name in a folder? | Excel Programming | |||
| Automatic update of links in destination file when source file mo. | Excel Discussion (Misc queries) | |||
Linear Mode
