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
'Here we handle non-casper numbers.
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
' Read last number from file
FNum = FreeFile()
Open FName For Input As FNum ' How to open for reading and writing?
Line Input #FNum, qnum
' Increment by 1
quoteNum = Int(qnum) + 1
quote = qProd & quoteNum
Range("O2").Value = quote
' Done with reading, reopen file for writing
' Write the used number to the file.
oNum = FreeFile
Open FName For Output As oNum ' Error: File already open
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?