Thx Tom!!! On Debug, I run into a macro On Error in the Pearson routine.
I am working with this CSV file
"http://www.nseindia.com/content/historical/DERIVATIVES/2004/SEP/fo14SEP2004bhav.csv"
The file has 4475 rows of market-quotes for Futures, Options & other
exchange traded derivative contracts. Would you have any suggestions, am
I running into a memory allocation problem?


TIA !!

Regards,
BR


"Tom Ogilvy" wrote:

'My macro starts here
Sub MAKEXLS()
Dim sname As String
Dim XLSBHAVCOPY As Workbook
sname = Dir("D:\INVESTMENTS\CSV\*.csv")
Do While sname < ""
' open the workbook
Set XLSBHAVCOPY = Workbooks.Add()
' open csv, write data to workbook
ImportTextFile sname, ";"
' save the a file as xls
With XLSBHAVCOPY
.SaveAs FILENAME:="d:\investments\xls\" & sname, _
FileFormat:=xlWorkbookNormal
.Close Savechanges:=True ' already saved
End With
sname = Dir()
Loop

End Sub
'my Macro ends above

'Chip Pearson code starts here
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub


--
Regards,
Tom Ogilvy


"BHARATH RAJAMANI" wrote in
message ...
Thx Tom !! I am able to parse Chip's code, but saving an empty file.
Would
you be able to tell me what is wrong in this macro:

'My macro starts here
Sub MAKEXLS()
Dim sname As String
Dim XLSBHAVCOPY As Workbook
sname = Dir("D:\INVESTMENTS\CSV\*.csv")
Do While sname < ""
ImportTextFile sname, ";"
' save the a file as xls
Set XLSBHAVCOPY = Workbooks.Add()
With XLSBHAVCOPY
.SaveAs FILENAME:="d:\investments\xls\" & sname
.SaveAs FileFormat:=xlWorkbookNormal
.Close
End With
sname = Dir()
Loop

End Sub
'my Macro ends above

'Chip Pearson code starts here
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'Chip Pearson macro ends above


TIA !!!
Regards,
BR



"Tom Ogilvy" wrote:

See Chip Pearson's site for code that will read in a CSV file - you can
specify the delimiter:

http://www.cpearson.com/excel/imptext.htm import/export text files

You can loop through a directory with

sName = dir("C:\Myfiles\*.csv")
do while sName < ""
' call chips routine to open the file
' save the a file as xls
sName = Dir()
Loop



--
Regards,
Tom Ogilvy

"BHARATH RAJAMANI" wrote in
message ...
Can someone guide me how to write a macro that Loops through 'N' .CSV
files
in a desktop folder and saves them as .XLS files. It is important
to
recognize the comma delimiter and throw the comma-separated individual
values
in different cells. If I use a normalExcel fileproperty then my xls
file
has
all values in column A only.


TIA

Regards,

--
Manager, International Private Banking, International Banking Group,
ICICI
Bank
East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai
India
400051