Export / Import to / from Text file

[Guest]

1)What I am wanting to do is export 1 cell ("I4") to a text file when the
focus is moved from that cell.
2)When the form is opened I want to be able to import the number from the
text file, and increase it by 1.

My employer wants to have the number that is in the "I4" cell to be auto
increased every time he opens that form. I have some programming experience
just not alot with importing or exporting anything to anything. Any help or
suggestions will be greatly appreciated.

 

[Tom Ogilvy]

You can get some useful information on reading and writing files here:

http://www.cpearson.com/excel/imptext.htm import/export text files

http://www.applecore99.com/gen/gen029.asp

More information on reading and writing text files from VBA

http:/www.cpearson.com/excel/events.htm
information on Chip Pearson's site on events. The Workbook_Open event can
be used to execute code when opening a workbook.

 

[Guest]

Sorry Tom I tried figuring it out from the link you gave me but im have a
really difficult time with this. Here is what I have even though its wrong.
Can you please tell me how to fix this.

Private sub Worksheet_Activate()
intGrFile = FreeFile
Open "C:\GR.txt" For Input As intGrFile
Range("I4").value = intGrFile + 1
Close intGrFile
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
intGrFile = FreeFile
Open "C:\GR.txt" For Output As intGrFile
If Range("I4").value > intGrFile then
intGrFile = Range("I4").value
End If
Close intGrFile
End Sub

Any help greatly appreciated from anyone.
Thanks

 

[Tom Ogilvy]

Private Sub Worksheet_Activate()
intGrFile = FreeFile
Open "C:\GR.txt" For Input As intGrFile
Line Input #intGrFile, sStr
Range("I4").Value = CLng(sStr) + 1
Close intGrFile
Open "C:\GR.txt" For Output As intGrFile
Print #intGrFile, Range("I4").Value
Close intGrFile
End Sub

is a basic approach. You might need to put in some more conditions to
insure the you only increase once on each opening.

Another approach is to store the value in a defined name

insert=>Name=>Define
name: cnt
refersto: =6

In I4 you would have

=cnt

and you could have your code increment cnt.

 

[Guest]

Ok I did as you said Tom but I am still having issues. On the intGrFile its
throwing a message of "variable not defined". Not sure what to define it as.
I tried setting ' Dim intGrFile as NewFile ' in Option Explicit but no good.
As of this moment I have nothing in my Option Explicit, and am unsure of how
to correct the issue. Here is what I have so far in my code.

Private Sub Worksheet_Activate()
intGrFile = FreeFile
Open "C:\GR.txt" For Input As intGrFile
Line Input #intGrFile, sStr
Range("I4").Value = CLng(sStr) + 1
Close intGrFile
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
intGrFile = FreeFile
Open "C:\Gr.txt" For Output As intGrFile
Line Input #intGrFile, sStr
If Range("I4").Value > sStr Then
Print #intGrFile, Range("I4").Value
End If
Close intGrFile
End Sub

The code that is in the Worksheet_Activate should I put this code instead in
Workbook_Activate of something. As it is now the Worksheet I am working on is
the only worksheet in the workbook so it never appears to be activated.
Thanks for you help so far.

 

[Tom Ogilvy]

Private Sub Worksheet_Activate()
Dim intGrFile as Long, sStr as String
intGrFile = FreeFile
Open "C:\GR.txt" For Input As intGrFile
Line Input #intGrFile, sStr
Range("I4").Value = CLng(sStr) + 1
Close intGrFile
Open "C:\GR.txt" For Output As intGrFile
Print #intGrFile, Range("I4").Value
Close intGrFile
End Sub

 

[Guest]

I got it figured out. In the Workbook I have this code:

Option Explicit
Dim sStr As String

Private Sub Workbook_Open()
Open "C:\GR.txt" For Input As #1
Input #1, sStr
Sheet1.Range("I4").Value = CLng(sStr) + 1
Close #1
End Sub

In the Worksheet I have this:

Option Explicit
Dim sStr As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Open "C:\Gr.txt" For Output As #1
If Range("I4").Value > sStr Then
Write #1, Range("I4").Value
End If
Close #1
End Sub

Thanks for all your help Tom. It got me going in the right direction.

C_Ascheman