How do I read text file in an EXCEL SHEET

[AMIT]

Hello,

With regards to EXCEL how we read the text file in EXCEL sheet using
MACRO.
The file is located in d:\Sql\Documents\Amit\log.txt.
I want to save the EXCEL sheet, and every time when I open this sheet
it reads this text file and displays the contents of this text file in
Excel Sheet.
I want to use MACRO for reading this text file.How I do that?

Thanks

Amit

 

[Alexander]

fileName = "D:\Sql\Documents\Amit\log.txt"

Workbooks.OpenText FileName:=fileName

OR

Workbooks.OpenText FileName:=fileName, Origin:=xlWindows, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 2), Array(10, 2). . .)


Alex

 

[Guest]

Dear Ami

Pls. enable the macro record option from tools menu after opening excel and use the manual way of opening the text file
by listing only text files and selecting it..stop/close recording the macro after assigning a shortcut(keys) and thats it.

with regards
bij

Eg
Sub Macro1(

' Macro1 Macr
' Macro recorded 12/30/2003 by bij

' Keyboard Shortcut: Ctrl+

ChDir "d:\Sql\Documents\Amit\
Workbooks.OpenText Filename:="d:\Sql\Documents\Amit\log.txt", Origin:=xlWindows, StartRow
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False
, Space:=False, Other:=False, 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)
End Su

 

[AMIT]

Hello,

Thanks for your information.
But I still need some more info about the problem.
Actually I want that every time when I run the macro, it refreshes the
information from the text file, from the following path
(d:\Sql\Documents\Amit\log.txt) and display its contents on the Excel
sheet. How should I record the macro in order to perform this
operation?


Best Regards

Amit.

 

[liddlem]

Hi Amit
To record the macro, do the following.

1. Open Excel.
2. From the menu bar, click 'Tools/Macro/Record Macro'
- a. Give your macro a name as requested by the 'Record Macro' dialogu
box.
3. When the record macro toolbar is displayed, Excel now records th
keystrokes that you type, so...
a. Click the 'File Open' tool.
b. Find and open the text file that you wish to use.
c. Choose the appropriate options from the 'Text Import Wizard'.

4. If you wish to format the data (Bold, Sorted etc. etc) then do thi
as well.
5. When you are finished, close the 'Macro Record' tool.

TIP : Amit, if you are not familar with macros yet, you should firs
practice the steps that you intend recording so that you get 'clean
source code.

You may need to edit the macro (VB) code so that you can open a numbe
of text files