How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
(OP)
Hi,
I recorded a macro that can convert a text file into an Excel workbook. But the code looks messy with quite some redundant statements. I tried to streamline it but with no success. Like the Arrays, do we need that many arrays?
Besides, what if I have more than 30 fields? That being the case, is the macro going to work?
Thanks in advance.
Sub TEXT_TO_EXCEL(fname As String)
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\All Users\Documents\" & fname & Chr(46) & "TXT" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=0, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Documents\Zephyr\PASSPORT PC TO HOST\" & fname & Chr(46) & "xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub GoGetIt()
TEXT_TO_EXCEL "FACTS_DETAIL"
TEXT_TO_EXCEL "FACTS_SUMMARY"
End Sub
I recorded a macro that can convert a text file into an Excel workbook. But the code looks messy with quite some redundant statements. I tried to streamline it but with no success. Like the Arrays, do we need that many arrays?
Besides, what if I have more than 30 fields? That being the case, is the macro going to work?
Thanks in advance.
Sub TEXT_TO_EXCEL(fname As String)
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\All Users\Documents\" & fname & Chr(46) & "TXT" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=0, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Documents\Zephyr\PASSPORT PC TO HOST\" & fname & Chr(46) & "xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub GoGetIt()
TEXT_TO_EXCEL "FACTS_DETAIL"
TEXT_TO_EXCEL "FACTS_SUMMARY"
End Sub
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
BTW,
Any of the lines with .Select do not really do anything.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Like the Arrays, do we need that many arrays?
Yes, it defines how many fields are being defined and what their data type is.
Besides, what if I have more than 30 fields?
When importing or opening a file where the type of data is unknown in each column, each column must be defined.
That being the case, is the macro going to work?
No!
I am glad that you see the need to use this method. Whenever I received a text file, I IMPORTED the data similarly, and defined each column in the Excel Import interface to assure a proper import. Many times I recorded the IMPORT and maintained in VBA. It's the price you pay to maintain data integrity when new data is introduced.
BTW, you can see the repetitive nature of the above mentioned arrays, so adding/deleting columns would be trivial. HOWEVER, the various data types are included in this array and choosing the correct data type in the correct column needs to be correct, unless you carte blanch assign General to all columns.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
CODE -->
combo
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
According to this place:
More about XlColumnDataType enumeration
You can easily determine how many columns you have in any imported text file (if you know the delimiter), and even with a little code - detect the data type.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
There are other formats...
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Andy, sorry that I cannot provide samples due to company policy, which is getting stricter and stricter. We all know nobody can take a few lines of data to perform fraud...
combo, I tested your code. It should be working but the TXT file that I used has no specific delimiters, like pipe or comma. In your code, there is a statement: .TextFileParseType = xlDelimited. Is there a way of setting up the delimiters?
Just curious, what does this mean: .TextFilePlatform = 437?
I am going to test combo's code and my recorded macro on a file with 150 columns and see what happens.
A pretty big challenge to me is that a text file has no decent delimiters (or just space/tab delimited); one field is fully populated with values; but the field next to it has only a few rows that have values. In Excel, it's rather difficult to do Text to Column manually, let alone handling with macros. Any suggestions are welcome!
Thanks again.
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
You MUST have EITHER a TEXT file with...
DELIMITERS or
FIX WIDTH FIELDS
...to Import or Open a file to parse into fields.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
I understand, but you can make up data in the example just to present what you are dealing with. In my company - when I need some bogus data - we have Buggs Bunny, Duffy Duck, and Elvis Presley as employees.
Space and Tab are pretty good delimiters
Or another crazy idea - this TXT file(s) of yours, somebody provides this data to you. Ask them to give it to you in Excel format.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
combo
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
I would ALWAYS record the initial import (which embedded a QueryTable) and then modified the code to be able to run the query, updating the data at runtime. So I always had the requisite SQL and VBA in a procedure that I might, from time to time, need to modify if necessary, and I certainly did.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!