opening csv files in excel

[Peter100]

Hi

I regularly want to open .CSV files in excel but the problem is that if I was to just open it it will convert certain entries such as 1-7 to 01-Jan.

I know I can use the import data wizard and set the columns to text, but as I need to open maybe ten files several times a day this is very time consuming and open to error.

Is there a way in VBA of writing a routine to do it

 

[Richard Schollar]

Do the files all have the same format? ie col 1 is a date, col 2 is an ID number, col 3 is [whatever]? How many columns in total? Does this vary between the files?

 

[Peter100]

Hi
in theory they should be the same format but not guaranteed, likewise the number of columns could vary.

I really just want to open them as plain text. is there a way of of writing a routine that lets you select a file and then have it default to comma delimited and set all columns to text

 

[Richard Schollar]

Hi Peter

You can use the following - need to amend the CONST values to what is appropriate:

Sub OpenCSVAsText()
Const NEW_DRIVE As String = "C"
Const NEW_DIR As String = "C:\Test1\"
Dim currDir As String
Dim sFullName As String, sNewName As String
Dim myArray

myArray = Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), _
        Array(4, xlTextFormat), Array(5, xlTextFormat), Array(6, xlTextFormat), _
        Array(7, xlTextFormat), Array(8, xlTextFormat), Array(9, xlTextFormat), _
        Array(10, xlTextFormat), Array(11, xlTextFormat), Array(12, xlTextFormat))

currDir = CurDir

ChDrive NEW_DRIVE
ChDir NEW_DIR

sFullName = Application.GetOpenFilename("CSV Files (*.csv),*.csv")

If sFullName = "" Then MsgBox "No File Selected": Exit Sub

sNewName = Left$(sFullName, Len(sFullName) - 3) & "txt"

Name sFullName As sNewName

Workbooks.OpenText Filename:=sNewName, DataType:=xlDelimited, _
    comma:=True, fieldinfo:=myArray
    
ChDrive Left$(currDir, 1)
ChDir currDir
End Sub

Only covers the first 12 columns though (this can be extended by modifying the FieldInfo string). Doesn't matter if you have fewer columns than this.

 

[Peter100]

Richard

Thank you VERY much