Recorded macro opens, converts comma-del file and saves

[Ed]

I recorded a macro to open a text-type file (.rev extension) and then save
it as an xls workbook. It works well. But I have a question regarding the
conversion.

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

and follows with Array(x,1) up to 69,1. I understand this is one array for
each column. But as recorded, this on ly works correctly if I have 69 (or
less, I presume) columns, right? If one has more than 69 columns, am I
going to miss data? Is there a better way to code this to make sure I catch
every column?

Thanks.
Ed

 

[NickHK]

Ed,
The FieldInfo argument is optional, so you can omit it.
That means that you cannot specify the data type of the fields, but will
have to let Excel guess.
Not sure if this would be suitable.

Otherwise you can build the array yourself before passing it, but again you
would need to the data type of each field.

NickHK

 

[Ed]

Thanks for the reply, Nick. If I omit the FieldInfo argument, then Excel
will automatically assign a data type to each column? So I will be assured
of getting each column, even if it varies between files?
Ed

 

[NickHK]

Ed,
As I understand it.
Give it a test on various files with differing numbers of columns and see
what you get.

NickHK

 

[Ed]

Thanks for the boost, Nick.
Ed

Ed,
As I understand it.
Give it a test on various files with differing numbers of columns and see
what you get.

NickHK

 

[NickHK]

Ed,
Test on some files, because the way Excel "guesse" which data type each
column contains can be surprising.
By default IIRC it reads the first 16 rows. So if the first 16 are numbers
and the rest are text, you may have problems.

NickHK

 

[Dave Peterson]

I like to specify what I want--and not let excel guess:

Option Explicit
Sub testme01()
Dim fName As String
Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = Worksheets(1).Columns.Count

fName = "C:\sometextfile.txt"

ReDim myArray(1 To maxFields, 1 To 2)
For iCtr = 1 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1
Next iCtr

Workbooks.OpenText Filename:=fName, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray

End Sub