Save as txt vba

Hello,
can you please help,
Im using below code to save range on sheet as .txt
I need to name the saved text as cell value which is not included in the range.
range to save is "A1:A1000"
Save range as text with name from Cell "B1"

Sub Solo_SAVE_Range_txt()
    Sheets("Solo").Select
    Range("A1:A1000").Copy
    Sheets.Add.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    ActiveSheet.UsedRange.EntireColumn.AutoFit
    ActiveSheet.Move
    ActiveWorkbook.SaveAs Filename:="C:\Users\Wassim\Desktop\Reports\" & Range("B1").Value & ".txt", FileFormat:=xlTextPrinter, CreateBackup:=False
    ActiveWorkbook.Close False
End Sub

Open in new window


Thank you fro any help.
W.E.BAsked:
Who is Participating?
 
rspahitzConnect With a Mentor Commented:
I'm not quite sure of the question, but if all you want to do is save some cells as text, you could do that directly as follows (rather than using the saveAs, which saves the entire sheet):
Sub Solo_SAVE_Range_txt()
    Sheets("Solo").Select
    Dim FileName as String
    Dim FileNumber as Integer

   Filename="C:\Users\Wassim\Desktop\Reports\" & Range("B1").Value & ".txt"
   FileNumber = FreeFile()
   Open FileName for Output as #FileNumber 
   For row=1 to 1000
      Print #FileNumber, cells(row,1).Value
   Next
   Close #FileNumber 
End Sub

Open in new window

0
 
W.E.BAuthor Commented:
Hello,
Correct, I don't want to save the entire sheet,
only range A1:A1000

save as cell "B1" value    (example: cell B1 Value = Solo (03-21-2014 _ 03-21-2014))

I get error when I run your code

Compile error
variable not defined.

For row = 1 To 1000
Thanks,
0
 
W.E.BAuthor Commented:
thank you
0
 
NorieVBA ExpertCommented:
Perhaps if you get the filename before you copy the data to a new workbook.
Sub Solo_SAVE_Range_txt()
Dim strFileName As String

    With Sheets("Solo").Select
        strFileName = .Range("B1").Value & ".txt"
        .Range("A1:A1000").Copy
    End With

    Sheets.Add.Range("A1").PasteSpecial xlPasteAll

    Application.CutCopyMode = False

    ActiveSheet.UsedRange.EntireColumn.AutoFit

    ActiveSheet.Move

    ActiveWorkbook.SaveAs Filename:="C:\Users\Wassim\Desktop\Reports\" & strFileName, FileFormat:=xlTextPrinter, CreateBackup:=False

    ActiveWorkbook.Close False

End Sub 

Open in new window

0
 
rspahitzCommented:
For the error, try adding this near the top:

Dim row as Integer
Sub Solo_SAVE_Range_txt()
    Sheets("Solo").Select
    Dim FileName As String
    Dim FileNumber As Integer
    Dim row As Integer

   FileName = "C:\Users\Wassim\Desktop\Reports\" & Range("B1").Value & ".txt"
   FileNumber = FreeFile()
   Open FileName For Output As #FileNumber
   For row = 1 To 1000
      Print #FileNumber, Cells(row, 1).Value
   Next
   Close #FileNumber
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.