The code works well. Will make some mods as one always does.

Thankyou again.

"Hobbo59" wrote:

That's ballistic Basilisk. Give me some time to play and I'll let you know
how I go.

Thankyou so much.

Regards

Hobbo59 (feral programmer)

"Basilisk96" wrote:

Okay, this will get you started. Feel free to experiment with the
variable names, sheet names, etc.

Make a fresh workbook.
Name one of the sheets Original.
Name another sheet New.
Go to the VBA Editor.
Insert a Module.
Then paste this code into it:

Sub ImportCsv(ByVal sh As Worksheet, fName)
Dim wb As Workbook
Set wb = Workbooks.Open( _
fileName:=fName, Format:=2)
wb.Sheets(1).UsedRange.Copy sh.Cells(1, 1)
wb.Close SaveChanges:=False
Set wb = Nothing
Set rng = Nothing
End Sub

Sub ProcessData(ByVal inSh As Worksheet, _
ByVal outSh As Worksheet)
'do your processing here
'for now, just transfer the data as-is
'from the input to the output sheet
inSh.UsedRange.Copy outSh.Cells(1, 1)
End Sub

Sub ExportCsv(ByVal sh As Worksheet, fName)
'
Dim wb As Workbook
Set wb = Workbooks.Add
sh.UsedRange.Copy wb.Sheets(1).Cells(1, 1)
wb.SaveAs fileName:=fName
wb.Close
Set wb = Nothing
End Sub

Function NameGen(test, batch, ddate)
NameGen = test & "-" & ddate & _
"-" & batch & ".res"
End Function

Sub FullMonty()
'performs import, processing and export calls
'input is like "Test-yyyymmdd-Batch.res.csv"
'such as: "ANA-20071201-1.res.csv".
'Test and Batch are defined names.
csvPath = ThisWorkbook.path

test = "ANA"
batch = 1
ymd = "20071201"

dataName = NameGen(test, batch, ymd)
csvName = csvPath & Application.PathSeparator _
& dataName & ".csv"
xlsName = csvPath & Application.PathSeparator _
& dataName & ".xls"
Set inSheet = ThisWorkbook.Sheets("Original")
Set outSheet = ThisWorkbook.Sheets("New")

ImportCsv inSheet, csvName
ProcessData inSheet, outSheet
ExportCsv outSheet, xlsName
End Sub


Save the workbook somewhere.
Copy your CSV file to the same location as the workbook (notice I used
ThisWorkbook.Path to locate the CSV files).
Name the CSV file "ANA-20071201-1.res.csv"
Run the sub named FullMonty.

In the end, you will fill the Original and New sheets with the CSV
data, and will create a "ANA-20071201-1.res.XLS" file in the same
directory with that data.

There is room for customization here. the FullMonty sub can be
modified to accept the test name, batch, and date as arguments for
convenience. For now, I hardcoded those parts as local variables. You
may want to use a file dialog to select the input file dynamically,
and InputBox calls for the test and batch, or whatever.

I leave the data processing code up to you ;-)

Cheers,
-Basilisk96