Save sheet as .txt

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

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.


"Steph" wrote:

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

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 wrote:
>
> 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

--

Dave Peterson

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


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> >
> > 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
>
> --
>
> Dave Peterson