Csv To Xlsx: Import Export values from Comma delimeted text file to Excel worksheet

Code:

---

Sub CSVToXLS()
    Dim fPath  As String, fPathDONE As String, fCOUNT As Long
    Dim fName  As String, fType    As String
    Dim fAfter As String, NwName    As String
 
    fPath = ThisWorkbook.Path & "\Test\"
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
 
    fPathDONE = fPath & "\Converted\"
    MakeFolders fPathDONE
 
    fName = Dir(fPath & "*.CSV")
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    Do While Len(fName) > 0
        NwName = Left(fName, InStrRev(fName, ".") - 1)
        Workbooks.Open fPath & fName
        ActiveSheet.Name = NwName
        ActiveWorkbook.SaveAs fPath & NwName & ".xls", FileFormat:=xlNormal
        ActiveWorkbook.Close
     
        Name fPath & fName As fPathDONE & fName
     
        fCOUNT = fCOUNT + 1
        fName = Dir()
    Loop
 
    MsgBox "A Total Of " & fCOUNT & " Files Were Processed"
    Application.ScreenUpdating = True
End Sub

Function MakeFolders(MyStr As String)
    Dim MyArr  As Variant
    Dim pNum    As Long
    Dim pBuf    As String
 
    On Error Resume Next
 
    MyArr = Split(MyStr, "\")
    pBuf = MyArr(LBound(MyArr)) & "\"
    For pNum = LBound(MyArr) + 1 To UBound(MyArr)
        pBuf = pBuf & MyArr(pNum) & "\"
        MkDir pBuf
    Next pNum
End Function

---

I Have a Macro that works perfectly & I need a little changes in it, Changes are mentioned Below
1)this macro works only if the file is located in test folder & I want to define the path in the macro, My file alway's be located in C:\Users\WolfieeeStyle\Desktop\Alert..csv & this macro should convert only that file from .csv to .xls & it should delete the .csv file & keep the .xls file to the same location
And I don't want any msg & any notification stating anything
So request u to plz look into it

