how to copy data from excel columns in a sheet and append them at end in a different sheet

Hello Experts,
I am new to VB Script. Looking for help on following issue mentioned as step 2,3. Any help is appreciated.
Thanks,
SK
Steps:
1. I am opening excel files in some folders to refresh as files have queries in it. (no issue – done)
2. I need to copy certain data values from the files that i opened in step 1. Basically, I need just two column data A2 and F2
3. I have a master file that has some data already in it in “sheet1”. Now, I have to append copied data values in step 2 to end of this master file sheet1 plus hardcoding some cell values. For example:
In Master file append to end of sheet1:
column A = copied A2 (from step 2) and format the value that is coming as “Sat, 2012-07-06” to
“7/6/2012”. So, column A value will be=7/6/2012
Column B = Hardcoded value “abc”
Column C= Hardcoded value “efg”
Column D= Hardcoded value “0”
Column E= copied F2 (from step 2)

4. Automatically email the master excel (this is for later)


Below is my attempt:

Please Login or Register  to view this content.

Hello SKumar9,

Welcome to the Forum!

There are a couple of points here that need attention. Where is this macro code, in the Master workbook? I ask because you are creating new Excel instances which would make sense if the code were being run from Outlook or some other Office application and not the master. You can open other workbooks in the same instance of Excel.

You are not using a filter for the files you return from the folders. Why? Is this because all your folders contain only Excel workbooks? If you attempt to open a non workbook file then the code will error. With no error handling to exit the loop, you will have to use CTRL+Break to stop the macro.

It will be easier and faster to develop a workable solution if you post a copy of the workbook. The workbook itself can answer a lot of questions ahead of time and get you an answer sooner.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.