VBA code to copy data from Excel by rows to multiple text files

lhmwnrexcel

New Member
Joined
Sep 19, 2020
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

I would like to the have the VBA codes to copy data from MS Excel, paste into Notepad and save as text file format (.txt).
Please refer to steps below. I have an MS Excel file with 2 worksheets: Fruits and Vegetables.

1. In worksheet: Fruits, Col A to F are the metadata information and Col G to L are data from Jan 2000 to Jun 2000. Row 1 refers to the headers and row 2 to 4 refers to the data. Some cells may be empty. Please refer to sample data format in the attachment.

2. Can I have the VBA codes to copy data from each row (row 2 onwards) of the worksheet and paste into Notepad and save as 1 txt file per row. The filename will follow col B value. There will be 3 files.

The format of text file for row 2 is:
[Row1] Fixed text
[Row2] Fixed text|insert formula to count number of data point from col G onwards|col B value
[Row3] |col A value: col C value|col B value: col D value|col E value
[Row4] cell G1 value displayed in YYYY MMM|col G value
[Row5] cell G1 value displayed in YYYY MMM|col H value
[Row6] cell G1 value displayed in YYYY MMM|col I value
[Row7] cell G1 value displayed in YYYY MMM|col J value
[Row8] cell G1 value displayed in YYYY MMM|col K value
[Row9] cell G1 value displayed in YYYY MMM|col L value

There will be 3 text files generated from Sheet: Fruits. Please refer to samples in the attachments.

3. The process will repeat for the 2nd worksheet: Vegetables (annual data) till end of the file.

4. In the actual excel file, there will be multiple worksheets (monthly or annual data), X number of data columns from col G onwards and Y number of rows from row 2 onwards. The number of columns and rows will vary in different worksheets.

Appreciate your assistance to advise on the VBA codes.
Thanks.
 

Attachments

  • sample file.PNG
    sample file.PNG
    220.2 KB · Views: 57

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Cross posted multiple sites.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

Please provide links to all other sites where you have asked this question.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top