Announcement

Collapse
No announcement yet.

Creating A Text File With Vba

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

  • Creating A Text File With Vba

    Hi, I'm a newbie - hope you can help. The problem is this:

    each row in sheet1 contains variables that, when applied to cells in sheet2, calculate a final figure. I have written code that selects each row, distributes its variables in sheet2, and shows the result back in sheet1, then selects the next row, and so on until all rows have been calculated.

    The calculation is complex, and I would like to create a text file that stores the calculation for each row in the same text file. The calculation is a 9 columns by 33 rows, and I would like to preserve this shape in the text file, though its OK not to have columns not lining up.

    There is a similar problem on an old thread but this requires only one iteration of "open file, write values, close file". I need "open file, write values, write more value, close file". Thanks for considering my problem,

  • #2
    Re: Creating A Text File With Vba

    Have you looked at Microsoft Scripting Runtime ? A search on MSR should give you more examples. Here is one looping through reading one text file and writing to another one.

    http://www.ozgrid.com/forum/showthread.php?t=53975

    If you can not make sense of it post back and we can explain in more detail.

    HTH

    Carl
    locii Insight
    Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

    Comment


    • #3
      Re: Creating A Text File With Vba

      Thanks Carl,

      I've never heard of MSR but I'll have a look and see wht I can dig up.

      Regards

      Z

      Comment


      • #4
        Re: Creating A Text File With Vba

        Z,

        This is just writing in a loop.

        Code:
        Sub WriteTextFile()
             ' Writing a text file using File System Object in VBA
             ' This code requires a reference (In the VBE Tools > References) to Microsoft Scripting Runtime
             
            Dim FSO As FileSystemObject
            Dim FSOFile As TextStream
            Dim FilePath As String
            Dim NoOfLoop As Integer
             
            FilePath = "c:\WriteTest.txt" ' create a test.txt file or change this
             
            Set FSO = New FileSystemObject
                 ' opens  file in write mode
                Set FSOFile = FSO.OpenTextFile(FilePath, 2, True)
                'loop round adding lines
                For NoOfLoop = 1 To 10
                    ' write your code here
                    FSOFile.WriteLine (" I am writing line " & NoOfLoop)
                Next NoOfLoop
                
                FSOFile.Close
        End Sub
        Also you do not have to use MSR. I have taken to using it but this method maybe easier for you see :-

        http://www.ozgrid.com/forum/showthread.php?t=49686

        HTH

        Carl
        locii Insight
        Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

        Comment


        • #5
          Re: Creating A Text File With Vba

          EDIT: Just listen to the fella above me, he has a better answer for you anyway Good luck!

          Can you post the code you were looking at that only required one iteration?

          It sounds like you can split it up across your own code to accomplish the same thing

          for instance, your code is: (psuedo)

          DO
          cell3 = cell1 * cell2
          LOOP until end

          their code is

          open
          write cell3
          close

          can't you do:

          DO
          open txt file
          cell3 = cell1 * cell2
          write cell3, ",", CRLF
          LOOP
          CLOSE

          Maybe I didn't follow what you were saying too well, but it sounds like you can probably modify what you already have to work for you.
          Last edited by stevemcguigan; October 6th, 2006, 05:23.

          Comment


          • #6
            Re: Creating A Text File With Vba

            It may also be worth noting that the file being created needs to be in the root directory (i.e. C:\). I tried this by drilling down just a wee bit (i.e. C:\blahblah\textfile.txt), and I get the message that the path could not be found. Apparently, it only works in the root. Not a big deal, of course, but thought it was worth mentioning. Has anyone else experienced this?

            Comment


            • #7
              Re: Creating A Text File With Vba

              Hello

              Did you create the folder C:\blahblah (either manually or through code)?
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment


              • #8
                Re: Creating A Text File With Vba

                I tried to create a file in the C:\blahblah directory via code. Basically, I modified the following, by changing "c:\myfile.txt" (which worked fine) to "c:\blahblah\myfile.txt", but this produced the error that the path could not be found.


                Code:
                 crete_textfile() 
                     
                    Const FILENAME = "c:\myfile.txt" 
                     
                    Dim My_filenumber As Integer 
                     
                    My_filenumber = FreeFile 
                     
                    Open FILENAME For Output As #My_filenumber 
                     
                    Write #My_filenumber, "Hello" 
                     
                    Close #My_filenumber 
                     
                End Sub
                Last edited by Wigi; October 22nd, 2011, 05:28. Reason: USE CODE TAGS

                Comment


                • #9
                  Re: Creating A Text File With Vba

                  Hi

                  I suspect that you have a typo, or that the directory does not exist. In my testing it works fine and as expected.
                  Regards,

                  Wigi

                  Excel MVP 2011-2014

                  For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                  -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                  Comment


                  • #10
                    Re: Creating A Text File With Vba

                    Please use [CODE] tags, rather than using other formatting tags for code. Thanks in advance, I corrected it already, but please pay attention in the future.
                    Regards,

                    Wigi

                    Excel MVP 2011-2014

                    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                    Comment

                    Working...
                    X