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

[DocAElstein]

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:

' 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