Prev: launch instance of excel without loading personal.xls
Next: Capture Date from External Sharepoint Portal
From: stevieb on 20 May 2010 15:59
Hello,

I am trying to export a worksheet as a *.txt file but do not want the text
quantifiers to appear (ie. the quotations around the text columns).

Here is a snippet of my saveas code:
ActiveWorkbook.SaveAs Filename:=mylocation , _
FileFormat:=xlText, CreateBackup:=False

Do I either have to change the format of the columns before I use the SaveAs
or can I mod the SaveAs function (ie. use a different paramter or try
different FileFormat's.)

Thanks,
-Steve
From: Dave Peterson on 20 May 2010 16:27
Maybe you could save the file as a .PRN file.

Or maybe you could use a macro and do exactly what you want:

On the other hand, if the data contains those double quotes, I'd use a macro:

(saved from a previous post)

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

(or maybe you could build your own formula and copy|paste into Notepad.)

Check out Earl's Text Write program first. It may do exactly what you want
right out of the box.

=========
Another way is to use a helper column and concatenate each of the fields the way
you want:

=a1&"|"&b1&"|"&text(c1,"dd-mm-yyyy")&"|"&text(d1,"0000000.000")
(where | isn't used in your data)

then drag down.

Then copy that column and paste into NotePad and save from there.

stevieb wrote:
>
> Hello,
>
> I am trying to export a worksheet as a *.txt file but do not want the text
> quantifiers to appear (ie. the quotations around the text columns).
>
> Here is a snippet of my saveas code:
> ActiveWorkbook.SaveAs Filename:=mylocation , _
> FileFormat:=xlText, CreateBackup:=False
>
> Do I either have to change the format of the columns before I use the SaveAs
> or can I mod the SaveAs function (ie. use a different paramter or try
> different FileFormat's.)
>
> Thanks,
> -Steve

--

Dave Peterson
From: CB on 21 May 2010 05:36
Very simple -
Change to: FileFormat:=xlTextPrinter
Regards
Charlie.B

> Hello,
>
> I am trying to export a worksheet as a *.txt file but do not want the text
> quantifiers to appear (ie. the quotations around the text columns).
>
> Here is a snippet of my saveas code:
> ActiveWorkbook.SaveAs Filename:=mylocation , _
> FileFormat:=xlText, CreateBackup:=False
>
> Do I either have to change the format of the columns before I use the SaveAs
> or can I mod the SaveAs function (ie. use a different paramter or try
> different FileFormat's.)
>
> Thanks,
> -Steve
From: stevieb on 21 May 2010 10:23
This does not output into one line.

"CB" wrote:

> Very simple -
> Change to: FileFormat:=xlTextPrinter
> Regards
> Charlie.B
>
> > Hello,
> >
> > I am trying to export a worksheet as a *.txt file but do not want the text
> > quantifiers to appear (ie. the quotations around the text columns).
> >
> > Here is a snippet of my saveas code:
> > ActiveWorkbook.SaveAs Filename:=mylocation , _
> > FileFormat:=xlText, CreateBackup:=False
> >
> > Do I either have to change the format of the columns before I use the SaveAs
> > or can I mod the SaveAs function (ie. use a different paramter or try
> > different FileFormat's.)
> >
> > Thanks,
> > -Steve
> .
>
From: CB on 21 May 2010 18:39
If you want the data all in one line then you need to alter the file
before saving out to a *.txt file - The output depends on what the data
source.
The xlTextPrinter will just stop the quotations around text.
Charlie.B

stevieb wrote:

> This does not output into one line.
>
> "CB" wrote:
>
>
>>Very simple -
>>Change to: FileFormat:=xlTextPrinter
>>Regards
>>Charlie.B
>>
>>
>>>Hello,
>>>
>>>I am trying to export a worksheet as a *.txt file but do not want the text
>>>quantifiers to appear (ie. the quotations around the text columns).
>>>
>>>Here is a snippet of my saveas code:
>>> ActiveWorkbook.SaveAs Filename:=mylocation , _
>>> FileFormat:=xlText, CreateBackup:=False
>>>
>>>Do I either have to change the format of the columns before I use the SaveAs
>>>or can I mod the SaveAs function (ie. use a different paramter or try
>>>different FileFormat's.)
>>>
>>>Thanks,
>>>-Steve
>>
>>.
>>
 | 
Pages: 1
Prev: launch instance of excel without loading personal.xls
Next: Capture Date from External Sharepoint Portal