Your macro above uses the Workbooks.Open ____________.csv type way to open/ use a .CSV file which we said must be avoided since there will always be problems in it ( https://excelfox.com/forum/showthrea...ll=1#post13710
https://excelfox.com/forum/showthrea...ll=1#post13709 )
Your macro above uses the CSV as you have said you will never do. ( But based on passed experience with you , I expect you will use it and we will go around in circles again for ever and get no where...)
So best is to ignore that macro and any variations of it. That is very important since that has already wasted a lot of peoples time, including yours for the last two months...
So start again / try again to explain...

---

We are not really talking about converting Csv to Xlsx
Csv are usually text files
Xlsx are usually Excel files.
You cannot convert one to the other.

I think you want to do this question:
Question.
_ Import values from a comma separated values text file into an Excel Worksheet.
_ Save the Excel File with the imported values. This will be a new file. It will be an Excel file containing just the values from the text file. It should not contain the comma separators. Each value should be in a cell. The text file lines should correspond to the Excel file rows.
Give Excel file a similar name to that of the text file, ( For example: If text file name is Alert..csv, then the name of the Excel file should be Alert..xls

Answer
( Same question and answer as many of your forum posts. Here are just a few of many example: Code for text file to Excel : http://www.eileenslounge.com/viewtop...269104#p269104
http://www.eileenslounge.com/viewtopic.php?f=30&t=34638
https://chandoo.org/forum/threads/fe...2/#post-264364 )

Try again….
A comma separated values file will usually also need a separator for the lines. So far I have seen you use 2 different forms,
vbCr & vbLf
and
vbLf
I have also seen you supply so called ".csv" files , which take many forms, and can also have different forms values separators ( http://www.eileenslounge.com/viewtop...268716#p268716 )
The most common used values separators are commas , _ But others include | ; vbTab

Therefore, I think it will save us time to make a function which takes in the values separators and the line.

Note: With Text files we must concern ourselves with the Record/Line(row) separator and the Field(column) Separator : They may vary. ##We must know about these. ##
( In Excel we do not have to concern ourselves with the row separator used internally by Excel ( vbCr & vbLf ), or the column Separator used internally by Excel ( vbTab ) : Excel does this for us. We do not need to add these when working with Excel Files. Internally, Excel uses those separators to make the cells that we see and work with. )

Different File Types used for simple values
See here ( This post https://excelfox.com/forum/showthrea...ge30#post13349 ) for typical comparisons of text Files, Excel files, and data files
Text File: https://excelfox.com/forum/showthrea...ll=1#post13693
Excel File: https://excelfox.com/forum/showthrea...ll=1#post13694
Data File: https://excelfox.com/forum/showthrea...ll=1#post13695

---

Function to make an Excel files from a text file containing values and separators

XLFlNme is the Excel File name wanted for the new File
TxtFlNme is Text File name of an existing text file
valSep is the values separator used in the existing text file##
LineSep is the line separator used in thee existing text file##
Paf it the path to the files. ( I assume they are at the same place for the existing text file and the new Excel File )

The function is almost identical to the macro I did for you here: Code for Text File to Excel https://eileenslounge.com/viewtopic....269105#p269105
The function is here: https://excelfox.com/forum/showthrea...ll=1#post13717

It is a function.
So you will need to call it with a test macro such as this:

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
Sub Test_MakeXLFileusingvaluesInTextFile()
Dim Pf As String
Let Pf = ThisWorkbook.Path  '                ' CHANGE TO SUIT
'let pf = "C:\Users\WolfieeeStyle\Desktop"  ' CHANGE TO SUIT
 Call MakeXLFileusingvaluesInTextFile(Pf, "sample2BEFORE..csv", "Test.xlsx", ",", vbCr & vbLf)
End Sub


---

I tested it using this text file: Share 'sample2BEFORE..csv' : https://app.box.com/s/a3o4irgofydb71e3o0c4aaxefg6dw3bi
NSE,101010,6,<,12783,A,,,,,GTT
NSE,22,6,<,12783,A,,,,,GTT
NSE,17388,6,<,12783,A,,,,,GTT

Running the test macro results in an Excel File being made looking like this:

_____ Workbook: Test.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L
1	NSE	101010	6	<	12783	A					GTT
2	NSE	22	6	<	12783	A					GTT
3	NSE	17388	6	<	12783	A					GTT
4

Worksheet: Sheet1

---

I have not addressed the issue of deleting the text file. You can probably do that yourself. ( It is not a good idea to do that, since a back up of your data is probably a good idea to have. )

I am mostly away now until Sunday.
Alan

---

sample2BEFORE..csv : https://app.box.com/s/a3o4irgofydb71e3o0c4aaxefg6dw3bi
Test.xlsx : https://app.box.com/s/cty8ldv85f4kn61pk2u049yqv0szk0uc
Vba.xlsm : https://app.box.com/s/lf6otsrl42m6vxxvycjo04zidya6pd2m
Function MakeXLFileusingvaluesInTextFile() : https://excelfox.com/forum/showthrea...ll=1#post13717

Which macro i have to run to get the desired result?
As i can see u have given

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
Sub Test_MakeXLFileusingvaluesInTextFile()
Dim Pf As String
Let Pf = ThisWorkbook.Path  '                ' CHANGE TO SUIT
'let pf = "C:\Users\WolfieeeStyle\Desktop"  ' CHANGE TO SUIT
 Call MakeXLFileusingvaluesInTextFile(Pf, "sample2BEFORE..csv", "Test.xlsx", ",", vbCr & vbLf)
End Sub

---

And this

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
'XLFlNme is the Excel File name wanted for the new File
'TxtFlNme is Text File name of an existing text file
'valSep is the values separator used in the existing text file
'LineSep is the line separator used in thee existing text file
'Paf it the path to the files. ( I assume they are at the same place for the existing text file and the new Excel File )

Function MakeXLFileusingvaluesInTextFile(ByVal Paf As String, ByVal TxtFlNme As String, ByVal XLFlNme As String, ByVal valSep As String, ByVal LineSep As String)

Rem 2 Text file info
' 2a) get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = Paf & Application.PathSeparator & TxtFlNme  '                                                              CHANGE TO SUIT                                                                                                        From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629    DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' 2b) Split into wholes line _ splitting the text file into rows by splitting by Line Seperator
Dim arrRws() As String: Let arrRws() = Split(TotalFile, LineSep, -1, vbBinaryCompare)
Dim RwCnt As Long: Let RwCnt = UBound(arrRws()) + 1    '  +1 is nedeed as the  Split Function  returns indicies 0 1 2 3 4 5  etc...
' 2c) split first line to determine the Field(column) number
Dim arrClms() As String: Let arrClms() = Split(arrRws(0), valSep, -1, vbBinaryCompare)
Dim ClmCnt As Long: Let ClmCnt = UBound(arrClms()) + 1
' 2d) we can now make an array for all the rows, and  columns
Dim arrOut() As String: ReDim arrOut(1 To RwCnt, 1 To ClmCnt)

Rem 3 An array is built up by _....
Dim Cnt As Long
    For Cnt = 1 To RwCnt '              _.. considering each row of data
    'Dim arrClms() As String
    Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare)  '  ___.. splitting each row into columns by splitting by the comma
    Dim Clm As Long  '
        For Clm = 1 To UBound(arrClms()) + 1
        Let arrOut(Cnt, Clm) = arrClms(Clm - 1)
        Next Clm
    Next Cnt

Rem 4  Finally the array is pasted to a worksheet in a new file
 Workbooks.Add
 ActiveWorkbook.SaveAs Filename:=Paf & Application.PathSeparator & XLFlNme, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
 Workbooks("" & XLFlNme & "").Worksheets.Item(1).Range("A1").Resize(RwCnt, ClmCnt).Value = arrOut()

End Function

---

there are two things
can i get one macro that will do the process,I will run the macro and it will do the process

You have used functions before, both from me, and from other people, many, many times, ( like Function CL() )
You put both macros in the same place
You run just Sub Test_MakeXLFileusingvaluesInTextFile()
The function, Function MakeXLFileusingvaluesInTextFile(), will be used when needed by Sub Test_MakeXLFileusingvaluesInTextFile()

I explained in great detail why I am using a function.

I suggest you read again what I have written.

You run just Sub Test_MakeXLFileusingvaluesInTextFile() and it will do the process!

You have just one macro that will do the process, you run the macro , Sub Test_MakeXLFileusingvaluesInTextFile() , and it will do the process....

I wanted to inform u that in that file there are 25 macros & it will be run back to back
If I used this it will not cause any errors with other macros?

It should not cause any problems. I cannot think of any reasons why it should

Error details:
Run time error 76
Path not found

I have used this

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
Sub Test_MakeXLFileusingvaluesInTextFile()
Dim Pf As String
Let Pf = "C:\Users\WolfieeeStyle\Desktop\Alert..csv"              ' CHANGE TO SUIT
'let pf = "C:\Users\WolfieeeStyle\Desktop"  ' CHANGE TO SUIT
 Call MakeXLFileusingvaluesInTextFile(Pf, "sample2BEFORE..csv", "Test.xlsx", ",", vbCr & vbLf)
End Sub

---

and

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
'XLFlNme is the Excel File name wanted for the new File
'TxtFlNme is Text File name of an existing text file
'valSep is the values separator used in the existing text file
'LineSep is the line separator used in thee existing text file
'Paf it the path to the files. ( I assume they are at the same place for the existing text file and the new Excel File )

Function MakeXLFileusingvaluesInTextFile(ByVal Paf As String, ByVal TxtFlNme As String, ByVal XLFlNme As String, ByVal valSep As String, ByVal LineSep As String)

Rem 2 Text file info
' 2a) get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = Paf & Application.PathSeparator & TxtFlNme  '                                                              CHANGE TO SUIT                                                                                                        From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629    DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' 2b) Split into wholes line _ splitting the text file into rows by splitting by Line Seperator
Dim arrRws() As String: Let arrRws() = Split(TotalFile, LineSep, -1, vbBinaryCompare)
Dim RwCnt As Long: Let RwCnt = UBound(arrRws()) + 1    '  +1 is nedeed as the  Split Function  returns indicies 0 1 2 3 4 5  etc...
' 2c) split first line to determine the Field(column) number
Dim arrClms() As String: Let arrClms() = Split(arrRws(0), valSep, -1, vbBinaryCompare)
Dim ClmCnt As Long: Let ClmCnt = UBound(arrClms()) + 1
' 2d) we can now make an array for all the rows, and  columns
Dim arrOut() As String: ReDim arrOut(1 To RwCnt, 1 To ClmCnt)

Rem 3 An array is built up by _....
Dim Cnt As Long
    For Cnt = 1 To RwCnt '              _.. considering each row of data
    'Dim arrClms() As String
    Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare)  '  ___.. splitting each row into columns by splitting by the comma
    Dim Clm As Long  '
        For Clm = 1 To UBound(arrClms()) + 1
        Let arrOut(Cnt, Clm) = arrClms(Clm - 1)
        Next Clm
    Next Cnt

Rem 4  Finally the array is pasted to a worksheet in a new file
 Workbooks.Add
 ActiveWorkbook.SaveAs Filename:=Paf & Application.PathSeparator & XLFlNme, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
 Workbooks("" & XLFlNme & "").Worksheets.Item(1).Range("A1").Resize(RwCnt, ClmCnt).Value = arrOut()

End Function

---

I placed the macro in vba.xlsm
My csv file name is alert..csv

You are making very, very simle mistakes because you are not taking any time to read or understand anything.
Please take more time to read all carefully , otherwise you are wasting my time.

Look again at my macro ... path should be "C:\Users\WolfieeeStyle\Desktop"

You are going too quickly and missing everything.

path should be this

Code:

---

"C:\Users\WolfieeeStyle\Desktop"

---

I agree
But may i know where the csv file name is mentioned in the macro?
File name is not mentioned in the macro(Alert..csv, So how the macro will know which file it has to convert)

Quote:

---

Originally Posted by fixer

file name is not mention in the macro (Alert..csv)
How macro will know which file it has to convert?



..............
1SEC




......
path should be this

Code:

---

"C:\Users\WolfieeeStyle\Desktop"

---

I agree
But may i know where the csv file name is mentioned in the macro?
File name is not mentioned in the macro(Alert..csv, So how the macro will know which file it has to convert)

---

You see what I mean...

you are wasting my time becoz you go too quick and read nothing

I have no more time to waste today.
maybe tomorrow or Sunday

Alan

Avinash.
I needed a few hours to write the macro for you. If you had taken a few minutes to read what I had written, then you would have answered most of your questions/ replies yourself. I then would have had time to answer any other issues, and you would have your solution and many other future questions already answered. But you chose to waste my time and Fail
Your choice. Do what you want to do, and Fail every time…

Good luck with your future failures…
Alan

---

Avinash ( Thread OP ) choose to Fail every time…

Question… Avinash you can choose from 2 things, every time you get an answer…..
You can choose. Your choice Do what you want Bro.
_1) Success
or
_2 ) Fail

_1) For Success, do this:
Read answer reply written for you for 1 minute, maybe 2 minutes: See answer for these and possibly lots of further questions. Maybe ask a few follow up questions for clarification . Get answer. Read it. See answer for these and possibly lots of further questions.
= Success :)
or
_2) For Fail do this:
Look very quickly at reply written for you for 1 second , maybe 2 seconds only. Then: Post quick rubbish, panic, delete , post more rubbish , any rubbish, delete, lie, post rubbish quickly , maybe look at answer quickly only again for 1 second only. Panic, delete , post more rubbish , any rubbish, delete, maybe look at answer quickly only get stressed out. Post rubbish. Go to excelforum, excelfox, eileenslounge, vbaexpress, chandroo , expertsexchange and many many more use zyxw123 jhas56788 rider roger anjus shinshan sumanjjj Leonardo1234 Umpsbug Kinjals Tinamishra kinjal124 sumanjjj123 KAJAL1234 rider1234 r@1234 andy124 rider@1234 fixer vixer Leonard dumdumbum Ava453644 MoldyBread and many many more… duplicate cross post same question many times, lie, post rubbish lie, "Sorry Sir, wont happen again", duplicate cross post same question many times more again, "Sorry Sir, wont happen again" lie, post rubbish , lie, "Thx Sir Problem Solved, have a great day", maybe macro worked today. Maybe not. Later it does not work… So…
Go to excelforum, excelfox, eileenslounge, vbaexpress, chandroo , expertsexchange and many many more use zyxw123 jhas56788 rider roger anjus shinshan sumanjjj Leonardo1234 Umpsbug Kinjals Tinamishra kinjal124 sumanjjj123 KAJAL1234 rider1234 r@1234 andy124 rider@1234 fixer vixer Leonard dumdumbum Ava453644 MoldyBread and many many more… duplicate cross post same question many times, lie, post rubbish lie, "Sorry Sir, wont happen again", duplicate cross post same question many times more again, "Sorry Sir, wont happen again" lie, post rubbish , lie, "Thx Sir Problem Solved, have a great day", maybe macro worked today. Maybe not. Later it does not work… So…
Go to excelforum, excelfox, eileenslounge, vbaexpress, chandroo , expertsexchange and many many more use zyxw123 jhas56788 rider roger anjus shinshan sumanjjj Leonardo1234 Umpsbug Kinjals Tinamishra kinjal124 sumanjjj123 KAJAL1234 rider1234 r@1234 andy124 rider@1234 fixer vixer Leonard dumdumbum Ava453644 MoldyBread and many many more… duplicate cross post same question many times, lie, post rubbish lie, "Sorry Sir, wont happen again", duplicate cross post same question many times more again, "Sorry Sir, wont happen again" lie, post rubbish , lie, "Thx Sir Problem Solved, have a great day" ", maybe macro worked today. Maybe not. Later it does not work… So…
Go to excelforum, excelfox, eileenslounge, vbaexpress, chandroo , expertsexchange and many many more use zyxw123 jhas56788 rider roger anjus shinshan sumanjjj Leonardo1234 Umpsbug Kinjals Tinamishra kinjal124 sumanjjj123 KAJAL1234 rider1234 r@1234 andy124 rider@1234 fixer vixer Leonard dumdumbum Ava453644 MoldyBread and many many more… duplicate cross post same question many times, lie, post rubbish lie, "Sorry Sir, wont happen again", duplicate cross post same question many times more again, "Sorry Sir, wont happen again" lie, post rubbish , lie, "Thx Sir Problem Solved, have a great day", ………….
_..and so on = Fail

