Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Read from file and write to file in one go?

  1. #1
    Join Date
    16th March 2012
    Posts
    9

    Read from file and write to file in one go?

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th July 2011
    Posts
    532

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    16th March 2012
    Posts
    9

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: September 27th, 2011, 21:21
  2. Confirm File Has Read/Write Access
    By ltworf in forum EXCEL HELP
    Replies: 2
    Last Post: October 25th, 2007, 00:31
  3. Read Text File And Write Text
    By dubjay in forum EXCEL HELP
    Replies: 12
    Last Post: February 17th, 2007, 10:07
  4. Replies: 2
    Last Post: January 4th, 2007, 16:32

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno