Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro to Export Active Cells, Comma Sep to .txt file | Excel Programming | |||
button that opens open dialogs but saves path to sheet | Excel Programming | |||
Excel saves CSV using semicolon(;) instead of comma(,) | Excel Programming | |||
how to get a macro to run when the file opens | Excel Programming |