VB Import/Export CSV

  • Thread starter Thread starter Hobbo59
  • Start date Start date
H

Hobbo59

Hi
I am a novice VB scripter and have found various pieces of the jigsaw
puzzlei n this fantastic forum, but can't seem to piece it all together.

I would like to import a csv file with a variable name into a workbook. Then
modify the information and export a modified file of the same name from a
different sheet. A defined range of columns and a variable number of rows
would be exported. The workbook needs to retain its original name.

The filename consists of the following components:
Test-yyyymmdd-Batch.res.csv which may look like "ANA-20071201-1.res.csv".
Test and Batch are defined names.
 
Hi,
I had to read your post several times, but it's still not crystal
clear. Let's see if I understand what you want to do:

1. Open a CSV whose filename is like
"Test-yyyymmdd-Batch.res.csv",
where Test and Batch are pre-specified strings.
2. Make some changes to the data.
3. Save the file as a Workbook file with
the same name, but "xls" extension.
What do you mean by "from a different sheet"?

Cheers,
-Basilisk96
 
Apologies for the confusion. Sheet =Worksheet. To clarify, I have a .xls file
containing multiple worksheets including one worksheet for importing the .csv
and another sheet for exporting a modified version of the import worksheet in
csv format. ("Test-yyyymmdd-Batch.res.csv"). There are two independent
processes import and export a csv file which is the same name.
 
I'm curious - why is it necessary to save the "before" and "after"
versions of the CSV in a workbook, when you can programmatically open
the CSV, modify the data, and output it to a new CSV? Is it for
backup/tracking purposes, or do you need to broaden your VBA
horizon? ;-)
 
The full story is that I work in a pathology lab. A csv file of patient
demographic data is produced from a mainframe. This is imported into Excel.
Sometimes the data is incomplete and may need to reimported at a later time,
hence overwriting any existing datawhich is why I would like to keep in its
virgin state. The export file will be in the same format, however will have
pulled results from other sheets via lookups to unique patient identifiers.
Would prefer to keep people out of the csv import/export sheets as there is
potential to wreak havoc. The export file will then be imported back into the
mainframe. Most instruments are online, however the tests which we do are
read on a microscope and are manually interpreted. Excel is a fantastic tool
for rule based algorithms and accuracy and rather than keying these results
directly into a mainframe system, can can be 'dumped with 100% accuracy via
the above mechanism.
 
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
 
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)
 
Back
Top