Recorded macro opens, converts comma-del file and saves
 

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

Recorded macro opens, converts comma-del file and saves
 

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" bl...
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

Recorded macro opens, converts comma-del file and saves
 

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" wrote in message
...
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"
bl...
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

Recorded macro opens, converts comma-del file and saves
 

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

NickHK

"Ed" bl...
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" wrote in message
...
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" bl...
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

Recorded macro opens, converts comma-del file and saves
 

Thanks for the boost, Nick.
Ed

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

NickHK

"Ed"
bl...
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" wrote in message
...
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"
bl...
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

Recorded macro opens, converts comma-del file and saves
 

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

"Ed" .gbl...
Thanks for the boost, Nick.
Ed

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

NickHK

"Ed" bl...
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" wrote in message
...
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" bl...
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

Recorded macro opens, converts comma-del file and saves
 

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

Ed wrote:

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

--

Dave Peterson