CODEWITHSUNDEEP
excelvbatxt
Antonio Masia
Antonio Masia

Reputation: 21

Create multiple txt files from Excel with a condition

I need to find a way to create multiple txt files from an Excel, as shown in the picture.

In detail I need to create a file txt with the name "sez-A1" and another "sez-A2" and so on. Inside the txt I need to have the numbers of the 2 columns x and y from the Excel.

enter image description here

Upvotes: 0

Views: 451

Answers (1)

Gove
Gove

Reputation: 1804

Antonio, welcome to Stack Overflow. Based on your question, I'm going to assume that you are pretty new to VBA. The approach I'm taking assumes that your data are contiguous in the worksheet. If this code encounters a blank line, it will end. The idea here is to start at the first line of the worksheet and continue down to process the data. When a row in column A has a numeric value, we add a line to the current file. If the value in column A is not a number, we assume it's the name of the next file. If you have specific questions about what I have written, please ask in a comment and I'll try to answer.

    Sub makeFiles()
      ' this code requires the workbook to be saved on a local dirve (e.g. "c:\some\path")
      ' If saved on a remote drive (e.g. onedive) it will fail
    
      Dim r As Long
      Dim s As Worksheet
      Dim path As String
      
      Set s = ActiveSheet ' use this line build files from active sheet
      'Set s = Worksheets("Sheet1") ' use this line to choose a specific sheet
    
      ' This line is reading the path from the current workbook. 
      ' The workbook must be saved first.
       path = ThisWorkbook.path & Application.PathSeparator
      
      r = 1
      ' this is an un-needed file to make the code simpler.  I delete it below.
      Open path & "junk.txt" For Output As #1  
      
      Do Until s.Cells(r, 1).Value = ""
        If IsNumeric(s.Cells(r, 1).Value) Then
          Print #1, s.Cells(r, 2).Value, s.Cells(r, 3).Value
        Else
          Close #1
          Debug.Print "Creating " & s.Cells(r, 1).Value + ".txt"
          Open path & s.Cells(r, 1).Value + ".txt" For Output As #1
        End If
        r = r + 1
      Loop
          Close #1
        
      Kill path & "junk.txt"  ' this approach creates an extra file to simplify the code, deleting it here
      Debug.Print "Done"


End Sub

Upvotes: 2

Related Questions