• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2028
  • Last Modified:

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.
0
W.E.B
Asked:
W.E.B
  • 2
  • 2
1 Solution
 
rspahitzCommented:
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
 
NorieCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now