convert the data from .xlsx to .txt file: Export Excel cell values to delimeted text File

**fixer** · 06-09-2020, 11:23 AM

“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthrea...ll=1#post14972
I am no longer monitoring what its doings. It had curiosity appeal for a while, but even that has worn off me now!
It’s getting worse by the Day. Its still doing whatever it is that it is doing and getting Replies and answers at excelforum.com and likely a few places I don’t know about.

**DocAElstein** · 06-10-2020, 04:45 PM

Hi
This is a very similar question to one which I and others have already given you a similar answer many, many times
Here just a few examples :
https://excelfox.com/forum/showthrea...ge28#post13208 b
https://eileenslounge.com/viewtopic....269104#p269104
Never mind.

Text files can take many different forms. You should know this already.
Here, https://excelfox.com/forum/showthrea...ge30#post13348 , I have looked at your latest text file , Alert..txt , in detail.
I have done the same many times for you before.
Your latest text file , Alert..txt , in this thread is a bit unusual, as it has a vbLf as the line ( record ) separator.

This macro does what I think you are asking for.
Note: We are not really converting data from .xlsx to .txt file.
We are making a text file that uses commas , as the values separator, and vbLf as the line ( record ) separator

Code:

' https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file
Sub xlsxTotxt_LineSeperatorvbLf_valuesSeperatorComma()
Rem 1 Workbooks info
Dim Wb1 As Workbook: Set Wb1 = Workbooks("sample2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb1.Worksheets.Item(1)
Dim Lr As Long, Lc As Long
 Let Lr = Ws1.Cells.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
 Let Lc = Ws1.Cells.Item(1, Ws1.Columns.Count).End(xlToLeft).Column
Dim arrIn() As Variant: Let arrIn() = Ws1.Range(Ws1.Range("A1"), Ws1.Cells.Item(Lr, Lc)).Value ' Data range in sample2.xlsx
Rem 2 make text file long string
Dim Rw As Long, Clm As Long '
    For Rw = 1 To Lr ' each row in Ws1
        For Clm = 1 To Lc ' each column for each row in Ws1
        Dim strTotalFile As String
         Let strTotalFile = strTotalFile & arrIn(Rw, Clm) & "," ' add a value and a seperator for this line
        Next Clm
     Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1) ' this will take off the last  ,
     Let strTotalFile = strTotalFile & vbLf  ' this adds the line seperator wanted by Avinash  -  https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page30#post13348  -  You will see that vbLf is the separator for lines(records)
    Next Rw
 Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1)     ' this takes off the last  vbLf
 Debug.Print strTotalFile
Rem 3 make text file from the  total string
Dim FileNum As Long
Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
 Open ThisWorkbook.Path & "\csv Text file Chaos\Alert..txt" For Output As #FileNum  ' CHANGE TO SUIT  ' Will be made if not there
 Print #FileNum, strTotalFile '                      strTotalFile
 Close #FileNum

End Sub

I don’t have any more time for you today. I will look again tomorrow at your latest questions

Alan

Share ‘sample2xlsx’ : https://app.box.com/s/np7kbvjydnyiu95pzyrgn76qi1uqg0ma
vba.xlsm : https://app.box.com/s/juekenyll42z84j6ms7qonzsngnugoyo

**fixer** · 06-10-2020, 05:05 PM

Plz download the sample file and run the macro and see the error
Getting run time error"52"
Bad file name or number
Macro which u have given, is putted in the file

**DocAElstein** · 06-11-2020, 02:01 AM

Hi
The macro seems to work OK using this version of the macro below and your uploaded files
Here is the output Share ‘Alert..txt’ : https://app.box.com/s/vyco4q3kgo8isalq48f926ftroyptri2

Code:

 Sub xlsxTotxt_LineSeperatorvbLf_valuesSeperatorComma()
Rem 1 Workbooks info
Dim Wb1 As Workbook ': Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..xls")
Set Wb1 = Workbooks("Alert..xls")
Dim Ws1 As Worksheet: Set Ws1 = Wb1.Worksheets.Item(1)
Dim Lr As Long, Lc As Long
 Let Lr = Ws1.Cells.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
 Let Lc = Ws1.Cells.Item(1, Ws1.Columns.Count).End(xlToLeft).Column
