Import TXT file and convert

HankD

New Member
Joined
Sep 25, 2006
Messages
7
Hi,
I am new to this forum.

I need to import txt files to excel and format them
with three different column formats.
I would like to have a scheme so these formatting
macros can be saved and used again.
Is there a plug-in or a program that can help me?
The text importer can't save the formatted schemes?
I am not used to programming or macros or the like.
A downloadable simple program maybe...?

Best
---Hank
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

chicagodiceman

Active Member
Joined
Mar 13, 2004
Messages
371
Welcome to the board,

Have you tried the macro recorder? This is a very good first step to create the code you will need. If you start the recorder before opening the text file in Excel, it will record all steps including the initial open and format. Then, with the recorder still active format the columns to the desired format.

If you don't feel comfortable with the programming, once you've recorded your macro if there are problems you could probably post your code and ask questions from there.

One thing, the recorder records EVERYTHING so you may want to write out a list of steps that you need to be performed so you don't have a lot of scrolling around recorded. Etc.
 

HankD

New Member
Joined
Sep 25, 2006
Messages
7
Thanks!
I think I understand, this is prob what I need to do. A macro will
record all the formatting - but how can I construct a macro that
will start by importing a file? Does that file has to be named
the same every time I start this procedure. Or maybe there is
a way to change that info in the macro, say like the next month
when I run it with a new file...? I noted that a macro recording
can't be started when I have already opened up Excel's Text
Importer.
---Hank
 

chicagodiceman

Active Member
Joined
Mar 13, 2004
Messages
371
Edit: You will need a blank workbook open to start the recorder. Then begin the open process and the macro will actually be stored on the blank workbook. This can later be used as a template so you can save the other file with different names.

You can modify the code after the recording is done to work on different file names. The recording process will help you as you say your not familiar with the macros in general. Once you've completed for the first text file, either search this site for examples of dynamic text file open procedures or post your code for help on modifying.

I do this frequently with files from our billing system and the name does change. So it is possible.
 

HankD

New Member
Joined
Sep 25, 2006
Messages
7

ADVERTISEMENT

This was very helpful and I managed to record a macro that handled the
formatting in a very good way. I even opened up the macro for editing
and I see that I can change the file name, to work with the next period's
file.
Maybe this is not the way you do it, if you do this frequently?
---Hank
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Hank - Hello & Welcome :)

If you saved the import file to the same filename each time you could automatically import this into excel & format as you require in one step

If you already have your format macro written or recorded, you could adapt this, then past your macro code after & hey presto!

NB - replace the network location to that where your file is

Code:
Application.ScreenUpdating = False
ChDir "Z:\Management\Customer Relations\Complaint Log"
    Workbooks.Open Filename:= _
        "Z:\Management\Customer Relations\Complaint Log\Complaint Log 2006.xls"
'your macro text here - delete this line
Application.ScreenUpdating = True
 

HankD

New Member
Joined
Sep 25, 2006
Messages
7

ADVERTISEMENT

I wonder if I can paste the code like this...?
Sub Makro4()
'
' Makro4 Makro
' Makrot inspelat 2006-09-25 av Henry Denander
'

'
Application.ScreenUpdating = False
ChDir "C:\Documents and Settings\Henry Denander\Mina dokument\AA UPGRADE\STIM avräkningar\JAN JUNI 2006\ncb juni 2006"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Henry Denander\Mina dokument\AA UPGRADE\STIM avräkningar\JAN JUNI 2006\ncb juni 2006.xls"
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(1, 1), Array(5, 1), Array(14, 1), Array(32, 1), Array(62, 1), _
Array(92, 1), Array(93, 1), Array(95, 1), Array(115, 1), Array(125, 1), Array(126, 1), _
Array(129, 1), Array(132, 1), Array(142, 1), Array(152, 1), Array(161, 1), Array(163, 1), _
Array(165, 1), Array(175, 1), Array(185, 1), Array(193, 1), Array(202, 1), Array(232, 1), _
Array(262, 1), Array(292, 1), Array(295, 1), Array(304, 1), Array(334, 1), Array(337, 1), _
Array(346, 1), Array(376, 1), Array(379, 1), Array(388, 1), Array(393, 1), Array(397, 1), _
Array(427, 1), Array(428, 1), Array(429, 1), Array(433, 1), Array(443, 1), Array(473, 1), _
Array(482, 1), Array(512, 1), Array(521, 1), Array(530, 1), Array(532, 1), Array(562, 1), _
Array(571, 1), Array(601, 1), Array(610, 1), Array(640, 1), Array(645, 1), Array(655, 1), _
Array(658, 1), Array(663, 1), Array(673, 1), Array(677, 1), Array(692, 1), Array(707, 1), _
Array(716, 1), Array(717, 1), Array(720, 1)), TrailingMinusNumbers:=True Application.ScreenUpdating = True
End Sub


