Strange effect when exporting as text file

Warzel · 02-13-2005, 06:06 AM

Hello

In Excel I'am trying to export a sheet with some simpel HTML formating
as txt file

In a sheet i have the following data: (all cells in the sheet ar
formated as text)

A1 = Content=Titel:
A2 = Data"

When i save it as text file there appear some extra qoute's,
Line 1 is good but line 2 has some extra quote's witch where not visible
in the sheet

The created text file:
Content=Titel:
"Data"

See the qoute at the beginning and end of line 2 and the extra quotes
arrond the 42, Again these where not visible in the sheet

I have created the values in A1 and A2 using the following:
Range("A1").Value =
"Content=Titel:"

Range("A2").Value ="" +
DataString + ""

Can someone explain this to me please

Ps.
Exporting as HTML is not an option as this creates a bloated HTML file
witch is not understood by flash where i want import the text file. XML
is not an option because of Office97.

**Leith Ross** · 02-13-2005, 08:12 AM

Hello Warzel,

The problem stems from Excel saving the data to a text file. Excel assumes you will at a later time import this or similar data back into Excel. Line 1 appears fine due to the Content= portion. Excel see the equal sign and interprets this data to be a Formula. Line 2 is seen as pure Text. Excel then places quotes at the start and finish of the text string. In this format both lines can be put back in to the worksheet cells without errors. You may have to open a file using VBA and copy the cells into a text file you create.

Hope this helps,
Leith Ross

Tom Ogilvy · 02-13-2005, 12:06 PM

the export message you are using is probably some type of delimited format.
Excel sees your embedded quote signs as delimiter - similar reason to why
you create them by using Quote(34) instead of directly. So excel marks them
with double double quotes to indicate a single double quote.

You can probably export as space delimited and it shouldn't put in the
double double quotes as I recall. If not, then you will need to write your
own routine to write the file (which might be better anyway - hard to tell
what you are doing).

Are you familiar with low level file io commands?

--
Regards,
Tom Ogilvy

"Warzel" <warzel@toegang.voor.iedereen.nedeland> wrote in message
news:420f2263$0$28990$e4fe514c@news.xs4all.nl...
> Hello
>
> In Excel I'am trying to export a sheet with some simpel HTML formating
> as txt file
>
> In a sheet i have the following data: (all cells in the sheet ar
> formated as text)
>
> A1 = Content=Titel:
> A2 = Data"
>
>
> When i save it as text file there appear some extra qoute's,
> Line 1 is good but line 2 has some extra quote's witch where not visible
> in the sheet
>
>
>
> The created text file:
> Content=Titel:
> "Data"
>
>
> See the qoute at the beginning and end of line 2 and the extra quotes
> arrond the 42, Again these where not visible in the sheet
>
>
>
> I have created the values in A1 and A2 using the following:
> Range("A1").Value =
> "Content=Titel:"
>
> Range("A2").Value ="" +
> DataString + ""
>
>
> Can someone explain this to me please
>
> Ps.
> Exporting as HTML is not an option as this creates a bloated HTML file
> witch is not understood by flash where i want import the text file. XML
> is not an option because of Office97.

Warzel · 02-13-2005, 02:06 PM

Tom Ogilvy wrote:
>hard to tell what you are doing.
>
I'am trying to make a user interface so one can type in data en do some
calculation's en export the results as text file with the html format,
this is loaded into a flash object in a powerpoint presentation so the
slide is automaticly updated. Got everything running except for the
quote thing. it's for a 24/7 info tv channel

> Are you familiar with low level file io commands?
>
No, please point me in the right direction

Tom Ogilvy · 02-13-2005, 02:06 PM

http://www.applecore99.com/gen/gen029.asp
File I/O Using VBA

http://support.microsoft.com/default...62&Product=xlw
Working with Sequential Access Files

You also might look at this page on David McRitchie's page which talks about
writing simplified html for an excel worksheet

