excel - need macro to import text file into worksheet with formulas

[bryman]

I need a macro to import a text file into a worksheet with formulas. I manually import a text file into a worksheet and then copy formulas. I know there has to be a simpler way. Thanks

 

[IFRs]

Ever try Data - Import External Data - Import Data and then pick the file?

 

[Zathras]

Surprising as it may seem, a file with .csv extension can have formulae. (At least in Excel 2K)
For example, the following file has the name testdata.csv:

X,Y,Sum,Sqrt
4,5,=A2+B2,=Sqrt(c2)
7,9,=A3+B3,=Sqrt(c3)

When I open it in Excel 2K, columns C and D have the formulas indicated.

A .txt file is just a .txt file, however.

 

[Zathras]

IFRs: Simultaneous posts are a problem on this site. I just tried your suggestion with a .txt file that has formulae and it works! Very good -- thanks for the post.

bryman: Please ignore the last sentence of my post.

 

[ETID]

ODBC...

in a blank excel worksheet, from the menu...

Data>get external data>new database query>select text files from the list in the left tab...follow the wizard, or select the use MS query option,...carefully select options you want on each window,...such as "read only" check boxes and what not...
then return data to ms excel.

when the data is returned, you can right click in the data area and set properties such as refresh on open, autofill formulas, and so on.

Then you need to only enter formulas in excel 1 time
and they will expand or contract as the source data(your text file) grows in size or shrinks.

You now only need to open the spreadsheet. to view the current data in that text file.

now when ever the text file is updated the workbook will reflect the changes.(if you refresh...auto or manually)