Announcement

Collapse
No announcement yet.

Read from file and write to file in one go?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Read from file and write to file in one go?

    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 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?

  • #2
    Re: Read from file and write to file in one go?

    This works for me. Hopefully you can adapt the structure to suit
    Code:
    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

    Comment


    • #3
      Re: Read from file and write to file in one go?

      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:
      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

      Comment

      Working...
      X