http://www.mvps.org/dmcritchie/excel/xl2html.htm

--
Regards,
Tom Ogilvy

"Warzel" <warzel@toegang.voor.iedereen.nedeland> wrote in message
news:420f8a26$0$28984$e4fe514c@news.xs4all.nl...
> Tom Ogilvy wrote:
> >hard to tell what you are doing.
> >
> I'am trying to make a user interface so one can type in data en do some
> calculation's en export the results as text file with the html format,
> this is loaded into a flash object in a powerpoint presentation so the
> slide is automaticly updated. Got everything running except for the
> quote thing. it's for a 24/7 info tv channel
>
> > Are you familiar with low level file io commands?
> >
> No, please point me in the right direction

Warzel · 02-13-2005, 04:06 PM

Tom Ogilvy wrote:
> http://www.applecore99.com/gen/gen029.asp
> File I/O Using VBA
>
> http://support.microsoft.com/default...62&Product=xlw
> Working with Sequential Access Files
>
> You also might look at this page on David McRitchie's page which talks about
> writing simplified html for an excel worksheet
>
> http://www.mvps.org/dmcritchie/excel/xl2html.htm
>

Thanks for the info

Warzel · 02-13-2005, 05:06 PM

Warzel wrote:
> Hello
>
> In Excel I'am trying to export a sheet with some simpel HTML formating
> as txt file
>
> In a sheet i have the following data: (all cells in the sheet ar
> formated as text)
>
> A1 = Content=Titel:
> A2 = Data"
>
>
> When i save it as text file there appear some extra qoute's,
> Line 1 is good but line 2 has some extra quote's witch where not visible
> in the sheet
>
>
>
> The created text file:
> Content=Titel:
> "Data"
>
>
> See the qoute at the beginning and end of line 2 and the extra quotes
> arrond the 42, Again these where not visible in the sheet
>
>
>
> I have created the values in A1 and A2 using the following:
> Range("A1").Value =
> "Content=Titel:"
>
> Range("A2").Value ="" +
> DataString + ""
>
>
> Can someone explain this to me please
>
> Ps.
> Exporting as HTML is not an option as this creates a bloated HTML file
> witch is not understood by flash where i want import the text file. XML
> is not an option because of Office97.

I found that if i save the file as: FileFormat:=xlTextPrinter
the extra quote's do not appear. problem solved
Thanks to Tom Ogilvy anyway

Tom Ogilvy · 02-13-2005, 06:06 PM

Why do you say Anyway

I suggested:
>You can probably export as space delimited and it shouldn't put in the
double double quotes

Which is what you say is the solution.

--
Regards anyway.
Tom Ogilvy

"Warzel" <warzel@toegang.voor.iedereen.nedeland> wrote in message
news:420fbd56$0$28976$e4fe514c@news.xs4all.nl...
> Warzel wrote:
> > Hello
> >
> > In Excel I'am trying to export a sheet with some simpel HTML formating
> > as txt file
> >
> > In a sheet i have the following data: (all cells in the sheet ar
> > formated as text)
> >
> > A1 = Content=Titel:
> > A2 = Data"
> >
> >
> > When i save it as text file there appear some extra qoute's,
> > Line 1 is good but line 2 has some extra quote's witch where not visible
> > in the sheet
> >
> >
> >
> > The created text file:
> > Content=Titel:
> > "Data"
> >
> >
> > See the qoute at the beginning and end of line 2 and the extra quotes
> > arrond the 42, Again these where not visible in the sheet
> >
> >
> >
> > I have created the values in A1 and A2 using the following:
> > Range("A1").Value =
> > "Content=Titel:"
> >
> > Range("A2").Value ="" +
> > DataString + ""
> >
> >
> > Can someone explain this to me please
> >
> > Ps.
> > Exporting as HTML is not an option as this creates a bloated HTML file
> > witch is not understood by flash where i want import the text file. XML
> > is not an option because of Office97.
>
>
> I found that if i save the file as: FileFormat:=xlTextPrinter
> the extra quote's do not appear. problem solved
> Thanks to Tom Ogilvy anyway

Tom Ogilvy · 02-13-2005, 06:06 PM

For future reference, if you do

Save File as and select

Formatted Text (space delimited) *.prn

in VBA it translates to
Activeworkbook.SaveAs filename:=fName, fileformat:=xlTextPrinter

You can see this if you record a macro doing what I said manually:

ActiveWorkbook.SaveAs FileName:="C:\Data7\ABCD.prn", FileFormat:= _
xlTextPrinter, CreateBackup:=False

Sorry if you didn't understand this.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:e0AsuNhEFHA.624@TK2MSFTNGP09.phx.gbl...
> Why do you say Anyway
>
> I suggested:
> >You can probably export as space delimited and it shouldn't put in the
> double double quotes
>
> Which is what you say is the solution.
>
> --
> Regards anyway.
> Tom Ogilvy
>
> "Warzel" <warzel@toegang.voor.iedereen.nedeland> wrote in message
> news:420fbd56$0$28976$e4fe514c@news.xs4all.nl...
> > Warzel wrote:
> > > Hello
> > >
> > > In Excel I'am trying to export a sheet with some simpel HTML formating
> > > as txt file
> > >
> > > In a sheet i have the following data: (all cells in the sheet ar
> > > formated as text)
> > >
> > > A1 = Content=Titel:
> > > A2 = Data"
> > >
> > >
> > > When i save it as text file there appear some extra qoute's,
> > > Line 1 is good but line 2 has some extra quote's witch where not
visible
> > > in the sheet
> > >
> > >
> > >
> > > The created text file:
> > > Content=Titel:
> > > "Data"
> > >
> > >
> > > See the qoute at the beginning and end of line 2 and the extra quotes
> > > arrond the 42, Again these where not visible in the sheet
> > >
> > >
> > >
> > > I have created the values in A1 and A2 using the following:
> > > Range("A1").Value =
> > > "Content=Titel:"
> > >
> > > Range("A2").Value =""
+
> > > DataString + ""
> > >
> > >
> > > Can someone explain this to me please
> > >
> > > Ps.
> > > Exporting as HTML is not an option as this creates a bloated HTML file
> > > witch is not understood by flash where i want import the text file.
XML
> > > is not an option because of Office97.
> >
> >
> > I found that if i save the file as: FileFormat:=xlTextPrinter
> > the extra quote's do not appear. problem solved
> > Thanks to Tom Ogilvy anyway
>
>

Warzel · 02-13-2005, 07:06 PM

Tom Ogilvy wrote:
> Why do you say Anyway
>
> I suggested:
>
>>You can probably export as space delimited and it shouldn't put in the
>
> double double quotes
>
> Which is what you say is the solution.
>
i'am sorry. you are wright as you indeed suggest the space delimited
option. So i stand corrected and thank you not anyway but all the way

**ceekay** · 03-01-2005, 06:42 AM

But from my experience excel saves all worksheets to different txt-files. And that's my problem, I need to extract the text from the entire workbook into 1 txt-file

anyone ??

300bps · 03-17-2005, 08:06 PM

I found each line of output being cut off after 55 characters when I used
FileFormat:=xlTextPrinter. No idea why.

But the textstream object avoided that problem.
In particular, this code snippet (from excel help) works:
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\testfile.txt", True)
a.WriteLine("This is a test, and this is a test!")
a.Close

"Warzel" wrote:

> Tom Ogilvy wrote:
> > Why do you say Anyway
> >
> > I suggested:
> >
> >>You can probably export as space delimited and it shouldn't put in the
> >
> > double double quotes
> >
> > Which is what you say is the solution.
> >
> i'am sorry. you are wright as you indeed suggest the space delimited
> option. So i stand corrected and thank you not anyway but all the way
>

Strange effect when exporting as text file

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Re: Strange effect when exporting as text file

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions