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 |
|
|
|
|
|
|
|
|
|
|
|
|
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.
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……
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
Reply With Quote
Bookmarks