locked
How to convert a part of a worksheet into txt. RRS feed

  • Question

  • Srs, Hello!
    I have to convert a range from a spreadsheet to TXT, in the code below it converts the entire spreadsheet, but I need that part of the spreadsheet to be converted to txt, and another part to another txt block. I tried to select only one range but I still have to learn a bit more of VBA.
    If you can help me!
    nome_arquivo = ActiveSheet.Range("a1").Value
       template_file = ActiveWorkbook.FullName
       
     fileSaveName = Application.GetSaveAsFilename(InitialFileName:=nome_arquivo & _
        ".txt", fileFilter:="Text Files (*.txt), *.txt")
    
        Dim newBook As Workbook
        Dim plan As Worksheet
          
        Set newBook = Workbooks.Add
    
    ThisWorkbook.ActiveSheet.Copy Before:=newBook.Sheets(1)
     
        For Each plan In newBook.Sheets
            If plan.Name <> ActiveSheet.Name Then
                newBook.Worksheets(plan.Index).Delete
            End If
           
            
        Next
     
        newBook.SaveAs Filename:= _
        fileSaveName, FileFormat:=xlTextWindows, _
        CreateBackup:=False
     
      
        newBook.Close SaveChanges:=True
        Set newBook = Nothing
        
    
     
     
    End Sub

    Tuesday, June 13, 2017 11:41 PM

Answers

  • Microsoft Office Professional Plus 2016
    Windows 7 Home Basic

    My AddIn works with this system.

    So either the file is modified during the download by your system or your security settings prevents to open the file.

    Anyway, based on your macro above, a simple as possible way is to copy the selected cells into a new file and save it as text file. See code below.

    Andreas.

    Sub Test()
      Dim Where As Range
      Dim Wb As Workbook
      Dim Filename As String
      
      'Step 1: Ask the user for a file name
      
      'Create a file dialog
      With Application.FileDialog(msoFileDialogSaveAs)
        'Setup a default name and path
        .InitialFileName = ThisWorkbook.Path & Application.PathSeparator & Range("A1").Value
        'Use the Textfile filter by default
        .FilterIndex = 12
        'Show the dialog, exit if aborted
        If Not .Show Then Exit Sub
        'Get the file name
        Filename = .SelectedItems(1)
        'Be sure we have a .txt at the end!
        If Right(Filename, 4) <> ".txt" Then
          Filename = Filename & ".txt"
        End If
      End With
      
      'Step 2: Create the text file
      
      'Screen off, runs faster
      Application.ScreenUpdating = False
      'Get the currently selected cells
      Set Where = Selection
      'Make a new file, one sheet
      Set Wb = Workbooks.Add(xlWBATWorksheet)
      'Copy our cells
      Where.Copy Wb.Sheets(1).Range("A1")
      Application.CutCopyMode = False
      'Save it
      Wb.SaveAs Filename, xlTextWindows
      'Close it
      Wb.Close
    End Sub

    • Marked as answer by Cdilenes Monday, June 19, 2017 10:21 PM
    Sunday, June 18, 2017 6:48 AM

All replies

  • Download this AddIn:
    https://www.dropbox.com/s/m7qdh5gs3wqb99r/CSV.xla?dl=1

    There is no need to install, just open the xla file. After that select the cells, right click and choose the CSV menu at the bottom.

    The code is not protected, you can view and copy (part of) the code if you like. But the macro also supports macro recording, so there is not really a need to copy the code.

    Andreas.

    Wednesday, June 14, 2017 7:10 AM
  • Andreas, thanks for replying.

    I open the xls and selected cells with the right button and did not enable any CSV menu at the bottom.

    There is also no code in the file!

    Wednesday, June 14, 2017 11:14 PM
  • I open the xls and selected cells with the right button and did not enable any CSV menu at the bottom.There is also no code in the file!

    If you downloaded a XLS you did something wrong, the link points to a XLA file.

    Copy the XLA file into a secure location, e.g. your documents folder. Make sure that your security settings did not block to open downloaded files.

    Also check your security settings and make sure that macros are enabled.

    Andreas.

    Thursday, June 15, 2017 7:12 AM
  • I downloaded the XLA file, copied it to the same folder. I did not discover anything that could block the opening of the downloaded file.

    I already checked my security settings and the macros are enabled, but nothing happened. Select the cells, right click and not enabled anything the CSV at the bottom or any corner of the page or am I doing something wrong!


    Thursday, June 15, 2017 6:21 PM
  • or am I doing something wrong!


    I guess so, see screenshot below.

    Andreas.

    Friday, June 16, 2017 7:10 AM
  • Well I checked again and for me in the file did not come any code or supplement. But I need help to create a macro in which I set the range and automatically create a txt , not the whole sheet.


    Friday, June 16, 2017 10:18 PM
  • Well I checked again and for me in the file did not come any code or supplement.

    Which OS and Office version do you have?

    Andreas.

    Saturday, June 17, 2017 7:58 AM
  • Microsoft Office Professional Plus 2016
    Windows 7 Home Basic
    Saturday, June 17, 2017 8:40 PM
  • Microsoft Office Professional Plus 2016
    Windows 7 Home Basic

    My AddIn works with this system.

    So either the file is modified during the download by your system or your security settings prevents to open the file.

    Anyway, based on your macro above, a simple as possible way is to copy the selected cells into a new file and save it as text file. See code below.

    Andreas.

    Sub Test()
      Dim Where As Range
      Dim Wb As Workbook
      Dim Filename As String
      
      'Step 1: Ask the user for a file name
      
      'Create a file dialog
      With Application.FileDialog(msoFileDialogSaveAs)
        'Setup a default name and path
        .InitialFileName = ThisWorkbook.Path & Application.PathSeparator & Range("A1").Value
        'Use the Textfile filter by default
        .FilterIndex = 12
        'Show the dialog, exit if aborted
        If Not .Show Then Exit Sub
        'Get the file name
        Filename = .SelectedItems(1)
        'Be sure we have a .txt at the end!
        If Right(Filename, 4) <> ".txt" Then
          Filename = Filename & ".txt"
        End If
      End With
      
      'Step 2: Create the text file
      
      'Screen off, runs faster
      Application.ScreenUpdating = False
      'Get the currently selected cells
      Set Where = Selection
      'Make a new file, one sheet
      Set Wb = Workbooks.Add(xlWBATWorksheet)
      'Copy our cells
      Where.Copy Wb.Sheets(1).Range("A1")
      Application.CutCopyMode = False
      'Save it
      Wb.SaveAs Filename, xlTextWindows
      'Close it
      Wb.Close
    End Sub

    • Marked as answer by Cdilenes Monday, June 19, 2017 10:21 PM
    Sunday, June 18, 2017 6:48 AM
  • Thanks for the help me , real Thanks!

    Cleber

    Sunday, June 18, 2017 7:35 PM