Excel macro to import text file

[peter2047]

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.

 

[Richard Schollar]

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]

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]

Peter

If you include the line:

Application.DisplayAlerts = False

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

Best regards

Richard[/code]

 

[peter2047]

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]

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

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]

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]

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