Some HELP items to read more on this subject.
File format converters supplied with Microsoft Excel
Help -> Wizard --> csv:
When a CSV file is read in with the .csv extension it will be read in automatically by Excel. The same file with a .txt extension will be processed by the Text Import Wizard and you can manually process how the columns are handled. CSV files are are also referred to as flat files, ascii files, and spreadsheet files.
The fields in a .CSV file are separated by commas. If there is a comma within the field the field is enclosed in double quotes. Sometimes the field will be enclosed in double quotes when not necessary.
A .CSV file will be read in automatically by Excel which will determine what the format is.
To override this automatic determination you can rename or copy the file so that the extension is .TXT instead of .CSV so that the Text Import Wizard will be used.
The Text Import Wizard will process files having an extension of .TXT
Bypassing the Text Import Wizard for customized Formatting on in Conversion of .csv or .txt file containing phone numbers and zip codes to used as text not numbers can be accomplished using the OpenText method.
Sub KarenCSV() Workbooks.OpenText Filename:="C:/temp/csv.txt", Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _ :=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 2), Array(3, 1), Array(4, 2)) Cells.Select Cells.EntireColumn.AutoFit End SubThere are probably much better ways of implementing this and creating a workbook but the following shows that it works.
on UNIX and phone, but I will list a better search below.
HELP --> INDEX --> opentext
(see also texttocolumns method)
See Google Advanced Groups Search -- http://groups.google.com/advanced_group_search
Find messages with all of the words: opentext fieldinfo
More information on Google and newsgroups
One of the replies in the newsgroup archives suggested turning on the Macro Recorder which simplifies the process of creation and understanding of how to implement it.
An Alternate Solution -- Don't know how or if it works. If you first create your Excel file 'template' and designate the zip code field as text (not numbers) then you can paste your data and retain the zeros.
Name,Items, Price , Total ,Text.zip,Quote.zip Alice,5000 , 14.15 ," 70,750.00 ",00712,00712 Bunny,14 , 15.50 , 217.00 ,00708,00708 Carrie,100 , 14.80 ," 1,480.00 ",00712,00712
The following will create a CSV file for use in another application with your data (instrument readings?). During the run, if you have old file in your temp directory with current sheetname you will be asked if you want to replace it (Yes), at the end your file is brought up in Notepad for your review.
Sub Macro20() Dim filename As String, RC As Long filename = "c:\temp\" & _ Replace(Application.ActiveSheet.Name, " ", "") & ".csv" ActiveSheet.Copy 'creates a new workbook with one sheet Cells.Select 'select ALL cells Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Shift:=xlToLeft Range("A1").Select 'reduce to only once cell selected ActiveWorkbook.SaveAs filename:=filename, _ FileFormat:=xlCSV, CreateBackup:=False '-- activeworkbook is the newly copied worksheet's workbook (.CSV) ActiveWorkbook.Close savechanges:=False RC = Shell("Notepad " & filename, 1) 'for your review End SubThe central part of the macro is based on recording a macro
Workbooks("Book1").Sheets("Sheet1").Copy Before:=Workbooks("Book2").Sheets(1) ActiveSheet.Name = newNamewould create a new worksheet
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2007, F. David McRitchie, All Rights Reserved