Dim arrIn() As Variant: Let arrIn() = Ws1.Range(Ws1.Range("A1"), Ws1.Cells.Item(Lr, Lc)).Value ' Data range in sample2.xlsx
Rem 2 make text file long string
Dim Rw As Long, Clm As Long '
    For Rw = 1 To Lr ' each row in Ws1
        For Clm = 1 To Lc ' each column for each row in Ws1
        Dim strTotalFile As String
         Let strTotalFile = strTotalFile & arrIn(Rw, Clm) & "," ' add a value and a seperator for this line
        Next Clm
     Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1) ' this will take off the last  ,
     Let strTotalFile = strTotalFile & vbLf  ' this adds the line seperator wanted by Avinash  -  https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page30#post13348  -  You will see that vbLf is the separator for lines(records)
    Next Rw
 Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1)     ' this takes off the last  vbLf
 Debug.Print strTotalFile
Rem 3 make text file from the  total string
Dim FileNum As Long
Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
 Open ThisWorkbook.Path & "\Alert..txt" For Output As #FileNum
' Open ThisWorkbook.Path & "C:\Users\WolfieeeStyle\Desktop\Alert..txt" For Output As #FileNum  ' CHANGE TO SUIT  ' Will be made if not there
 Print #FileNum, strTotalFile '                      strTotalFile
 Close #FileNum

End Sub

Possibly your error was this

Code:

 Open ThisWorkbook.Path & "C:\Users\WolfieeeStyle\Desktop\Alert..txt" For Output As #FileNum  ' CHANGE TO SUIT  ' Will be made if not there

You probably meant to write this:

Code:

 Open "C:\Users\WolfieeeStyle\Desktop\Alert..txt" For Output As #FileNum  ' CHANGE TO SUIT  ' Will be made if not there

Or this

Code:

 Open ThisWorkbook.Path & "\Alert..txt" For Output As #FileNum  ' CHANGE TO SUIT  ' Will be made if not there

Alan

P.S.
Just to check that you understand what is ThisWorkbook.Path, run this

Code:

Sub WhatIsThisWorkbook_Path()
 MsgBox Prompt:=ThisWorkbook.Path
End Sub

**fixer** · 06-11-2020, 02:18 PM

**fixer** · 09-22-2020, 09:42 PM

Hi,

I have a macro that converts the data from .xlsx to notepad But there is some issue with the notepad data, sometimes my system reads it & sometimes not bcoz the data may have issues like in notepad the data may be as value or the data will
have different format data Or it can be xyz issue

I am sending the notepad file which i downloaded from system & system accept that type of notepad files & reads all the data of notepad files , so plz see the system notepad file & correct the macro according to that , the data which this macro creates should be exact same to same format or types ,i dont know what is the exact issue

Code:

Sub STEP24()
Dim wb1 As Workbook
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\Alert.xlsx")
Dim ws1 As Worksheet: Set ws1 = wb1.Worksheets.Item(1)
Dim lr As Long, Lc As Long
 Let lr = ws1.Cells.Range("A" & ws1.Rows.Count & "").End(xlUp).Row
 Let Lc = ws1.Cells.Item(1, ws1.Columns.Count).End(xlToLeft).Column
Dim arrIn() As Variant: Let arrIn() = ws1.Range(ws1.Range("A1"), ws1.Cells.Item(lr, Lc)).Value
Dim Rw As Long, Clm As Long '
    For Rw = 1 To lr
        For Clm = 1 To Lc
        Dim strTotalFile As String
         Let strTotalFile = strTotalFile & arrIn(Rw, Clm) & ","
        Next Clm
     Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1)
     Let strTotalFile = strTotalFile & vbLf
    Next Rw
 Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1)
 Debug.Print strTotalFile

Dim FileNum As Long
Let FileNum = FreeFile(1)
 Open "C:\Users\WolfieeeStyle\Desktop\Alert..txt" For Output As #FileNum
 Print #FileNum, strTotalFile
 Close #FileNum
 wb1.Close
 
End Sub

Alert.txt is the system data

https://www.excelforum.com/excel-pro...o-notepad.html

**DocAElstein** · 09-24-2020, 10:36 AM

Originally Posted by fixer

....,i dont know what is the exact issue...
First i was using .csv & then i converted that .csv to xlsx & then from xlsx to notepad & then from notepad to trading system
But in notepad also same happens, sometimes it reads data & sometimes it doesn't read

