Prev: Use Macro to Format UserForm Object
Next: FYI: MSDG not displaying posts; expects duplicates :-(
From: FatBytestard on 10 Jan 2010 21:29
On Mon, 11 Jan 2010 12:14:50 +1100, "macropod" <macropod(a)invalid.invalid>
wrote:

>macropod

What an idiot.
From: ker_01 on 11 Jan 2010 16:03
Thank you to macropod and Cellshocked for your responses.

I am now saving the file as a txt file, and have used commas to delimit the
file, and surrounded string fields with quotes so that 'accidental' commas
won't throw off the format.

When I right-click the file and say "open with Excel", it still throws each
entire row into column A.

Are there any tutorials on how to manually format a file output to be more
Excel-friendly, even (gack) if it means throwing it into XML format?

My alternative is to have Outlook create an instance of Excel, and dump the
contents directly into Excel instead of a flat file... but I'd be treading in
unfamiliar waters, given that this needs to work in a mixed 2003/2007
environment.

Thank you!!
Keith

"macropod" wrote:

> Hi ker_01,
>
> You can use a csv file by enclosing each field that might contain commas in double quotes.
>
> Applying an xls extension to a text file does not a valid Excel file make and is liable to generate an error message when Excel
> tries to open it. Plus, if you then proceed to open the file, all the data will probably be in one column.
>
> --
> Cheers
> macropod
> [Microsoft MVP - Word]
>
>
> "ker_01" <ker01(a)discussions.microsoft.com> wrote in message news:4DDBC7F0-3966-436C-80EC-54908AA3244F(a)microsoft.com...
> > I'm generating a flat file in Outlook VBA. Right now I'm able to save it as a
> > .txt file, although I hope to save it in a delimited flat file format with an
> > .xls extension so it will automatically be opened by excel without having to
> > go through the whole 'import' sequence.
> >
> > There are string fields in the file, some of which contain commas- so I
> > can't make it a comma delimited file.
> >
> > What is the best delimiter to use so that Excel will automatically recognize
> > and parse the flat file contents across columns?
> >
> > Currently in 2003, but strongly prefer solutions that will also work in 2007
> >
> > Many thanks,
> > Keith
>
> .
>
From: Archimedes' Lever on 11 Jan 2010 19:56
It is not "right click and select open with excel".


You OPEN Excel, and select the "Data" tab and select "from text"

(you could record these moves in a macro too)

Also, your file should be a .csv file, NOT a .txt file, though either
will work.

csv is "comma separated values". If you already have a pre-parsed
file, give it the pre-parsed file extension name.

Anyway, opening Excel first and performing an import is the right way,
especially if you have several fields to define.


On Mon, 11 Jan 2010 13:03:02 -0800, ker_01
<ker01(a)discussions.microsoft.com> wrote:

>Thank you to macropod and Cellshocked for your responses.
>
>I am now saving the file as a txt file, and have used commas to delimit the
>file, and surrounded string fields with quotes so that 'accidental' commas
>won't throw off the format.
>
>When I right-click the file and say "open with Excel", it still throws each
>entire row into column A.
>
>Are there any tutorials on how to manually format a file output to be more
>Excel-friendly, even (gack) if it means throwing it into XML format?
>
>My alternative is to have Outlook create an instance of Excel, and dump the
>contents directly into Excel instead of a flat file... but I'd be treading in
>unfamiliar waters, given that this needs to work in a mixed 2003/2007
>environment.
>
>Thank you!!
>Keith
>
>"macropod" wrote:
>
>> Hi ker_01,
>>
>> You can use a csv file by enclosing each field that might contain commas in double quotes.
>>
>> Applying an xls extension to a text file does not a valid Excel file make and is liable to generate an error message when Excel
>> tries to open it. Plus, if you then proceed to open the file, all the data will probably be in one column.
>>
>> --
>> Cheers
>> macropod
>> [Microsoft MVP - Word]
>>
>>
>> "ker_01" <ker01(a)discussions.microsoft.com> wrote in message news:4DDBC7F0-3966-436C-80EC-54908AA3244F(a)microsoft.com...
>> > I'm generating a flat file in Outlook VBA. Right now I'm able to save it as a
>> > .txt file, although I hope to save it in a delimited flat file format with an
>> > .xls extension so it will automatically be opened by excel without having to
>> > go through the whole 'import' sequence.
>> >
>> > There are string fields in the file, some of which contain commas- so I
>> > can't make it a comma delimited file.
>> >
>> > What is the best delimiter to use so that Excel will automatically recognize
>> > and parse the flat file contents across columns?
>> >
>> > Currently in 2003, but strongly prefer solutions that will also work in 2007
>> >
>> > Many thanks,
>> > Keith
>>
>> .
>>
From: macropod on 12 Jan 2010 20:44
I guess that's really a compliment from someone who wants to go down in history as FatBytestard!

--
Cheers
macropod
[Microsoft MVP - Word]


"FatBytestard" <FatBytestard(a)somewheronyourharddrive.org> wrote in message news:m73lk59vh4smku8pnn3pb9d84s9ip1uva0(a)4ax.com...
> On Mon, 11 Jan 2010 12:14:50 +1100, "macropod" <macropod(a)invalid.invalid>
> wrote:
>
>>macropod
>
> What an idiot.
First  |  Prev  | 
Pages: 1 2
Prev: Use Macro to Format UserForm Object
Next: FYI: MSDG not displaying posts; expects duplicates :-(