Workbooks.OpenText doesn't take 'fieldinfo' parameter

Question

The following code demonstrates the problem which is stated in questions title.

Copy & paste it in a new Microsoft Excel 2003 workbook.

Sub mytest()
    mypath = Application.GetSaveAsFilename()

    Workbooks.OpenText Filename:=mypath, DataType:=xlDelimited, _ 
        TextQualifier:=xlTextQualifierDoubleQuote, _ 
        semicolon:=True, _ 
        fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2)), _ 
        Local:=True
End Sub

Execute the code. It will ask for an input file where you should use this semicolon-seperated test.csv. It creates a new workbook and imports all the data from test.csv to sheet1.

The picture below shows the result
enter image description here

But It should have shown a result like this
enter image description here

fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2))
states that Excel should treat all imported data as text. Unfortunately it doesn't.

The two-dimensional array: first number is the column number, second number stands for data type where 2 stands for text
I have to use the 'local'-parameter because I'm on a localized OS with a localized Office 2003
The operating system is irrelevant since it happens on Windows XP and on Windows 7

Can someone show me how to use opentext and fieldinfo in the correct way?

I already know the work-around with QueryTables.Add(Connection[...].
But thats not a solution for my case.

nixda · Accepted Answer · 2012-12-31T00:32:57.640

5

If you rename the extension from the input file from .csv to .txt the fieldinfo parameter works as designed.

edited Dec 31 '12 at 00:32

answered Mar 05 '12 at 19:54

nixda

2,654
12
49
82

2

Thanks much for this, I spent ages trying `workbooks.opentext` on a .csv file and got bad results until I renamed it to .txt. Is this documented somewhere? I can't see a reference to CSV in the relevant MSDN [library page](http://bit.ly/14uzq3z). And is there a workaround if I'm not able to rename the file from .csv? – idoimaging May 21 '13 at 20:40
1

@a.out Yes, have a look at this [answer](http://superuser.com/a/527894/50173). I recommend you use *QueryTables*. Have a look at the example code – nixda Nov 07 '16 at 10:46

Workbooks.OpenText doesn't take 'fieldinfo' parameter

1 Answers1