VB Import/Export CSV
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
|