Help write to a text file, a bit confused.

[mrmagoo_83]

I need some help. I have tried to do this on my own using other examples, but I am a bit lost.

I have a sheet in my excel file, a lot of data in column 1. I am wanting to open a text file and write all that data to that text file.

Each line in the column is like "The letter is", the macro adds a charater, so the column then looks like "The letter is A", then B, then C, etc...

I want to write the entire column to a text file such as A.txt (when everything is A) and B.txt when everything is (B).

I have the macro to run through and edit each line and all that; however, I cannot get this thing to open a file for nothing.

I tried the following, where the Filename was a variable that ends up looking like "C:\A.txt"; but i get an error 438 on the line that attempts to open the text file. Line is also a string Variable that has already been set to a value.

Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(Filename)
    Set ts = f.OpenAsTextFile(ForAppending, TristateTrue)
    ts.Write Line
    ts.Close

Can anyone help?

 

[crobertson]

Are you wanting to append data to a text file from excel?

 

[mrmagoo_83]

Well sort of, I am more wanting to create a new file with each incerment of the count.

Each files name will change by one, and the contents will change by one.

So I am not really appending the files, but creating new ones.

A.txt
B.txt
C.txt
etc....

 

[crobertson]

Here is what I am doing, exporting a specified range to a delimited file... I don't know if this helps, but I don't know if you can nail down a specific range for each text file.

Anyway... I hope this helps

Sub X() 
    SaveAsDelimited Range("'MAS90 Data'!A1:B4"), "," 
 End Sub 
  
Sub SaveAsDelimited(Data As Range, Delimiter As String) 
    Dim Fname As Variant 
    Dim strBuf As String 
    Dim rngTemp As Range 
    Dim rngCell As Range 
    Dim intUnit As Integer 
    Dim strDelimit As String 
    Dim strPath As String 
    Dim strName As String 
    
    MsgBox "Please enter the job number as the filename." 
    Fname = Application.GetSaveAsFilename( _ 
    fileFilter:="CSV (*.csv), *.csv") 
    If Fname = False Then 
    MsgBox "You didn't enter a filename!" 
    End If 
    
    intUnit = FreeFile 
    Open Fname For Output As intUnit 
    For Each rngTemp In Data.Rows 
        strBuf = "" 
        strDelimit = "" 
        For Each rngCell In rngTemp.Cells 
            If InStr(rngCell.Text, ",") > 0 Then 
                               strBuf = strBuf & strDelimit & """" & rngCell.Text & """" 
            Else 
                strBuf = strBuf & strDelimit & rngCell.Text 
            End If 
            strDelimit = Delimiter 
        Next 
        Print #intUnit, strBuf 
    Next 
    Close intUnit 
      
End Sub

 

[mrmagoo_83]

I will give that a shot, I am still not understanding all this FreeFile stuff, but I am seeing it in a lot of the code to do such things, so I will give it a try when i get to the casa.

 

[bandit_1981]

Open SaveFile For Output As #1 ' Open file and make it ready for output.
Print #1, strData
Close #1

This will create or recreate whatever file name you have in the savefile varaible. THen it puts the data from the strData in it.

 

[mrmagoo_83]

ok that works partially.

The problem is I have like a dozen lines that need to be written to each file, this only writes one, then overwrites it with the next.

If I misrespresented that somewhere I apologize.

 

[crobertson]

You would have to copy this sub for each range and then modify the range in the sub...

If you have dynamic data, that is to say

You may have A,B,C txt files today

and A,B,C,D text files tommorow, this will not work. Or it might I just don't know how to change it so that it will.

 

[mrmagoo_83]

Nevermind, I got it, just moved the stupid close line

 

[mrmagoo_83]

Well, now it works, but it creates the files A 1.txt, instead of A1.txt

I do this:

FileNum = Str(Count + 1)

Which = " 1"

How the heck do I get rid of that space?