I have explained all the issues in great detail to you very many times already.
But you don’t or won’t understand anything at all so, you will just keep going around in circles, repeating the same questions for ever.
It is not possible to communicate anything to you.

A quick analysis of the text file you uploaded shows that it has the vbLf as line separator. https://excelfox.com/forum/showthrea...ll=1#post14970
But its anyone’s guess what and where that file came from or what it has to do with your question. As usual you are totally incapable of explaining what it is you are wanting, or what problem you are having…

Maybe something might work today if you change
vbLf
to
vbCr & vbLf

But then it won’t work another day. Then you will re post the question
It might work that day if you change
vbCr & vbLf
to
vbLf

But then it won’t work another day. Then you will re post the question
It might work that day if you change
vbLf
to
vbCr & vbLf
or
vbCr

But then it won’t work another day. Then you will re post the question
It might work that day if you change
vbCr
to
vbCr & vbLf
Etc….etc…. for ever and ever….

I have explained this and other issues in great detail to you many times , and given you crystal clear ways to overcome the issues. But it makes no difference, you just keep repeating the question over and over again.
( And I must now have told you nearly 100 times that there is no such things as "Notepad files". ( https://excelfox.com/forum/showthrea...ll=1#post14628 ).
I give up with you now , it's totally pointless telling you, or giving you, anything)

“Moderator” Notice

**I am Banning you to prevent you making any more postings here of the type you have been making here and elsewhere under hundreds of different user names at many of the English speaking Excel and Office help forums for the last couple of years.

The type of post that you have been posting suggest that
_ You may be one person or a !!team of many people working at something organised like a Call Centre.
( !! Sometime when you have been “caught” cross posting, you did not know yourself where you cross posted, and asked to be told. ( Or you maybe only wanted to admit to those where you got “caught”) )
_ You have almost no understanding of the English language
_ You may not have a computer and may have no access to Excel
_ You have no interest in Excel or Excel VBA
_ You have almost no knowledge or interest in any of the questions that you are asking
_ You may be simply offering a service of posting other peoples questions and supplying them with any answers you get.
_ You may be part of the development of a question asking and Replying Bot

_ In some cases, something extremely simple to understand, has been explained to you very many times, in great detail , even graphically, such that even a small mentally handicapped child could understand it and remember it. Despite this, you continually ask exactly that same question over and over again: If you are part of a team interested in only posting questions and taking the answer, then you are very badly organised,
Or
There is no real intelligence behind what is producing your questions and posts
_ One of the things you consistently do after receiving a macro is to delete all explanations, explaining 'comments and all files associated, and indeed it appears as if you try to remove almost all record of the coding and the question and answer. This further encourages the posting of the same or similar questions over and over again.

Whatever you are attempting to do, it appears to be extremely, almost insanely, inefficient ,
compared to
a single person with a computer and Excel, and a minimum of basic Excel VBA knowledge trying to achieve the same.

The main reason for the ban is
Whatever you are attempting to do, it is requiring 10-100 times more time than is typically required of helpers at a forum. All indications are that what you are doing will fail to achieve anything, and is therefore a total waste of everyone’s time. At excelfox, the current small number of helpers have only a limited amount of time, but even if we had more members, excelfox would not be the place for you.
I am totally bored shitless with your Threads that are almost always asking the same thing.
## Some of the major forums may be a good place for you to post. There are some senior brain dead morons there who are happy to keep answering the same questions over and over again. Half of them are probably either senile Dement or just plain stupid anyway, and they don’t remember from one day to the next.

These are some suggestions, from me, on how you should continue
_ If you intend to continue, regardless of any of my previous suggestions, in postings of the type as you have done in the past, then you should think about making some changes to your wording, introduce some new canned replies, possibly organise a new set of similar questions and post at the major forums, such as mrexcel.com, excelforum.com, ozgrid.com
_ If you wish to make a career out of posting questions and getting answers without having any real intentions of thinking about anything, then excelfox is not the forum for you to post in. Most of the smaller forums are not the place for you. The larger forums may be able to accommodate you, if you give at least some thought to making it not quite so obvious: Your distinguishing characteristic is that you have been making it much more obvious than others doing the same, do: Many people do the such. At least half the traffic at such forums originates from such. I have passed many people on to such forums and they are making a successful career based on passing on the work done for them by helpers at the major forums. Such is actually encouraged, all be it , not openly, at the major forums.
_ If you have not understood most of this Moderator Notice , then your first priority should be to improve on your English. Indeed, your apparent understanding and ability in communicating in English suggests that you will achieve nothing whatsoever and fail completely in anything at all involving communicating in English.

_ If you are, as you sometimes told me via PM, actively working on an important personal problem requiring VBA , then you are doing it totally wrongly: You have been on the project already for at least two years and have a mixed up set of codings produced by many different people. Some work . Some don’t. You have not the slightest idea or understanding of any of the codings. You will never be able to use them to any effect. You are getting an ever increasingly different set of codings with every post you make, and reply you get, which all just confuses the issues further. You are making negative progress, Bro! You are working and going backwards most of the time.
If , on the other hand, you had a computer, with Excel, and spent a few weeks learning VBA, and then carefully studied all the macros that you have been given, then you would be able to answer most of your further questions, and would have at least a chance of being able to use the codings effectively:-
1 Month learn VBA and 1 month getting answers, partly alone, partly with help from forums = Finished Success
2+ Years posting the same and similar questions and just taking the answers = Never Ending Fail
_ It is unlikely that the macros you have that work will ever be very efficient and will likely be slower than anyone else’s: They will certainly not be the best possible. Giving you better coding has proved to be impossible: It is not possible to pass on better codings because of the ridiculously inefficient way that you are organising whatever it is that you are doing: The person receiving and passing on the coding needs to understand the English language and to understand some basic coding and to understand how to use such better coding. We have tried this a few times, but it proved always completely impossible to do. One example of this is the issue of text files: Because you are mostly dealing with values, the use of text files is almost certainly beneficial and in some cases the only efficient way to proceed. You have completely missed the point on this: You have repeated much work to try to avoid using text files. The problem was, and will never be, the issue of text files themselves. The issue is your total inability or unwillingness to understand anything at all about them.

Another possibility is that you and/ or yous are simply severally mentally handicapped: If that is the case then, then I am , sincerely, sorry for you, but you have no hope of achieving anything with what you are doing at forums , apart from wasting a lot of other people’s time.

##The main purpose of the question section of excelfox is approximately the following:
_1. Promote and improve the understanding of Excel and Excel VBA.
_2. Help people who get stuck on a problem and/or help people who are unsure how to proceed in solving a problem using Excel and Excel VBA.

Your objectives??
I do not know what the true reason is behind your postings. I can’t believe anything you say is your purpose, since you have lied and contradicted yourself in the past. The only thing we know 100% for sure is that your posting types are not for any of the purposes for which the question section of excelfox is intended.
You have had the benefit of the doubt given to you now very many times. You have had lots of chances.
You may be able to continue at some of the major forums, where some people are happy to continue to spend time to answer similar questions from the same source.
I do not think you will get any more replies to the types of postings you have been making at excelfox or at any other of the smaller English speaking Forums. You are wasting your time making any such posts from now on.
**I am Banning you, not as any form of punishment, but purely as in the past , it has proven to be the only way to prevent you wasting yours and other peoples time with your postings.
I do wish you luck and success with what ever it is you are attempting to do. But you should not be doing it at excelfox.
If you are attempting the personal project that you have told me about via PM, then you are going about it in completely the wrong way.
If you are trying to make a career of posting other people’s questions and getting answers for them, then you should post mostly at the major forums and organise yourself better: At least have access to Excel on a computer and learn the basics of VBA. If you are trying to make a career of posting other people’s questions and getting answers for them, as many people do, then you have made the mistake of making it too obvious. Many of the senior helpers at the main forums prefer to think that they are helping people rather than doing their work for them. What they don’t know, does not hurt them.

I will leave all your posts in the main forum for a few weeks. Then I will move them all to the test forum. I will probably further merge them. Eventually I may delete them all.

Bro, whatever you are trying to do, its not working. Its never going to work. Its just wasting everybody’s time.
You need first to learn English
Then get a computer with Microsoft Office.
Then learn some basic Excel and Excel VBA
Then start again.
If you ever come to excelfox again you will have to prove to me that
_ You are a real person
_ You are genuinely attempting a personal project and need help
_ You have a computer with Microsoft Office / Excel installed and that you can, and do, use it, and that you understand basic VBA programming.

Ref
https://excelfox.com/forum/showthrea...h-Introduction