Hello Guys,
I have data in excel file that has its row separated by empty cells. Now, what I want is, to export each batch of data in the row to a separate text. Please see the attached excel file, it makes every think clear.
example.xlsx
Thanks guys.
u want to create text file for that data right
Thanks - Naveed
-----------------------------
If the suggestion helps you, then Click * to Add Reputation
To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
yes, different text file for each group of data in rows separated by empty cells.
try this code..
Step: open the file & press Alt+F11 and run the Macro it will ask you to select the range then select the range which range u want to export to text ok.
hope this willl work
Thanks Naveed,
thanks for your help. but the problem is that I have a huge chunk of data (like 29,000 rows) and the row groups are divided in 10, 100 or 200 set. So each time to export text file, I've to select the whole batch, which is tedious. Instead, I was looking for a vba that automates the whole process.
Basically what it will do is: For example if my data is in Column A, it will start selecting starting from A1 until it finds empty cell and stops selecting then it exports the selected data to text file and renames the text file based on the number that is on the adjacent column (see example.xlsx) and then starts selecting the next column that's below the empty column and selects rows until it finds empty cell again, exports to txt, renames it and does the whole process again until last row.
ohh ok ok i will provide you updated file tonight . i understand the pain
This should do it..
Nice that your groups of data is separated by a blank row allowing for the use of .Areas..
Change the filepath in this line to suit..
Private Sub CommandButton1_Click() Dim fs As Object, a As Object, Z, textname As String, myArea For Each myArea In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(2).Areas textname = myArea.Offset(, 1).Resize(1, 1).Value & ".txt" Set fs = CreateObject("Scripting.FileSystemObject") Set a = fs.CreateTextFile("c:\" & textname, True) For Each Z In myArea a.writeline Z Next Z Next myArea End Sub
Set a = fs.CreateTextFile("c:\" & textname, True)
Last edited by apo; 01-16-2014 at 09:21 AM.
Apo, you just sent me awesomeness in macro enabled workbookI don't know how to thank you enough. Here, take some reputations.
No worries.. glad it helped..
For your info.. in regards to this solution (if you want to know why/how)...
Take a look at :Basically what it will do is: For example if my data is in Column A, it will start selecting starting from A1 until it finds empty cell and stops selecting then it exports the selected data to text file and renames the text file based on the number that is on the adjacent column (see example.xlsx) and then starts selecting the next column that's below the empty column and selects rows until it finds empty cell again, exports to txt, renames it and does the whole process again until last row.
.Areas
Offset
Scripting.FileSystemObject
Combine those 3 things.. (plus a beer or x).. and you get what you got..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks