Macro convert CSV -to- XLS without parsing line by line

[Guest]

Is there anyway I can import a "," delimited CSV file as a .XLS Workbook
without parsing the CSV line-by-line?

My CSV is a derivatives trading-data dump & has 4775 records per day with 14
columns of text, numeric & currency fields. I need to run this macro on 60
such CSV files everyday for 60day Moving Average securities analysis.


TIA !!

Regards,
BR

 

[Tom Ogilvy]

You macro needs a little more work. This worked fine for me:

'My macro starts here
Sub MAKEXLS()
Dim sname As String
Dim sPath As String
Dim XLSBHAVCOPY As Workbook
sPath = "D:\INVESTMENTS\CSV\"
sPath1 = "D:\INVESTMENTS\xls\"
If Right(sPath, 1) <> "\" Then
sPath = sPath & "\"
End If
If Right(sPath1, 1) <> "\" Then
sPath = sPath1 & "\"
End If
sname = Dir(sPath & "*.csv")
Do While sname <> ""
' open the workbook
Set XLSBHAVCOPY = Workbooks.Add()
' open csv, write data to workbook
ImportTextFile sPath & sname, ","
' save the a file as xls
sname = Left(sname, Len(sname) - 4)
With XLSBHAVCOPY
.SaveAs Filename:=sPath1 & 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

 

[Guest]

thx so much Tom!! This almost solves the problem, a little more tweaking is
required.

I copied this code to a file called MacroProcessor.xls
The code copies the contents of the csv to Sheet1 in MacroProcessor.xls
However, the resultant Destination File that is created from the CSV file
name (for e.g. fo10Aug2004bhav.xls from fo10Aug2004bhav.csv) is empty.
Would you be able to advise me?

TIA!!

Regards,
BR

 

[sidibou - ExcelForums.com]

there is a very simple solution
But may be you know it and for some reason don't want it

replace the delimiter symbol "," with ";"
Everything will be then fine without any macro

Your
Sidibo

 

[Tom Ogilvy]

in the code

Set XLSBHAVCOPY = Workbooks.Add()
' open csv, write data to workbook
ImportTextFile sPath & sname, ","

so the new workbook XLSBHAVCOPY should be the activeworkbook and Chip's
code writes to the activeworkbook/activesheet.

I don't see how this wouldn't work unless you have some kind of event code
that makes MacroProcessor.xls the activeworkbook after you open the new
workbook.

Obviously I can't see what happens. Can you see anything that makes the new
workbook not the activeworkbook before you call ImportTextFile?

 

[Guest]

Tom, There are two problems here:
1. The destination file is still saving as a CSV file-format.
2. The code below:
Cells(RowNdx, ColNdx).Value = TempVal
in Pearson's routing writes to the file wherein the macro resides
(macroprocessor.xls)

My debug window shows "Book1" as the ActiveWorkbook.Name, however
Cells(..,..) is writing elsewhere! Any thoughts?

TIA!!

Regards,
BR