Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-18-2020, 11:39 PM
lhmwnr lhmwnr is offline VBA code to copy data from Excel by rows to multiple text files Windows 10 VBA code to copy data from Excel by rows to multiple text files Office 2019
Novice
VBA code to copy data from Excel by rows to multiple text files
 
Join Date: Sep 2020
Posts: 2
lhmwnr is on a distinguished road
Default 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.
Attached Files
File Type: xls VBA codes to copy data from Excel to multiple text files.xls (28.5 KB, 7 views)
File Type: txt Fruits01.01.txt (177 Bytes, 9 views)
File Type: txt Vegetables65.103.txt (189 Bytes, 5 views)
Reply With Quote
  #2  
Old 09-19-2020, 01:18 PM
Purfleet Purfleet is offline VBA code to copy data from Excel by rows to multiple text files Windows 10 VBA code to copy data from Excel by rows to multiple text files Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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?
Reply With Quote
  #3  
Old 09-19-2020, 02:14 PM
NoSparks NoSparks is offline VBA code to copy data from Excel by rows to multiple text files Windows 10 VBA code to copy data from Excel by rows to multiple text files Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Looks like an assignment to me too.
also posted at Excel Forum and VBA Express
Reply With Quote
  #4  
Old 09-19-2020, 11:57 PM
lhmwnr lhmwnr is offline VBA code to copy data from Excel by rows to multiple text files Windows 10 VBA code to copy data from Excel by rows to multiple text files Office 2019
Novice
VBA code to copy data from Excel by rows to multiple text files
 
Join Date: Sep 2020
Posts: 2
lhmwnr is on a distinguished road
Default

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.

Last edited by lhmwnr; 09-20-2020 at 02:16 AM. Reason: to refine the post
Reply With Quote
  #5  
Old 09-21-2020, 06:43 PM
Purfleet Purfleet is offline VBA code to copy data from Excel by rows to multiple text files Windows 10 VBA code to copy data from Excel by rows to multiple text files Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #6  
Old 09-21-2020, 08:32 PM
NoSparks NoSparks is offline VBA code to copy data from Excel by rows to multiple text files Windows 10 VBA code to copy data from Excel by rows to multiple text files Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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

Tags
text, vba excel

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA code to copy data from Excel by rows to multiple text files Returns in Word table text become multiple rows when pasted in Excel blorence Word Tables 8 01-23-2019 01:57 PM
VBA code to copy data from Excel by rows to multiple text files how to copy different text from word into excel on consecutive rows mihnea96 Word VBA 4 05-08-2017 12:09 PM
Email Merge: Is It Possible to Include Multiple rows (Excel) of data with For Each Record Emailed? Sgt Rock Mail Merge 6 08-10-2016 11:03 AM
Data Import from multiple word files into an Excel tale asmanokhchi Word 1 04-21-2015 06:24 AM
VBA code to copy data from Excel by rows to multiple text files Copy Multiple Rows to new workbook when multiple criteria is met. flds Excel Programming 5 09-30-2014 09:58 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:32 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft