Read from file and write to file in one go?

[LostInDaJungle]

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Intersect(Range("O2"), Target) Is Nothing Then 
        If IsNumeric(Left(Range("O2"), 1)) Then 
            ActiveWorkbook.RefreshAll 
        Else 
             '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 
                Close #FNum 
                 
                 ' 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 
                Close #oNum 
            Else 
                MsgBox "Not found: " & FName, vbCritical, "ERROR" 
                Exit Sub 
            End If 
        End If 
    End If 
End Sub 

The basic procedure here is as follows:

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.

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.)

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.

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?

[trunten]

This works for me. Hopefully you can adapt the structure to suit

Option Explicit 
 
Sub readWriteTest() 
    Static i As Long 
    Dim s As String 
     
    If i = 0 Then i = 1 
     
    Open "H:\test.txt" For Input As #1 
    While Not EOF(1) 
        Line Input #1, s 
        Debug.Print s 
    Wend 
    Close #1 
     
    Open "H:\test.txt" For Append As #1 
    Print #1, "This is a test" & i 
    Close #1 
     
    i = i + 1 
End Sub 

[LostInDaJungle]

Hmmm, it looks like what was happening here was the Workbook_Change method was looping something crazy. My code wants to read the value in Cell O2 and then update that value. It looks like when it updates that value, the Workbook_Change method gets called again which changes the workbook, etc...

Since the Workbook_Change method got called before the file Close, the file was still open when it looped back around to my code.

The answer was to disable Events before running the code.

Updated Code:

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Intersect(Range("O2"), Target) Is Nothing Then 
        If IsNumeric(Left(Range("O2"), 1)) Then 
            ActiveWorkbook.RefreshAll 
        Else 
             'Here we handle non-casper numbers.
            Dim qProd As String 
            Dim FileName As String 
            Dim qnum As String 
            Dim quote As String 
             
            qProd = Left(Range("O2"), 1) 
            FileName = "\\datasrv\Quotes\" & qProd & ".txt" 
            If Dir(FileName) <> "" Then 
                Application.EnableEvents = False 
                 ' Read last number from file
                Open FileName For Input As #1 
                Line Input #1, qnum 
                quoteNum = Int(qnum) + 1 
                quote = qProd & quoteNum 
                Range("O2").Value = quote 
                Close #1 
                 ' Write the used number to the file.
                Open FileName For Output As #1 
                Print #1, quoteNum 
                Close #1 
                Application.EnableEvents = True 
            Else 
                MsgBox qProd & " Is not a recognized estimator. " & vbNewLine & "Not found: " & FileName, vbCritical, "ERROR" 
                Exit Sub 
            End If 
        End If 
    End If 
End Sub