VBA code to copy data from Excel by rows 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 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.

Originally Posted by hmlaw

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 haven't seen too many questions here get answered when ""steps"" are given. Not sure why. But regardless, what you need to know is that you can't officially copy data from excel to notepad by way of the screen interface of your machine. however, the options to do this with code that I'm aware of are:

=> possibly using the SENDKEYS() methods in VBA.
=> saving your excel file with a .txt extension.
=> using I/O streaming to throw your data from excel into an un-named file with a .txt extension that is created and/or appended to with VBA code.

for the last point, the functions you want to research are as follows:

=> PRINT()
=> WRITE()

https://docs.microsoft.com/en-us/off...printstatement

https://docs.microsoft.com/en-us/off...writestatement

you can also create/append/open .txt files with this coding language by using what is called the fileSystemObject. as you can see from the following articles, these methods also make use of the functions I mentioned above:

https://docs.microsoft.com/en-us/off...extfile-method

https://docs.microsoft.com/en-us/off...extfile-method

Originally Posted by hmlaw;5397261
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

This should do for the uploaded workbook.

Please Login or Register  to view this content.

Hi jindon,

Thanks for very much for the reply and the VBA codes, it worked well.

I have made further changes to the Excel workbook with the following. Sorry for the changes.

1. New columns added between the original col A to F. Col A to J are the metadata information and Col K to P are data from Jan 2000 to Jun 2000.
2. For monthly worksheet, date in source workbook are in MMMYYYY format. Can we convert it to YYYY MMM in the text output files?
I have added the frequency in col F. May I know if VBA codes be able to convert the date based on frequency col?
For monthly worksheets, the periods to be converted to YYYY MMM in the text files and for non-monthly workbook while it will remain as same date value as the workbook.
3. To display value as 250.0 in the text file instead of 250. In the workbook, the number of decimal places to be defaulted to 1 decimal place, but some of the worksheets are in 2 or more decimal places.
Can the VBA codes be able to tell the number of decimal places in the Excel and display the same in the output files.
4. The filename of output files will follow col C (Product ID): e.g. Fruits12345678.txt
5. Can we specify the path of the output text files in another folder different from the Excel workbook, e.g. C:\test\123\

Please refer to the updated workbook and text file in the attachment.

May I have the modified VBA codes, please.
Thank you for your assistance.

Hi vba_php, thanks for your reply and info.

Please Login or Register  to view this content.

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hi jindon,

The codes worked well and thanks very much for your assistance and patience.

May I seek your guidance to explain how does each line of the code works? I would like to gain an understanding of VBA codes so that I can learn to modify them if there is any change in format in future. Thank you.

Happy to help you understand my code, but...

Do a step debug.
while you are in vbe:
1) [View] - [Local Window]
2) Click somewhere in the code.
3) as you hit F8, the code executes one line and you will see all the variables in Local Window.

Try to get the meaning of each lines for yourself first, using VBA Help/any web site.
Then ask specific line(s) that you don't understand, if you still don't understand.

Hi jindon,

Noted, thank you.

If you know of any guide or websites suitable for beginners, please let me know too.
Thanks.