Avinash Answer is : _2) Fail


Second chance 1 week later…
You can choose, Avinash. Your choice Do what you want Bro. _1) Success or _2 ) Fail?

Avinash Answer is : _2) Fail

Third chance 10 days later:
You can choose, Avinash. Your choice Do what you want Bro. _1) Success or _2 ) Fail?

Avinash Answer is : _2) Fail

Forth chance 2 weeks later later:
You can choose, Avinash. Your choice Do what you want Bro. _1) Success or _2 ) Fail?

_....? what will it be, Avinash.
I think you will probably choose to fail for ever..

---

I answer questions further for those who wish to have Success….
_... see next post

As working example ( similar name for both files )
For full detailed explanation, see Post#2 : https://excelfox.com/forum/showthrea...ll=1#post13718
The function used in the macro allows you to choose any File name for either the Excel file or the text file. Since those file names have different extension, we can usem for example, the similar name for both.
So, as example:
_...”….. Give Excel file a similar name to that of the text file, ( For example: If text file name is Alert..csv, then the name of the Excel file should be Alert..xls ….”….
Excel file name ( in variable XLFlNme ) can be Alert..xlsx
Text file name ( in variable TxtFlNme ) can be Alert..csv

Code:

---

Sub Test_MakeXLFileusingvaluesInTextFileSimilarNamesAlertDot()
Dim Pf As String
Let Pf = ThisWorkbook.Path  '                ' CHANGE TO SUIT
'let pf = "C:\Users\WolfieeeStyle\Desktop"  ' CHANGE TO SUIT
 Call MakeXLFileusingvaluesInTextFile(Pf, "Alert..csv", "Alert.xlsx", ",", vbCr & vbLf)
End Sub

---

( This is the obvious change needed , which Avinash overlooked in his haste to get Fail )



Before( text file Alert..csv)
NSE,101010,6,<,12783,A,,,,,GTT
NSE,22,6,<,12783,A,,,,,GTT
NSE,17388,6,<,12783,A,,,,,GTT

After Excel File
_____ Workbook: Alert.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L
1	NSE	101010	6	<	12783	A					GTT
2	NSE	22	6	<	12783	A					GTT
3	NSE	17388	6	<	12783	A					GTT
4

Worksheet: Sheet1

---

vba.xlsm : https://app.box.com/s/lf6otsrl42m6vxxvycjo04zidya6pd2m




One procedure solutions, ( No function )
For most people, the standard comer , will be the text file value separator, and the text file line separator will be vbCr & vbLf
So the function solution is not required. ( This is also the solution for Avinash Fail : it probably may work today, but in the future probably will give problems leading to ….. …. Go to excelforum, excelfox, eileenslounge, vbaexpress, chandroo , expertsexchange and many many more use zyxw123 jhas56788 rider roger anjus shinshan sumanjjj Leonardo1234 Umpsbug Kinjals Tinamishra kinjal124 sumanjjj123 KAJAL1234 rider1234 r@1234 andy124 rider@1234 fixer vixer Leonard dumdumbum Ava453644 MoldyBread and many many more… duplicate cross post same question many times, lie, post rubbish lie, “Sorry Sir, wont happen again”, duplicate cross post same question many times more again, “Sorry Sir, wont happen again” lie, post rubbish , lie, “Thx Sir Problem Solved, have a great day”, maybe macro worked today. Maybe not. Later it does not work… So…
Go to excelforum, excelfox, eileenslounge, … and so on = Fail )
The following procedure, Sub SolutionForAvinashFail() , is exactly the same coding as the function, from Rem 2

Code:

---

Sub SolutionForAvinashFail() ' Macro will take values from text file, Alert..csv , and put them in an Excel File and save that Excel files as Alert..xlsx
Rem 1 Files info
Dim Paf As String, TxtFlNme As String, XLFlNme As String, LineSep As String, valSep As String
 Let Paf = ThisWorkbook.Path  '                ' CHANGE TO SUIT
'let pf = "C:\Users\WolfieeeStyle\Desktop"    ' CHANGE TO SUIT
 Let TxtFlNme = "Alert..csv" ' text file name
 Let XLFlNme = "Alert..xlsx" ' Excel file name
 Let LineSep = vbCr & vbLf  ' Typical text file line seperator
 Let valSep = ","            ' most common used seperator in  comma seperated values text files
Rem 2 Text file info
' 2a) get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = Paf & Application.PathSeparator & TxtFlNme  '                                                              CHANGE TO SUIT                                                                                                        From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629    DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' 2b) Split into wholes line _ splitting the text file into rows by splitting by Line Seperator
Dim arrRws() As String: Let arrRws() = Split(TotalFile, LineSep, -1, vbBinaryCompare)
Dim RwCnt As Long: Let RwCnt = UBound(arrRws()) + 1    '  +1 is nedeed as the  Split Function  returns indicies 0 1 2 3 4 5  etc...
' 2c) split first line to determine the Field(column) number
Dim arrClms() As String: Let arrClms() = Split(arrRws(0), valSep, -1, vbBinaryCompare)
Dim ClmCnt As Long: Let ClmCnt = UBound(arrClms()) + 1
' 2d) we can now make an array for all the rows, and we know our columns are A-J = 10 columns
Dim arrOut() As String: ReDim arrOut(1 To RwCnt, 1 To ClmCnt)

Rem 3 An array is built up by _....
Dim Cnt As Long
    For Cnt = 1 To RwCnt '              _.. considering each row of data
    'Dim arrClms() As String
    Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare)  '  ___.. splitting each row into columns by splitting by the comma
    Dim Clm As Long  '
        For Clm = 1 To UBound(arrClms()) + 1
        Let arrOut(Cnt, Clm) = arrClms(Clm - 1)
        Next Clm
    Next Cnt

