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

 

[Cindy Ellis]

Just a couple of questions...
Is there anything else in the worksheet other than the text that you want to save?
Are the cells in Column A, Rows 1, 2, and 3? If not, which cells contain the data to be saved to the text file?
Do you want the text from each of the 3 cells as a separate line in the output file?
If there is only 1 cell with text, would it always be in the first row of the 3? (And by extension, if there are only 2 cells with text, would the text always be in the first 2 rows?)
If you can answer these, I think I can help you with a solution.
Cindy

 

[diddi]

there have been several txt file questions lately and we have had good results for each.

what we need is:
Where the data is on your sheet
Exactly what the output looks like

you need to show sample data with variations so we can cover the various possibilities that might arise in the data

 

[maxman]

Yes, the worksheet contains data entry and pull down menus for the operator to select and some macros to minimize errors. The input and selections of the operator generates a file name that a template for the laser is saved to. This is totally unrelated to the text I want to export. The operator can mark anything she wants. I am trying to eliminate the need for the operator to goto NotePAD, enter the text, then save the file, then go back to Excel. I want to do it all in excel.

The data can go anywhere in the file. It would look like this and the operator would enter the information. If there is only 1 line of text, line 2 and line 3 would be empty. If there is 2 lines of text, line 3 would be empty. I just want to save the lines of text to a file called "etch_text.txt". No comma separated variables, etc. The following data would be in 2 columns and 3 rows. I want to export the 2nd column as the 1st column will be prompts for the operator. Keep it simple!!!

Line 1 Text: Joe Mama's Laser Etching Services
Line 2 Text: Part Number 1234556
Line 3 Text: January 10, 2010 USA

Currently, NotePAD is used to enter:

Joe Mama's Laser Etching Service
Part Number 1234556
January 10, 2010 USA


The file is saved as "etch_text.txt". Once the file is saved, the operator goes the the laser software and updates the template. The laser template opens with the entered text from the NotePAD text file. The laser template is not related to EXCEL in anyway.

The etching on the part would look exactly like this:

Joe Mama's Laser Etching Service
Part Number 1234556
January 10, 2010 USA

Hope that makes more sense.

 

[diddi]

ok assuming text is in b1,b2,b3

Sub MakeTXT()
    open "c:|etch_text.txt" for output as #1
        print #1, cells(1,2) & chr(13) & cells(2,2) & chr(13) & cells(3,2) 
    close
end sub

 

[maxman]

Thanks a bunch. I can't wait to try it at work in the AM. I will let you know how it goes.

It is amazing how fast responses come. This is my second time to use this.

 

[diddi]

no worries, see you another time

just saw a mistake:

Sub MakeTXT()
    open "c:\etch_text.txt" for output as #1
        print #1, cells(1,2) & chr(13) & cells(2,2) & chr(13) & cells(3,2) 
    close
end sub

 

[Cindy Ellis]

Back to the original request that there not be any extraneous characters if a line is blank...you will probably want to include a test for empty cells. Building on diddi's code (and fixing a typo in the file naming string):

Sub MakeTXT()
    Open "c:[COLOR=red][B]\[/B][/COLOR]etch_text.txt" For Output As #1    'change to your directory
        Print #1, Cells(1, 2)
        If Len(Cells(2, 2)) > 0 Then Print #1, Cells(2, 2)
        If Len(Cells(3, 2)) > 0 Then Print #1, Cells(3, 2)
    Close
End Sub

Hope this helps,

Cindy

 

[diddi]

yeh i was wondering about that, but i think that its for some machine PLC that requires 3 lines of text to have a valid file structure, so i didnt include IF.

either way he can pick a solution that works.

\ not | darn shift key

 

[maxman]

Worked like a charm. Had to use the code with the if statements because the ASCII code CHR(13) appeared as a symbol in the text file and did not carriage return as expected.

Best regards to all!