autofit by default

[marcky]

Hi,

I've looked everywhere but I can't find it. How do I instruct Excell to
always and automatically autofit all cells in a worksheet when I open the
file by double clicking on it??

More specifically, I have tab separated clolumns of strings in a file with a
..xls extension. The files opens ok, but all the columns have a standard width
and I want to be able to read all my data without any extra clicking to make
the columns wider. Why isn't there a Tools--> Options--> View--> 'Autofit
cells on opening' checkbox or something? Or am I looking at the wrong place?

Thanks,
Marc

[Dave Peterson]

If you save the data as a text file, then no formatting is gonna be
saved--including the columnwidths.

You could create a macro (and save it in another workbook--since that text file
can't have macros, too) and then just run that macro.

But ctrl-a (twice in xl2003) to select all the cells and doubleclicking between
a couple of columns doesn't seem like a lot of work to me.



marcky wrote:
>
> Hi,
>
> I've looked everywhere but I can't find it. How do I instruct Excell to
> always and automatically autofit all cells in a worksheet when I open the
> file by double clicking on it??
>
> More specifically, I have tab separated clolumns of strings in a file with a
> .xls extension. The files opens ok, but all the columns have a standard width
> and I want to be able to read all my data without any extra clicking to make
> the columns wider. Why isn't there a Tools--> Options--> View--> 'Autofit
> cells on opening' checkbox or something? Or am I looking at the wrong place?
>
> Thanks,
> Marc

--

Dave Peterson

[marcky]

Thanks Dave, but that's the point. It is annoying to have to start clicking
every time for something that seems so logical to be done automatically.
Suppose I would open a text file in Word and by default it would show me only
the first few letters of each word and had to click between every word to see
the whole words or something. That would be completely unacceptable but
apparently somehow this is seems accepted normal behaviour in Excel... But I
still hope I'm wrong...

Marc

"Dave Peterson" wrote:

> If you save the data as a text file, then no formatting is gonna be
> saved--including the columnwidths.
>
> You could create a macro (and save it in another workbook--since that text file
> can't have macros, too) and then just run that macro.
>
> But ctrl-a (twice in xl2003) to select all the cells and doubleclicking between
> a couple of columns doesn't seem like a lot of work to me.
>
>
>
> marcky wrote:
> >
> > Hi,
> >
> > I've looked everywhere but I can't find it. How do I instruct Excell to
> > always and automatically autofit all cells in a worksheet when I open the
> > file by double clicking on it??
> >
> > More specifically, I have tab separated clolumns of strings in a file with a
> > .xls extension. The files opens ok, but all the columns have a standard width
> > and I want to be able to read all my data without any extra clicking to make
> > the columns wider. Why isn't there a Tools--> Options--> View--> 'Autofit
> > cells on opening' checkbox or something? Or am I looking at the wrong place?
> >
> > Thanks,
> > Marc
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Ah, but there's the rub.

You may like it autofit columns--others may not.

In fact, depending on what I'm doing, I might want it one way (or the other).

I've imported text files where less than a column would show on the screen (1000
characters in each cell in that column). It could make it more confusing not
seeing multiple columns.

I guess I'm saying that the developers had to do something. You might disagree,
but it seems that they chose a reasonable way to me.



marcky wrote:
>
> Thanks Dave, but that's the point. It is annoying to have to start clicking
> every time for something that seems so logical to be done automatically.
> Suppose I would open a text file in Word and by default it would show me only
> the first few letters of each word and had to click between every word to see
> the whole words or something. That would be completely unacceptable but
> apparently somehow this is seems accepted normal behaviour in Excel... But I
> still hope I'm wrong...
>
> Marc
>
> "Dave Peterson" wrote:
>
> > If you save the data as a text file, then no formatting is gonna be
> > saved--including the columnwidths.
> >
> > You could create a macro (and save it in another workbook--since that text file
> > can't have macros, too) and then just run that macro.
> >
> > But ctrl-a (twice in xl2003) to select all the cells and doubleclicking between
> > a couple of columns doesn't seem like a lot of work to me.
> >
> >
> >
> > marcky wrote:
> > >
> > > Hi,
> > >
> > > I've looked everywhere but I can't find it. How do I instruct Excell to
> > > always and automatically autofit all cells in a worksheet when I open the
> > > file by double clicking on it??
> > >
> > > More specifically, I have tab separated clolumns of strings in a file with a
> > > .xls extension. The files opens ok, but all the columns have a standard width
> > > and I want to be able to read all my data without any extra clicking to make
> > > the columns wider. Why isn't there a Tools--> Options--> View--> 'Autofit
> > > cells on opening' checkbox or something? Or am I looking at the wrong place?
> > >
> > > Thanks,
> > > Marc
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[marcky]

Well, ok. The reasonable thing then would be to make it a view option like I
suggested in my first post.

Is there a standard startup macro in Excel that is automatically aplied to
every file that opens? If I could adapt that one, I could make it work. I
vaguely remember something like that existing in Word...

"Dave Peterson" wrote:

> Ah, but there's the rub.
>
> You may like it autofit columns--others may not.
>
> In fact, depending on what I'm doing, I might want it one way (or the other).
>
> I've imported text files where less than a column would show on the screen (1000
> characters in each cell in that column). It could make it more confusing not
> seeing multiple columns.
>
> I guess I'm saying that the developers had to do something. You might disagree,
> but it seems that they chose a reasonable way to me.
>
>
>
> marcky wrote:
> >
> > Thanks Dave, but that's the point. It is annoying to have to start clicking
> > every time for something that seems so logical to be done automatically.
> > Suppose I would open a text file in Word and by default it would show me only
> > the first few letters of each word and had to click between every word to see
> > the whole words or something. That would be completely unacceptable but
> > apparently somehow this is seems accepted normal behaviour in Excel... But I
> > still hope I'm wrong...
> >
> > Marc
> >
> > "Dave Peterson" wrote:
> >
> > > If you save the data as a text file, then no formatting is gonna be
> > > saved--including the columnwidths.
> > >
> > > You could create a macro (and save it in another workbook--since that text file
> > > can't have macros, too) and then just run that macro.
> > >
> > > But ctrl-a (twice in xl2003) to select all the cells and doubleclicking between
> > > a couple of columns doesn't seem like a lot of work to me.
> > >
> > >
> > >
> > > marcky wrote:
> > > >
> > > > Hi,
> > > >
> > > > I've looked everywhere but I can't find it. How do I instruct Excell to
> > > > always and automatically autofit all cells in a worksheet when I open the
> > > > file by double clicking on it??
> > > >
> > > > More specifically, I have tab separated clolumns of strings in a file with a
> > > > .xls extension. The files opens ok, but all the columns have a standard width
> > > > and I want to be able to read all my data without any extra clicking to make
> > > > the columns wider. Why isn't there a Tools--> Options--> View--> 'Autofit
> > > > cells on opening' checkbox or something? Or am I looking at the wrong place?
> > > >
> > > > Thanks,
> > > > Marc
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Just to be argumentative...

Would that setting be stored with the workbook--if yes, then it wouldn't affect
..txt files--they're not workbooks.

Would that setting be stored with the application--if yes, then if you turn it
on, then you'd have to turn it off before you open any workbook that shouldn't
have the columns resized. This could cause a problem if you open excel and a
workbook by double clicking on it in windows explorer.

Would that setting be stored for just certain extensions--if yes, then you can
use almost any extension for a text file, that could be a long list to cover all
possibilities--but not too unfeasible (I like arguing with myself, too!).

IIRC, MSWord has a way of doing this built in. But excel doesn't.

But you could use an event macro that looks at the extension and then does what
you want.

Create a new workbook:

Add this under the ThisWorkbook module.

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Select Case LCase(Right(Wb.Name, 4))
Case Is = ".txt", ".prn"
Wb.Worksheets(1).UsedRange.Columns.AutoFit
End Select
End Sub

Save this workbook to a nice location.
as an addin named: AutoFitTextColumns.xla
Close excel and reopen it
Tools|addins
browse to that nice location and select this addin.

Then each time you open excel, this addin will open.

If you decide you don't want it, just tools|addins|and uncheck it.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about those application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm



marcky wrote:
>
> Well, ok. The reasonable thing then would be to make it a view option like I
> suggested in my first post.
>
> Is there a standard startup macro in Excel that is automatically aplied to
> every file that opens? If I could adapt that one, I could make it work. I
> vaguely remember something like that existing in Word...
>
> "Dave Peterson" wrote:
>
> > Ah, but there's the rub.
> >
> > You may like it autofit columns--others may not.
> >
> > In fact, depending on what I'm doing, I might want it one way (or the other).
> >
> > I've imported text files where less than a column would show on the screen (1000
> > characters in each cell in that column). It could make it more confusing not
> > seeing multiple columns.
> >
> > I guess I'm saying that the developers had to do something. You might disagree,
> > but it seems that they chose a reasonable way to me.
> >
> >
> >
> > marcky wrote:
> > >
> > > Thanks Dave, but that's the point. It is annoying to have to start clicking
> > > every time for something that seems so logical to be done automatically.
> > > Suppose I would open a text file in Word and by default it would show me only
> > > the first few letters of each word and had to click between every word to see
> > > the whole words or something. That would be completely unacceptable but
> > > apparently somehow this is seems accepted normal behaviour in Excel... But I
> > > still hope I'm wrong...
> > >
> > > Marc
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > If you save the data as a text file, then no formatting is gonna be
> > > > saved--including the columnwidths.
> > > >
> > > > You could create a macro (and save it in another workbook--since that text file
> > > > can't have macros, too) and then just run that macro.
> > > >
> > > > But ctrl-a (twice in xl2003) to select all the cells and doubleclicking between
> > > > a couple of columns doesn't seem like a lot of work to me.
> > > >
> > > >
> > > >
> > > > marcky wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I've looked everywhere but I can't find it. How do I instruct Excell to
> > > > > always and automatically autofit all cells in a worksheet when I open the
> > > > > file by double clicking on it??
> > > > >
> > > > > More specifically, I have tab separated clolumns of strings in a file with a
> > > > > .xls extension. The files opens ok, but all the columns have a standard width
> > > > > and I want to be able to read all my data without any extra clicking to make
> > > > > the columns wider. Why isn't there a Tools--> Options--> View--> 'Autofit
> > > > > cells on opening' checkbox or something? Or am I looking at the wrong place?
> > > > >
> > > > > Thanks,
> > > > > Marc
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[marcky]

Excellent job Dave! Thank you! This is exactly what I wanted. Works perfectly.

I understand your point. I suppose Excel could let you create extra personal
extensions like xl1, xl2, xl2 etc with you own special settings with nicely
tickable boxes of course in stead of vba ;-).

Thanks again,
Marc

"Dave Peterson" wrote:

> Just to be argumentative...
>
> Would that setting be stored with the workbook--if yes, then it wouldn't affect
> ..txt files--they're not workbooks.
>
> Would that setting be stored with the application--if yes, then if you turn it
> on, then you'd have to turn it off before you open any workbook that shouldn't
> have the columns resized. This could cause a problem if you open excel and a
> workbook by double clicking on it in windows explorer.
>
> Would that setting be stored for just certain extensions--if yes, then you can
> use almost any extension for a text file, that could be a long list to cover all
> possibilities--but not too unfeasible (I like arguing with myself, too!).
>
> IIRC, MSWord has a way of doing this built in. But excel doesn't.
>
> But you could use an event macro that looks at the extension and then does what
> you want.
>
> Create a new workbook:
>
> Add this under the ThisWorkbook module.
>
> Option Explicit
> Public WithEvents xlApp As Excel.Application
> Private Sub Workbook_Open()
> Set xlApp = Application
> End Sub
> Private Sub Workbook_Close()
> Set xlApp = Nothing
> End Sub
> Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
> Select Case LCase(Right(Wb.Name, 4))
> Case Is = ".txt", ".prn"
> Wb.Worksheets(1).UsedRange.Columns.AutoFit
> End Select
> End Sub
>
> Save this workbook to a nice location.
> as an addin named: AutoFitTextColumns.xla
> Close excel and reopen it
> Tools|addins
> browse to that nice location and select this addin.
>
> Then each time you open excel, this addin will open.
>
> If you decide you don't want it, just tools|addins|and uncheck it.
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> You can read more about those application events at Chip Pearson's site:
> http://www.cpearson.com/excel/AppEvent.htm
>
>
>
> marcky wrote:
> >
> > Well, ok. The reasonable thing then would be to make it a view option like I
> > suggested in my first post.
> >
> > Is there a standard startup macro in Excel that is automatically aplied to
> > every file that opens? If I could adapt that one, I could make it work. I
> > vaguely remember something like that existing in Word...
> >
> > "Dave Peterson" wrote:
> >
> > > Ah, but there's the rub.
> > >
> > > You may like it autofit columns--others may not.
> > >
> > > In fact, depending on what I'm doing, I might want it one way (or the other).
> > >
> > > I've imported text files where less than a column would show on the screen (1000
> > > characters in each cell in that column). It could make it more confusing not
> > > seeing multiple columns.
> > >
> > > I guess I'm saying that the developers had to do something. You might disagree,
> > > but it seems that they chose a reasonable way to me.
> > >
> > >
> > >
> > > marcky wrote:
> > > >
> > > > Thanks Dave, but that's the point. It is annoying to have to start clicking
> > > > every time for something that seems so logical to be done automatically.
> > > > Suppose I would open a text file in Word and by default it would show me only
> > > > the first few letters of each word and had to click between every word to see
> > > > the whole words or something. That would be completely unacceptable but
> > > > apparently somehow this is seems accepted normal behaviour in Excel... But I
> > > > still hope I'm wrong...
> > > >
> > > > Marc
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > If you save the data as a text file, then no formatting is gonna be
> > > > > saved--including the columnwidths.
> > > > >
> > > > > You could create a macro (and save it in another workbook--since that text file
> > > > > can't have macros, too) and then just run that macro.
> > > > >
> > > > > But ctrl-a (twice in xl2003) to select all the cells and doubleclicking between
> > > > > a couple of columns doesn't seem like a lot of work to me.
> > > > >
> > > > >
> > > > >
> > > > > marcky wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I've looked everywhere but I can't find it. How do I instruct Excell to
> > > > > > always and automatically autofit all cells in a worksheet when I open the
> > > > > > file by double clicking on it??
> > > > > >
> > > > > > More specifically, I have tab separated clolumns of strings in a file with a
> > > > > > .xls extension. The files opens ok, but all the columns have a standard width
> > > > > > and I want to be able to read all my data without any extra clicking to make
> > > > > > the columns wider. Why isn't there a Tools--> Options--> View--> 'Autofit
> > > > > > cells on opening' checkbox or something? Or am I looking at the wrong place?
> > > > > >
> > > > > > Thanks,
> > > > > > Marc
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>