Reading Txt file from Excel

[pradipto]

Hi, I have a txt file with rows as follows:

row 1-> "Long-NSX,Short-ACC,0.40238"
row 2-> "Long-NSX,Short-BHE,0.4096"

I want to read it in an excel workbook using the Text Import Wizard by using the Delimited Option, and using the Tab, Semicolon and the Other option with the character as "-" so that my Excel sheet reads:

Col 1 Col 2 Col 3 Col 4 Col 5
Long NSX Short ACC 0.40238
Long NSX Short BHE 0.4096

etc ...

can some guide me how to programatically use the Text Import Wizard in VBA to achieve the above. I am a beginning programmer in VBA.

Also I would be very grateful if someone can tell me the commands to (a) set the column width in Excel thru VBA (b) how to delete a column thru VBA and (c) how to set the column width to minimum possible column width such that the elements are still visible (i.e. equivalent to double-clicking in the partition between two columns)

Thanks a lot.

 

[SkipVought]

Hi pradipto,
Excel has a great feature -- Macro record -- Tools/Macro/Record New Macro.

Turn on the recorder and go thru the process of opening and parsing your text file.

Turn off the recorder and go to the VB Editor (alt+F11) and observe th results. You will find many of the objects, properties and methods in this way. You will also discover ways to "clean up" the recorded code so it functions more effieiently and is easier to maintain.

Use Help in the VBE also.

On your specific questions...

(a) set the column width in Excel thru VBA
right out of HELP
ColumnWidth Property
See Also Applies To Example Specifics
Returns or sets the width of all columns in the specified range. Read/write Variant.

Remarks
One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

Use the Width property to return the width of a column in points.

If all columns in the range have the same width, the ColumnWidth property returns the width. If columns in the range have different widths, this property returns Null.

Example
This example doubles the width of column A on Sheet1.

With Worksheets("Sheet1&quot.Columns("A&quot
.ColumnWidth = .ColumnWidth * 2
End With
(b) how to delete a column thru VBA and
Worksheets("Sheet1&quot.Columns("A&quot.Delete shift:=xlLeft
(c) how to set the column width to minimum possible column width such that the elements are still visible (i.e. equivalent to double-clicking in the partition between two columns)
Worksheets("Sheet1&quot.Columns("A&quot.AutoFit



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

For the main part of this, just record yourself importing the file and setting the delimeters. This should then work for any imports of the same type

a)Columns("D&quot.ColumnWidth = 12.86
b)Columns("D&quot.Delete Shift:=xlToLeft
c)Columns("D&quot.EntireColumn.AutoFit

You can get ALL of this thru the macro recorder. This is THE most useful tool for learning VBA. Just record yourself doing stuff and see what it spits out. Generally, the code that is recorded has more than is strictly necessary but as your learning progresses, you'll be able to see what needs to be left in and what can be taken out. BTW I STILL use this tool - it is invaluable as I'm sure no-one can remember the exact syntax fort manipulating all of excel's object model Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde