How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?

[feipezi]

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

 

[Andrzejek]

It would be nice to have some examples (as attachments) of your FACTS_DETAIL.TXT and FACTS_SUMMARY.TXT files so we know what we deal with...

BTW,
Any of the lines with [tt].Select[/tt] do not really do anything.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Hi,

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[combo]

Some time ago I imported text to newly added worksheet using query table. The code (also recorded, still with too many settings, but I had no time to brush up it; some settings are local):

Sub ImportFile(sFilePN As String)
Dim wksData As Worksheet
Set wksData = ThisWorkbook.Worksheets.Add
With wksData
    .Name = "data"
    With .QueryTables.Add(Connection:="TEXT;" & sFilePN, Destination:=.Range("$A$1"))
        .Name = "TemporaryName"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileDecimalSeparator = "."
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 4, 4, 4, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
        .Delete
    End With
End With
Set wksData = Nothing
End Sub

combo

 

[Andrzejek]

Looks like in combo's example he is importing a text file with 18 columns with [tt]TextFileColumnDataTypes[/tt] of either 1 or 4

According to this place:
[pre]
1 = xlGeneralFormat General
2 = xlTextFormat Text
3 = xlMDYFormat MDY date
4 = xlDMYFormat DMY date
9 = xlSkipColumn Skip column [/pre]

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

 

[SkipVought]

Not many people know that I have a column Data Type in Excel named after me?

There are other formats...
[pre]
Name Value Description
xlGeneralFormat 1 General.
xlTextFormat 2 Text.
xlMDYFormat 3 MDY date format.
xlDMYFormat 4 DMY date format.
xlYMDFormat 5 YMD date format.
xlMYDFormat 6 MYD date format.
xlDYMFormat 7 DYM date format.
xlYDMFormat 8 YDM date format.
xlSkipColumn 9 Column is not parsed.
xlEMDFormat 10 EMD date format.
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[feipezi]

Thanks folks for your reply.

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.

 

[SkipVought]

...but the TXT file that I used has no specific delimiters...

You MUST have EITHER a TEXT file with...
DELIMITERS or
FIX WIDTH FIELDS
...to Import or Open a file to parse into fields.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Andrzejek]

I cannot provide samples due to company policy

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.

text file has no decent delimiters (or just space/tab delimited)

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

 

[combo]

The procedure I posted is a (modified) macro recorded when I Imported a text file to the worksheet. When a text file is imported, a wizard appears, where the user can dclarne general data shape in the file, and formats for each column. If you plan to proceed this way, I recommend to record import of your data and adapt my code to your needs. This refer both to the description of data in the file, and the location and name of workbook and worksheet where the data is imported.

combo

 

[Andrzejek]

Any resolution to your issue...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

BTW, I often IMPORTED text data into production workbooks that were regularly updated.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!