×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread707-1824263

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

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

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 .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?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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?

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):

CODE --> 

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

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

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

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

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?

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

There are other formats...

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.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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?

(OP)
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.

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

Quote:

...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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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?

Quote (feipezi)

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. smile

Quote (feipezi)

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

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

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

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

Any resolution to your issue... ponder

---- 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?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close