Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Import/Export CSV
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Import/Export CSV
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 On Nov 30, 4:19 pm, Hobbo59 wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Import/Export CSV
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. "Basilisk96" wrote: 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 On Nov 30, 4:19 pm, Hobbo59 wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Import/Export CSV
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? ;-) Hobbo59 wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Import/Export CSV
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. "Basilisk96" wrote: 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? ;-) Hobbo59 wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Import/Export CSV
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import & Export | Excel Discussion (Misc queries) | |||
Import/Export | Excel Discussion (Misc queries) | |||
pdf..import/export | Excel Programming | |||
import and export vba | Excel Programming | |||
Import and Export | Excel Programming |