Excel macro to import text file

peter2047

Board Regular
Joined
Dec 31, 2005
Messages
70
Hi All,
I am a newbee here. Greatly appreciated if some expert here can guide me to code a Excel macro to import a text file. The excel and text files are in the same folder.

Thanks in advance.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Peter

The best way to begin would be to record a macro of you importing the text file to begin with, and then examine the resultant code. Use the macro recorder: Tools>Macro>Record New Macro and go from there.

Best regards

Richard
 

peter2047

Board Regular
Joined
Dec 31, 2005
Messages
70
Hi Parsnip,
Thanks. Manage to do it... Here is the macro.

Sub Macro4()
'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Stock\5EE.SI.TXT", Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
1), Array(8, 1), Array(13, 1), Array(18, 1), Array(23, 1), Array(28, 1))
Cells.Select
Selection.Copy
Windows("Beta.xls").Activate
Cells.Select
ActiveSheet.Paste
Windows("5EE.SI.TXT").Activate
ActiveWindow.Close
End Sub

But I have a problem with the prompt from Excel asking me whether keep the large clipboard or not. Can I stop or disable that prompt ?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Peter

If you include the line:

Code:
Application.DisplayAlerts = False

within your sub, that should stop you from seeing the clipboard warning.

Best regards

Richard[/code]
 

peter2047

Board Regular
Joined
Dec 31, 2005
Messages
70

ADVERTISEMENT

Parsnip,
Thank you very much. It works.

One more question and I ready to start on my data compilation project. Is there a way to automatically locate the folder so I can easily move my project folder.

Is there a way to replace this statement ?
"C:\Documents and Settings\Administrator\Desktop\Stock\fileaname"

I am looking for an Excel macro command to detemine the current location.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Peter

If you include the following code (this assumes that the text file name the same each time, and that the text file is held in the same folder as the excel workbook that contains the macro):


Code:
Sub Macro4() 
Dim strPath as String

strPath = ThisWorkBook.Path


Workbooks.OpenText Filename:= strPath & "/5EE.SI.TXT", Origin:= _ 
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _ 
1), Array(8, 1), Array(13, 1), Array(18, 1), Array(23, 1), Array(28, 1)) 
Cells.Select 
Selection.Copy 
Windows("Beta.xls").Activate 
Cells.Select 
ActiveSheet.Paste 
Windows("5EE.SI.TXT").Activate 
ActiveWindow.Close 
End Sub

It should work. Please let me know if you run into any problems.

Best regards

Richard
 

peter2047

Board Regular
Joined
Dec 31, 2005
Messages
70
Hi Richard,
Your code works perfectly. But need to change the forward slash to a back slash("\5EE.SI.TXT").

Thank you very much. It's almost mid-night here and approaching the new year.

I would like to wish you and your love ones a Happy New Year.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Peter

You are welcome:)

Happy New Year to you and your family too!

Richard

PS: Would you believe I actually checked my path names to find out which way round the slash should be, and then went and wrote down completely the wrong one? It must be my age :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,796
Messages
5,574,359
Members
412,588
Latest member
FabrizioMaurizio
Top