Maybe...

Option Explicit
Sub testme01()

Dim CSVFileName As Variant
Dim TxtFileName As String

Dim TempWks As Worksheet

CSVFileName = Application.GetOpenFilename(Filefilter:="CSV Files, *.csv")
If CSVFileName = False Then
Exit Sub 'user hit cancel
End If

TxtFileName = CSVFileName & ".txt"

FileCopy Source:=CSVFileName, Destination:=TxtFileName

Workbooks.OpenText Filename:=TxtFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, _
FieldInfo:=Array(1, 2)

Set TempWks = ActiveSheet

'copy to a new workbook
'so we can close and kill the text file
TempWks.Copy
TempWks.Parent.Close savechanges:=False
Kill TxtFileName

End Sub

You'll want to record a macro when you open one of your text files (after you've
renamed the .csv to .txt) so that you can get that .opentext line
correct--especially the delimiters and the fieldinfo stuff.



Rich wrote:

Hi,

The team at work have to export a file from an app one column which contains
16 digits product codes. The app only exports as a CSV. My colleagues use
excel to open the csv, but the long number reverts to scientific notation,
and formatting the cell to number corrupts the number slightly.

The only work around I can offer if to rename the CSV as text, open with
excel then go through the wizard and set the offending column to text, which
works, but is over complicated and cumbersome for the team.

Can anyone offer an easy solution or a macro method to ease this process ?

--
Rich
http://www.richdavies.com
http://www.richdavies.com/excel.htm
http://www.richdavies.com/fantasy-football.htm

** Posted from http://www.teranews.com **

--

Dave Peterson