Something is wrong in my macro....I tried to join your lines
---Hank
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
It should work, Open your macro in the viewer and press F8 (continually) to identify the error

I'll watch this thread

Mark
 

HankD

New Member
Joined
Sep 25, 2006
Messages
7
Hi,
It gives me some syntax problem
error (translated from Swedish...) for this area:

Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(1, 1), Array(5, 1), Array(14, 1), Array(32, 1), Array(62, 1), _
Array(92, 1), Array(93, 1), Array(95, 1), Array(115, 1), Array(125, 1), Array(126, 1), _
Array(129, 1), Array(132, 1), Array(142, 1), Array(152, 1), Array(161, 1), Array(163, 1), _
Array(165, 1), Array(175, 1), Array(185, 1), Array(193, 1), Array(202, 1), Array(232, 1), _
Array(262, 1), Array(292, 1), Array(295, 1), Array(304, 1), Array(334, 1), Array(337, 1), _
Array(346, 1), Array(376, 1), Array(379, 1), Array(388, 1), Array(393, 1), Array(397, 1), _
Array(427, 1), Array(428, 1), Array(429, 1), Array(433, 1), Array(443, 1), Array(473, 1), _
Array(482, 1), Array(512, 1), Array(521, 1), Array(530, 1), Array(532, 1), Array(562, 1), _
Array(571, 1), Array(601, 1), Array(610, 1), Array(640, 1), Array(645, 1), Array(655, 1), _
Array(658, 1), Array(663, 1), Array(673, 1), Array(677, 1), Array(692, 1), Array(707, 1), _
Array(716, 1), Array(717, 1), Array(720, 1)), TrailingMinusNumbers:=True Application.ScreenUpdating = True

What can be wrong?
---Hank
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Hi,
It gives me some syntax problem
error (translated from Swedish...) for this area:

Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(1, 1), Array(5, 1), Array(14, 1), Array(32, 1), Array(62, 1), _
Array(92, 1), Array(93, 1), Array(95, 1), Array(115, 1), Array(125, 1), Array(126, 1), _
Array(129, 1), Array(132, 1), Array(142, 1), Array(152, 1), Array(161, 1), Array(163, 1), _
Array(165, 1), Array(175, 1), Array(185, 1), Array(193, 1), Array(202, 1), Array(232, 1), _
Array(262, 1), Array(292, 1), Array(295, 1), Array(304, 1), Array(334, 1), Array(337, 1), _
Array(346, 1), Array(376, 1), Array(379, 1), Array(388, 1), Array(393, 1), Array(397, 1), _
Array(427, 1), Array(428, 1), Array(429, 1), Array(433, 1), Array(443, 1), Array(473, 1), _
Array(482, 1), Array(512, 1), Array(521, 1), Array(530, 1), Array(532, 1), Array(562, 1), _
Array(571, 1), Array(601, 1), Array(610, 1), Array(640, 1), Array(645, 1), Array(655, 1), _
Array(658, 1), Array(663, 1), Array(673, 1), Array(677, 1), Array(692, 1), Array(707, 1), _
Array(716, 1), Array(717, 1), Array(720, 1)), TrailingMinusNumbers:=True Application.ScreenUpdating = True

What can be wrong?
---Hank

I honestly dont know - Did your code work before adding mine - does mine give any error?
 

Forum statistics

Threads
1,136,652
Messages
5,677,007
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top