Can you access a system variable from a excel spreadsheet

[Steve]

I want to access a value held in a system variable defined
by a script that counts the number of lines in a text
file. Can this be done??

 

[Tom Ogilvy]

You would need to make a User Defined function (UDF) that returns the value
of the variable.

Public gSysVar as Long


Public Function ReturnValue()
Application.Volatile
ReturnValue = gSysVar
End Sub



=300*ReturnValue()

 

[Steve]

Or can I automate the refresh external data
function???. . this would do what I want. I have set up a
import of external data within excel and counta those
lines, all i need to do is automatically refresh that
external data. . . if that could happen it would be so
very cool!!!!!!!

Oh please Mr. Ogilvy tell me that can be done. . .

 

[Tom Ogilvy]

If you are using a querytable, then you can refresh it. If you have a
macro that reads the data into a worksheet at a specific location, you can
run the macro again. I don't know what you have already set up, so I can't
say what you can do.

 

[Steve]

Mr. Ogilvy,

Your other VB solution for counting lines in a text file
is a far more elegant solution for what I want to do. Just
for my knowledge, is there a way to have that macro run
automatically at a set interval, I assigned a hot key to
the macro but I would like to automate it if I can.

your most humble student. . .

 

[Tom Ogilvy]

Look at Chip Pearson's page on using Ontime

http://www.cpearson.com/excel/ontime.htm

You also might want to look at Dane Delouis' post in that thread - his
method may be faster if the file is very large.