The basic procedure here is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("O2"), Target) Is Nothing Then
If IsNumeric(Left(Range("O2"), 1)) Then
Dim qProd As String
Dim FName As String
Dim FNum As Integer
Dim oNum As Integer
Dim qnum As String
Dim quote As String
qProd = Left(Range("O2"), 1)
FName = "\\datasrv\Quotes\" & qProd & ".txt"
If Dir(FName) <> "" Then
FNum = FreeFile()
Open FName For Input As FNum
Line Input #FNum, qnum
quoteNum = Int(qnum) + 1
quote = qProd & quoteNum
Range("O2").Value = quote
oNum = FreeFile
Open FName For Output As oNum
Print #oNum, quoteNum
MsgBox "Not found: " & FName, vbCritical, "ERROR"
Our estimator will enter the quote number from our estimating system and fire the sql
- or -
He will enter a single letter (his first initial) and the system will return the next number for his sequence. (t10000, t10001, etc...)
To do this I have a text file with the last number used for each of our estimators. I want to read the number, increment by 1 and write back to the file.
The problem I'm having:
Opening the file As Output doesn't allow me to read the file. Why? All of the references that I'm seeing say that "Output" is a read/write mode.
No biggie, I can type a bit more... I have tried opening the file for input, closing the file and then reopening it for output. That's what I'm trying to accomplish with the code above. However, when I get to reopening the file for output, Excel carps at me that the file is already open.
Output: A sequential access mode that allows the file to be read or written to. In this
mode, a new file is always created. (An existing file with the same name is deleted.)
Que paso? How can I open the file for both reading AND writing, or failing that, how can I really close the file so Excel doesn't think it's still open?