NEED HELP with this code for names

[Shawnathon]

Hi There Forum,

I have a code here that I use to generate a text file.
What I need to do is run the whole list in one shot. I have some lists with 10,000 names.

Right now i need to change the range amount and the name of the file to run the next text file. I run it for every 30 rows. I know I need to pre make the text file for it to save. (Do you know if XL can be coded to pre make the Text files also? or what program can do that? thanks)

Sub MakeTextFile()
 
'*** change dimension to use late binding ***
Dim FSO As Object 'FSO As Scripting.FileSystemObject
Dim TextStr As Object 'TextStr As Scripting.TextStream
 
Dim Rng As Range
 
'*** use create object to create a FileSystemObject ***
'Set FSO = New Scripting.FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
 
'*** Open a text file for appending ***
'*** if it does not already exist, then create it ***
ForAppending = 8
Set TextStr = FSO.OpenTextFile(Filename:="C:\MyFile\MyFile.txt", _
IOMode:=ForAppending, create:=True)
 
For Each Rng In Range("A1:A30")
If Rng.Value <> "" Then
TextStr.WriteLine Text:=Rng.Value
End If
Next Rng
 
 
TextStr.Close
Set FSO = Nothing
 
 
End Sub

So say I have 5000 rows to complete, it will generate 167 text files with 30 rows each in them. Please let me know if you have any questions, thanks!

Shawn

 

[Domenic]

Since you have not indicated how the text files should be name, the following code names them MyFile1.txt, MyFile2.txt, Myfile3.txt, etc.

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] MakeTextFiles()
 
    [color=darkblue]Dim[/color] FSO [color=darkblue]As[/color] [color=darkblue]Object[/color]
    [color=darkblue]Dim[/color] TextStr [color=darkblue]As[/color] [color=darkblue]Object[/color]
    [color=darkblue]Dim[/color] Rng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Cell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] ForAppending [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Num [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] FSO = CreateObject("Scripting.FileSystemObject")
     
    ForAppending = 8
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Num = 1
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LastRow [color=darkblue]Step[/color] 30
    
        [color=darkblue]Set[/color] TextStr = FSO.OpenTextFile(Filename:="C:\MyFile\MyFile" & Num & ".txt", _
            IOMode:=ForAppending, Create:=True)
            
        [color=darkblue]Set[/color] Rng = Range(Cells(i, "A"), Cells(i + 30 - 1, "A"))
         
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Cell [color=darkblue]In[/color] Rng
            [color=darkblue]If[/color] Cell.Value <> "" [color=darkblue]Then[/color]
                TextStr.WriteLine Cell.Value
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] Cell
         
        TextStr.Close
        
        Num = Num + 1
        
    [color=darkblue]Next[/color] i
        
    [color=darkblue]Set[/color] FSO = [color=darkblue]Nothing[/color]
    [color=darkblue]Set[/color] TextStr = [color=darkblue]Nothing[/color]
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]



[/font]

 

[Shawnathon]

Perfect! Thanks a million! This forum rocks! Appreciate it!

Thanks for the quick get back!
Shawn