Save as Text files from Excel cells

[BOGIA]

Hi all,

I would like to have a VB script to save cell's value from Excel to Text file (.txt).
Eg: cell A1 = Today is Monday
A2 = Tomorrow is Tuesday
Become Text1.txt with the content "Today is Monday"
Text2.txt with the content "Tomorrow is Tuesday"

I need this for a column from A2 to A100 (vary) and there should be 100 Text files will be created after the VB script run. Text1.txt, Text2.txt to Text100.txt files

Hope this explanation is clear enough to understand.

Thanks,

Bogia

 

[wongm003]

You can use this code... if there is no value in the cell it will not create a file. The text file will be created in the same folder as the workbook.

Public Sub make_text_files()
On Error Resume Next

    Dim fso As Variant, a As Variant
    Dim intCnt As Integer
    Dim strPath As String, strFileName As String
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    intCnt = 0
    strPath = ActiveWorkbook.Path
    
    For Each c In ActiveSheet.Range("A2:A100")
        If Len(Trim(c.Value)) > 0 Then
            intCnt = intCnt + 1
            strFileName = strPath & "\Text" & intCnt & ".txt"
            Set a = fso.CreateTextFile(strFileName, True)
            a.writeline (c.Value)
            a.Close
            a = Nothing
        End If
    Next c
       
End Sub

 

[BOGIA]

Save as Text file from Excel cells

Wongm003,

Thanks very much for your code. It creates exactly files I need. I'm learning to write VB code and am just the beginer so could you please commence each line of the code if you have a minute free. It'll help me to understand the code better and to use it to something else, I'm very much appreciated.

Regards,

Bogia

 

[wongm003]

Here are comments for the code...

Public Sub make_text_files()

'tell the application how to handle errors
'in this case ignore error and resume next line
On Error Resume Next

    'Dim -> declare variables and what type of data they contain
    Dim fso As Variant, a As Variant
    Dim intCnt As Integer
    Dim strPath As String, strFileName As String
    
    'create a file sytem object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'counter - used in the filename
    intCnt = 0
    
    'determine the path of workbook - used in the filename
    strPath = ActiveWorkbook.Path
    
    'go through each cell in the range
    For Each c In ActiveSheet.Range("A2:A100")
        
        'if the cell has a value (length of cell contents > 0)
        If Len(Trim(c.Value)) > 0 Then
        
            'increment the counter
            intCnt = intCnt + 1
            
            'determine next file name
            strFileName = strPath & "\Text" & intCnt & ".txt"
            
            'create and open a new text file
            Set a = fso.CreateTextFile(strFileName, True)
            
            'write the contents of the cell to a line in the text file
            a.writeline (c.Value)
            
            'finished with the text file
            'close the text file
            a.Close
            
            'release the text file
            a = Nothing
        End If
        
        'process next cell
    Next c
        
End Sub

 

[BOGIA]

Hi Wongm003,

Thanks very much for taking time to commence in your codes. It helps me to understand the code very clear now.

Once again, thank you for your kindness.

Regards,

Bogia