Save as Text files from Excel cells

BOGIA

Active Member
Joined
May 23, 2005
Messages
266
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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.

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

Active Member
Joined
May 23, 2005
Messages
266
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

Board Regular
Joined
Aug 8, 2005
Messages
237
Here are comments for the code...

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

Active Member
Joined
May 23, 2005
Messages
266
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
 

Forum statistics

Threads
1,137,206
Messages
5,680,191
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top