Excel Macro to Export Text *.txt file

[maxman]

I want to create a macro that outputs the value of 3 cells, in the same column, to a *.txt file. Not CSV, only text. Each row is to represent a line of text not to exceed 50 characters. The file name would be "etch_text.txt". 1, 2, or 3 lines of text will be entered in the spread sheet by an operator. If only 1 or 2 lines are input, the empty cells must not output any charaters or codes.

The application is a laser etching system that reads a *.txt file for what needs to be marked. An excel spreadsheet is used to determine the font size, what to mark, etc, and the file name of the laser system template is generated and compared to a list of files in a directory. Currently, the operator must switch to NotePAD and enter the 1, 2, or 3 lines of text to etch. The NotePAD file is saved as etch_text.txt. Then the operator returns to the EXCEL application. I would like to eliminate the need to go to NotePAD and do everything in EXCEL.

thanks in advance

Maxman

 

[dazzerj]

Hi Cindy or Didi,

Can I jump in here and ask for a little further refinement of this code, please. Or should I start a new post?

I'll press on for now.

This code works great for me too except that on my first sheet I have inserted a check box object to indicate if the text line should be output or not. If the check box is checked then the output text is copied into sheet 2.

I want to include a button to activate the macro on sheet 1 and only export the content of sheet 2.

I'll continue to search the posts, but if you get chance to reply I'd be very grateful.

Many thanks for what I already have from you.

 

[Cindy Ellis]

Hello dazzerj, and welcome to the forum!
I'm not sure I understand exactly what you need. Is your checkbox that copies text to sheet 2 already working, and you just need to modify the code so that a button-click triggers it to export the contents of Sheet 2?
Or do you need code for the checkbox(es) too?
Cindy

 

[dazzerj]

Hello Cindy,

Thanks for the reply. Your understanding is correct. I have the check box element working and needed to export the content of sheet 2 to a file.
I continued looking around the forum and came across a simple solution. My interpretation may not be the best coding ever, but the solution is based on the solution from here

http://www.mrexcel.com/forum/showthread.php?t=520071&highlight=print+file

It works perfectly. Probably not the most efficient answer because I was only testing it with 5 optional elements for export. As the file gets bigger it will become more unweildy to manage.

I'm on the wrong computer at the moment, but I might upload the code for your critique later.

Many thanks for the forum and acknowledgment

 

[alicekwong]

Have been searching for the solution to export txt file with more than 240 character per line and finally got this answer.

As from Cindy's solution, the output file is hard coded. If the filename change everytimes when the macro run , how should be the script be?


Thanks

 

[Antonio12]

Hello to all. First of all Im not sure if im supposed to post this question in a 2010 discussion but my problem is almost the same. I need the txt to come with a specific order. If I create a code like the ones above, what will happen is that the txt file will present the info one on top of the other.

If I have info in cells b2:b4, c2:c4, d2:d4 and so on, I would like the txt to present the info in the following order:

B2;C2;D2...
B3;C3;D3...

Always in order and here's the tricky part, separated by ";"

Can anyone please help?

 

[Rick Rothstein]

Hello to all. First of all Im not sure if im supposed to post this question in a 2010 discussion but my problem is almost the same. I need the txt to come with a specific order. If I create a code like the ones above, what will happen is that the txt file will present the info one on top of the other.

If I have info in cells b2:b4, c2:c4, d2:d4 and so on, I would like the txt to present the info in the following order:

B2;C2;D2...
B3;C3;D3...

Always in order and here's the tricky part, separated by ";"

Can anyone please help?

See if this macro does what you want...

Sub OutputSemiColonDelimitedData()
  Dim X As Long, LastRow As Long, FF As Long, vArr As Variant, JoinedRow() As String
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  ReDim JoinedRow(1 To Range("B2:B" & LastRow).Rows.Count)
  For X = 1 To UBound(JoinedRow)
    With WorksheetFunction
      JoinedRow(X) = .Trim(Join(.Index(Range(Cells(X + 1, 2), Cells(X, Columns.Count).End(xlToLeft)).Value, 1, 0), ";"))
    End With
  Next
  FF = FreeFile
  Open "c:\temp\SemiColonDelimitedFile.txt" For Output As #FF
  Print #FF, Join(JoinedRow, vbNewLine)
  Close #FF
End Sub

Note: Change the red highlighted text to your a path-filename that you want to output.

 

[taurean]

Maybe like:

Public Sub PrintToTextFile()
Dim iFile As Integer, j As Integer
iFile = FreeFile
Open "C:\Temp\Testfile.txt" For Output As #iFile 'Change path to suit
    For j = 2 To Range("B" & Rows.Count).End(xlUp).Row
        Print #iFile, Range("B" & j).Value & ";" & _
        Range("C" & j).Value & ";" & Range("D" & j).Value
    Next j
Close #iFile
End Sub

 

[Rick Rothstein]

Maybe like:

Public Sub PrintToTextFile()
Dim iFile As Integer, j As Integer
iFile = FreeFile
Open "C:\Temp\Testfile.txt" For Output As #iFile 'Change path to suit
    For j = 2 To Range("B" & Rows.Count).End(xlUp).Row
        Print #iFile, Range("B" & j).Value & ";" & _
        Range("C" & j).Value & ";" & Range("D" & j).Value
    Next j
Close #iFile
End Sub

I think the ... at the end of each line was supposed to indicate the data went on past Column D.

 

[Antonio12]

IT WORKED!!! Thank you all so much you saved me!

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU

Regards

Tony

 

[Rick Rothstein]

IT WORKED!!! Thank you all so much you saved me!

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU

It is not clear from your message whose procedure you are using, but I am sure I speak for taurean as well when I say... you are quite welcome, glad we could be of help to you.