Save sheet as .txt

[Steph]

Hi. I am doing an EDI transfer into our ERP system, and need to convert a
sheet in excel into a text file. I have code to take the sheet, copy it to
another workbook, and save the file as a .txt. This works fine. When I run
the EDI transfer, I get an error. (I don't expect any of you to know why
I'm getting the error!) But here's where it gets strange - If I manually
highlight and copy the contents of the excel sheet, and past that into
notepad, and then run that .txt file through the EDI, it works!

Upon initial inspection of the 2 files, they look identical! Does anyone
know why 1 file saved as a .txt would be different in any way than a
manually created .txt file?

Overriding question - how can I automate manually copying the contents of
the excel sheet and pasting into notepad? The code I have is below.
Thanks!

Sub Saveastxt()

Dim sh As Worksheet

Set sh = Worksheets("EDI Data")
sh.Copy

'Need this line because sh.Copy truncates each cell at 256 characters
sh.Range("A1:A" & sh.Range("A65536").End(xlUp).Row).Copy
Destination:=ActiveWorkbook.Worksheets("EDI Data").Range("A1")

ActiveWorkbook.SaveAs Filename:="\\server\folder\Data" & Format(Now,
"mdyyhmm") & ".txt" ', FileFormat:=xlText
ActiveWorkbook.Close SaveChanges:=False

End Sub

 

[K.W.Martens]

Try deleting the additional blank sheets in the file....I had the same
problem and found that Excel will only allow one sheet to be saved into text
format.

 

[Dave Peterson]

I think I would get one of those Text file comparison programs.

If you search shareware.com, you'll find millions.

I bet there's a difference in your text file that's difficult to find (pretty
obvious, huh?).

(Could it be a line wrap where it didn't belong?)

If all else fails, you could write your own macro to save the data.

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

 

[Steph]

Thanks Dave. There was a difference in the files (pretty obvious, huh!).
The text file that the macro saved had 1 extra carriage return at the bottom
of the data.....Unbelievable that ONE extra live would screw things up!!
Thanks for your help!!