Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Creating A Text File With Vba

  1. #1
    Join Date
    6th October 2006
    Posts
    20

    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,

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    6th October 2006
    Posts
    20

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    Re: Creating A Text File With Vba

    Z,

    This is just writing in a loop.

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    6th October 2006
    Posts
    2

    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 at 05:23.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd October 2011
    Posts
    2

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,042

    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, 2012, 2013



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

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

  8. #8
    Join Date
    22nd October 2011
    Posts
    2

    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.


    VB:
    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 at 05:28. Reason: USE CODE TAGS

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,042

    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, 2012, 2013



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

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

  10. #10
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,042

    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, 2012, 2013



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

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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 6
    Last Post: November 21st, 2007, 08:11
  2. Creating A .txt File
    By mhoctober in forum EXCEL HELP
    Replies: 4
    Last Post: April 2nd, 2007, 07:17
  3. Creating Text File
    By Bugsy in forum Excel and/or Email Help
    Replies: 1
    Last Post: October 28th, 2005, 03:25
  4. creating a text file through a macro
    By TroyArthur in forum EXCEL HELP
    Replies: 10
    Last Post: August 23rd, 2005, 13:30
  5. creating a csv file
    By sdj1704 in forum EXCEL HELP
    Replies: 9
    Last Post: February 20th, 2004, 01:38

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