Rem 4  Finally the array is pasted to a worksheet in a new file
 Workbooks.Add
 ActiveWorkbook.SaveAs Filename:=Paf & Application.PathSeparator & XLFlNme, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
 Workbooks("" & XLFlNme & "").Worksheets.Item(1).Range("A1").Resize(RwCnt, ClmCnt).Value = arrOut()

End Sub

---

( Results same as for Sub Test_MakeXLFileusingvaluesInTextFileSimilarNamesAl ertDot() )

---

Edit:-
Alternative single procedure ( Alternative Avinash Fail solution )
We can achieve a problem (Ava – Fail ) solution by having CSV files used.
See also as alternative solution, as example, as here: http://www.eileenslounge.com/viewtop...270130#p270130
This uses the .CSV files in Excel …. Workbooks.Open ____________.csv ….

Code:

---

fPath = "C:\Users\WolfieeeStyle\Desktop\"
    fName = "Alert..csv
'  ….
    Workbooks.Open fPath & fName

---

= Workbooks.Open ____________.csv ….
As noted here, https://excelfox.com/forum/showthrea...ll=1#post13709 , this is the dangerous solution that will some times work but later will likely have the .CSV file caused problem issues.

Quote:

---

Originally Posted by DocAElstein

.....
Excel VBA is good for controlling Excel Files
Excel VBA is good for controlling Text files

