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

parkie_G

New Member
Joined
Jun 24, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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:

exceldata.PNG

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

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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

Try this

VBA Code:
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

New Member
Joined
Jun 24, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Jun 24, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi and welcome to MrExcel.

Try this

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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:

VBA Code:
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

New Member
Joined
Jun 24, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
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! :giggle:

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!!

image001.png


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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try

VBA Code:
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

New Member
Joined
Jun 24, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Try

VBA Code:
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 (y)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,736
Messages
5,626,600
Members
416,194
Latest member
Dhisilva_aguiar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top