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

[lhmwnr]

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.

[Purfleet]

This sounds like homework to me.

Have you tried to do it yourself?

The code wouldnt be too difficult, but very bespoke to the situation. May i ask why you need each row saved as a seperate text file?

[NoSparks]

Looks like an assignment to me too.
also posted at Excel Forum and VBA Express

[lhmwnr]

Hi Purfleet & NoSparks,

Thanks for your reply and looking into it.
The files are needed as separate text file for transmission into a system.
I am not familiar with VBA so would like to seek experts' advice to automate the process through VBA for my company's work. To reduce the manual effort involved
Thank you in advance.

[Purfleet]

I am still dubious.

How are you doing it manually at the moment? Have you attempted a VBA solution?

As i said it is a very bespoke request, if i was you, i would get it working for 1 row then start adding in each year category as a variable within a loop.

Unfortunately 'VBA Codes' that are generic don’t really exist

[NoSparks]

Good luck Purfleet.
The OP got what they wanted from jindon,
but Is yet to provide any links or advise its been solved.