Read/Write data to/from text files from a spreadsheet.

[lothario]

Hi,

I have:

1. A text file called "t.txt" which contains just 1 line.
This 1 line contains just 1 value. Call this value "x".

2. An spreadsheet cell formula that does some calculations
and returns the value "y".


I need to:

1. Change the value "x" in the "t.txt" file to value "z".

2. Where z = x - y


So the steps would be:

1. Get value "x" from "t.txt" file. By the way,
the name "t.txt" comes from cell a12.

2. Get value "y" from cell d34.

3. Subtract "x" from "y" and assign the result to "z".

4. Replace the value "x" in "t.txt" file with value "z".


Can you tell me - How can this be done WITHOUT using any VBA code?
Can this be done using just formulas? If yes, please show me how?

Thanks.
Luther

 

[Phobos]

Can you tell me - How can this be done WITHOUT using any VBA code?

It can't

Can this be done using just formulas?
No

If yes, please show me how?

It can't

Thanks.
Luther

You're welcome

P

 

[lothario]

Ok then how do I do this with VBA code?

 

[Phobos]

Option Explicit

Sub XtoZ()

'1. Get value "x" from "t.txt" file. By the way,
'the name "t.txt" comes from cell a12.

Dim fl As String
fl = ActiveSheet.Range("a12").Value
Dim fn As Long
fn = FreeFile
Open fl For Input As #fn

Dim dblX As Double
Input #fn, dblX

'2. Get value "y" from cell d34.

Dim dblY As Double
dblY = ActiveSheet.Range("d34").Value


'3. Subtract "x" from "y" and assign the result to "z".

Dim dblZ As Double
dblZ = dblY - dblX

'4. Replace the value "x" in "t.txt" file with value "z".

Close #fn

Kill fl

Open fl For Output As #fn
Write #fn, dblZ
Close #fn

End Sub




Create a new module (Insert | Module) and paste the above code into it.

You can assign the macro to a button on the worksheet if you want (this
ensures that the activesheet is the right one).

P

 

[lothario]

Thanks Phobos

 

[twaccess]

Phobos

I enjoyed this very brief thread... This is a subject close to my heart
as I have spent many hours cutting, parsing and pasting data from text
files to Excel and back again.... This opens a door for me for doing it
by vba.... along with membership of this forum too.

I ran the code and its cute... (Sorry, I'm easily impressed !!)

I wonder if you would mind putting some explanatory notes against
certain elements of the code such as "dim dblZ" "#fn" etc to help me
understand better how this code works.

Thanks


tdub

 

[Phobos]

Thanks Phobos

You're welcome

P

 

[Phobos]

I enjoyed this very brief thread... This is a subject close to my heart
as I have spent many hours cutting, parsing and pasting data from text
files to Excel and back again.... This opens a door for me for doing it
by vba.... along with membership of this forum too.

You could have gained access to this forum at any time to ask about your
specific issues, that's why the group exists.

I ran the code and its cute... (Sorry, I'm easily impressed !!)

I wonder if you would mind putting some explanatory notes against
certain elements of the code such as "dim dblZ" "#fn" etc to help me
understand better how this code works.

I take it you are new to VBA? OK, I'll do it, but you must look in the help
files as well.

Thanks

You're welcome.




Sub XtoZ()

'1. Get value "x" from "t.txt" file. By the way,
'the name "t.txt" comes from cell a12.

Dim fl As String
fl = ActiveSheet.Range("a12").Value 'Assign the string in a12 to fl
Dim fn As Long 'fn is going to be the file number
fn = FreeFile 'Look up the FreeFile function in help
Open fl For Input As #fn 'Opens a text file

Dim dblX As Double 'We'll need a variable to store the value from the
file
Input #fn, dblX 'dblX now equals the value in the file

'2. Get value "y" from cell d34.

Dim dblY As Double 'dblY will hold the value of "y"
dblY = ActiveSheet.Range("d34").Value 'Y = d34


'3. Subtract "x" from "y" and assign the result to "z".

Dim dblZ As Double 'Z is the result of the operation
dblZ = dblY - dblX 'Z = Y - X

'4. Replace the value "x" in "t.txt" file with value "z".

Close #fn 'Close the file (so we can kill it)

Kill fl 'Kill the file ( fl = ActiveSheet.Range("a12").Value )

Open fl For Output As #fn 'This line re-creates the file
Write #fn, dblZ 'Write the new value of Z to the file
Close #fn 'Close the file

End Sub




Some other stuff:
*****************

dblX, dblY & dblZ

I sometimes (but not always) declare variables with certain prefixes to
indicate their type e.g.

dblName = Double
intName = Integer
strName = String
lngName = Long

It helps when you are developing a large procedure to avoid having a
variable and a procedure with the same name.

hth


P

 

[Tom Ogilvy]

Here is another source of information:
http://support.microsoft.com/support/excel/content/fileio/fileio.asp
File Access with Visual Basic® for Applications