It is just sometimes bad to try to Open a text file into Excel .
To open an Excel file with Excel VBA is no problems usually.
To import data from a text file into Excel is also usually no problem.


This is mostly a problem…
To try
Workbooks.Open ____________.csv
or
Workbooks.Open ____________.txt
These two things are often big problem

---

This solution , http://www.eileenslounge.com/viewtop...270130#p270130 , is another possible Avinash Fail solution……

Quote:

---

Originally Posted by DocAElstein

.....
Look very quickly at reply written for you for 1 second , maybe 2 seconds only. Then: Post quick rubbish, panic, delete , post more rubbish , any rubbish, delete, lie, ...... Go to excelforum, excelfox, eileenslounge, vbaexpress, ........... use zyxw123 jhas56788 rider roger anjus shinshan sumanjjj ....., “Thx Sir Problem Solved, have a great day”, maybe macro worked today. Maybe not. Later it does not work… So…
Go to excelforum, excelfox, eileenslounge, vbaexpress, chandroo , expe...... ”, maybe macro worked today. Maybe not. Later it does not work… So…
Go to excelforum, excelfox, eileenslounge, vbaexpress,......., ………….
_..and so on
_.. and so on .... = Fail

......

---

https://excelfox.com/forum/showthrea...ll=1#post13709
https://excelfox.com/forum/showthrea...ll=1#post13710
http://www.eileenslounge.com/viewtop...270130#p270130

Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem Sir

---

“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthrea...ll=1#post14972
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.

---

---

“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