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: 11

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,892
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,798
Messages
5,542,562
Members
410,560
Latest member
1ndependent
Top