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
|