VBA code to copy data from Excel to multiple text files

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 year 2000 to 2005. Row 1 refers to the headers and row 2 to 4 refers to the data. Some cells may be empty. Sample data format (I use comma to represent different cells):
[Row1] Product code, Product sub-code, Product, Product name, Unit, Details, Jan2000, Feb2000, Mar2000, Apr2000, May2000, Jun2000
[Row2]Fruits01, Fruits01.01, Fruits, Apple, Kg, Max 1000kg, 500.1, 100.3, 300.2, 450.4, 100.6, 250.8
[Row3]Fruits01, Fruits01.02, Fruits, Orange, Kg, , 200, 400, 450, 200, 150, 420
[Row4]Fruits01, Fruits01.03, Fruits, Watermelon, Tonnes, Stock in Jan2000. , 10, 30, 25, 20, 40, 15

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

In the text file (Fruits01.01.txt), the data format will be:
Food
Company|6|Fruits01.01
|Fruits01:Fruits|Fruits01.01:Apple|Kg|Max 1000Kg.
2000 Jan|500.1
2000 Feb|100.3
2000 Mar|300.2
2000 Apr|450.4
2000 May|100.6
2000 Jun|250.8

3. The process will repeat for the 2nd worksheet till end of the file.

4. In the actual excel file, there will be multiple worksheets, 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.

Hi,

Thanks for the reply. I have attached the excel file and a sample text file for your reference, please.
Thanks.