How can I automatically create .txt files from an Excel sheet?

[parkie_G]

Hi!

I'm trying to find out if there is a way to automatically create .txt files containing values which are in an Excel sheet, but also create a new .txt file each time a certain values changes (in the case, the date).
The below image shows the data that exists in the sheet:

I need the values in column B to be saved into .txt files like this, with the filename derived from column C:

The values must be grouped together by date (column A).
The text files must use the preset header, and contain the applicable data below it.

Is there a way to automatically generate and save these text files? Currently I'm having to copy and paste the values over, but this is going to be extremely time consuming in the long run.

Hopefully I've explained this well enough.

Many thanks for any help you can provide.

 

[DanteAmor]

Hi and welcome to MrExcel.

Try this

Sub Create_Txt_File()
  Dim a As Variant, dic As Object, i As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("C" & Rows.Count).End(3)).Value2
    
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      dic(a(i, 1)) = Empty
      Close #1
      Open ThisWorkbook.Path & "\" & a(i, 3) For Output As #1
      Print #1, "Data_For_Processing-2020_06"
    End If
    Print #1, a(i, 2)
  Next i
  Close #1
End Sub

 

[parkie_G]

Hi DanteAmour, thanks for your reply!

I'm a complete noob at VBA unfortunately.
I used Alt+F11, selected the workbook, right clicked Insert, Module, and pasted your code verbatim. Then hit Run, but nothing's happening.
Would you mind giving me a little more instruction please? I'm not sure I'm doing it right.

Many thanks!

 

[DanteAmor]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Create_Txt_File) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Select the sheet with the data and execute the macro.

 

[parkie_G]

Hi and welcome to MrExcel.

Try this

Sub Create_Txt_File()
  Dim a As Variant, dic As Object, i As Long

  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("C" & Rows.Count).End(3)).Value2
  
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      dic(a(i, 1)) = Empty
      Close #1
      Open ThisWorkbook.Path & "\" & a(i, 3) For Output As #1
      Print #1, "Data_For_Processing-2020_06"
    End If
    Print #1, a(i, 2)
  Next i
  Close #1
End Sub

Any futher help

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Create_Txt_File) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Select the sheet with the data and execute the macro.

Thanks again for your help, but I've followed your instructions several times, on two different versions of Excel, and each time nothing happens when I click Run.

Am I missing a step somewhere?

 

[DanteAmor]

You must run the macro on the sheet that has the data.
The data should be like your example, that is, it should start in cell A1.
The data must be filled in the 3 columns A, B and C.
Do you really have data in column C?
The files are generated in the same folder where you have the file with the macro.

Try this:

Sub Create_Txt_File()
  Dim a As Variant, dic As Object, i As Long, sName As String
 
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:C" & Range("A" & Rows.Count).End(3).Row).Value2
   
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      sName = Format(a(i, 1), "dd.mm.yy") & ".data.txt"
      dic(a(i, 1)) = Empty
      Close #1
      Open ThisWorkbook.Path & "\" & sName For Output As #1
      Print #1, "Data_For_Processing-2020_06"
    End If
    Print #1, a(i, 2)
  Next i
  Close #1
End Sub

 

[parkie_G]

You must run the macro on the sheet that has the data.
The data should be like your example, that is, it should start in cell A1.
The data must be filled in the 3 columns A, B and C.
Do you really have data in column C?
The files are generated in the same folder where you have the file with the macro.

Try this:

Sub Create_Txt_File()
  Dim a As Variant, dic As Object, i As Long, sName As String

  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:C" & Range("A" & Rows.Count).End(3).Row).Value2
  
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      sName = Format(a(i, 1), "dd.mm.yy") & ".data.txt"
      dic(a(i, 1)) = Empty
      Close #1
      Open ThisWorkbook.Path & "\" & sName For Output As #1
      Print #1, "Data_For_Processing-2020_06"
    End If
    Print #1, a(i, 2)
  Next i
  Close #1
End Sub

Hi DanteAmor,

Thanks again. I don't know where I went wrong the first time, but I tried it again and it worked perfectly!

One thing though, my data template has changed slightly so I need to tweak the VBA code to match. I had a go myself but it didn't work.
Would you mind adjusting your first VBA code to suit this new format please? I'd be very grateful. Thanks again!!

Summary of changes:

• the header of column B, "Value" has now become "Text",
• the header "Date" is now "Date Received" and it has moved from col. A to C.
• the previous column C has shifted to col. D
• there is new values in column A

Thank you

 

[DanteAmor]

Try

Sub Create_Txt_File()
  Dim a As Variant, dic As Object, i As Long, sName As String
 
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:D" & Range("C" & Rows.Count).End(3).Row).Value2
   
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 3)) Then
      sName = a(i, 4) 
      dic(a(i, 3)) = Empty
      Close #1
      Open ThisWorkbook.Path & "\" & sName For Output As #1
      Print #1, "Data_For_Processing-2020_06"
    End If
    Print #1, a(i, 2)
  Next i
  Close #1
End Sub

 

[parkie_G]

Try

Sub Create_Txt_File()
  Dim a As Variant, dic As Object, i As Long, sName As String

  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:D" & Range("C" & Rows.Count).End(3).Row).Value2
  
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 3)) Then
      sName = a(i, 4)
      dic(a(i, 3)) = Empty
      Close #1
      Open ThisWorkbook.Path & "\" & sName For Output As #1
      Print #1, "Data_For_Processing-2020_06"
    End If
    Print #1, a(i, 2)
  Next i
  Close #1
End Sub

Perfect! Thank you so much. Really appreciate it

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.