Export to text files by macro

[YasserKhalil]

I have large data in Column A and I want export every 500 of these data to a text file ....
Can you help me about that?
A1:A500 should be exported to text file called Yasser1
and A501:1000 should be exported to text file called Yasser2
and so on
the range is very large
A1:A650000

 

[YasserKhalil]

UP

 

[jim may]

This should get you started (if not completed). Change IN RED to your Path before trying

Jim

Sub COPYTOTXTFILE()
Dim BNum as Long, SNum as Long, FilN as long

BNum = 1
SNum = 500
FilN = 1
Range("A1").Select
Do Until ActiveCell = ""
    Range(Cells(BNum, 1), Cells(SNum, 1)).Copy
    Workbooks.Add (xlWBATWorksheet)
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\James\Documents\MrExcel\Yasser" & FilN & ".txt"
    ActiveWorkbook.Save
        ActiveWindow.Close
    Application.DisplayAlerts = True
    BNum = BNum + 500
    SNum = SNum + 500
    FilN = FilN + 1
ActiveCell.Offset(500).Select
Loop
End Sub

 

[YasserKhalil]

Good Mr. jim may
The text files are exported well but when opening them I found strange characters
I don't expect that!!

 

[YasserKhalil]

UP

 

[jim may]

"when opening them" << What Program are you using to Open your *.txt in?

Try MS Word or WordPad

Opening a *.txt file using Excel will automatically bring up the IMPORT WIZARD...

Jim

 

[jim may]

Better still, add to the existing code line below the RED PORTION

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\James\Documents\MrExcel\Yasser" & FilN & ".txt", FileFormat _
:=xlTextMSDOS

 

[trunten]

I'm not 100% sure of the format you need these files in, or to what you will use them for afterwards. But, just in case what you are after is just essentially a text log of the data items try this on for size:

Sub exportData()
    
    Dim i As Integer, i2 As Integer
    Dim myFile As String
    
    i = 1
    i2 = 1
    
    For Each c In Range("A1:A2001") '<<< Change this to suit your needs
        If i2 = 501 Then
            i = i + 1
            i2 = 1
        End If
        myFile = "Yasser" & i
        addToText c.text, myFile
        i2 = i2 + 1
    Next
    
End Sub

Sub addToText(ByVal text As String, myFile As String)
On Error Resume Next

    Dim myPath As String
    Dim fnum As Variant
    
    myPath = "C:\" '<<< Change this to suit your needs
    myFile = myPath & myFile
    fnum = FreeFile()
    
    Open myFile For Append As fnum
    Print #fnum, text
    Close #fnum
    
End Sub

 

[YasserKhalil]

Thank you very much
Excellent !!!

 

[YasserKhalil]

Thank you very much Mr. jim may
Excellent solution