The Auto_Open Macro In Personal Only Runs On One CSV In XLSTART Fo

[BB]

Hello! Have recorded a macro in Personal.XLS and copied in some csv files to
have the macro Auto Fit columns on all these.

The macro only runs on one of the csv files, not all, they all open as they
should since they are in XLStart but the macro only works on the csv that was
copied in their first.

Can move the files out and put them back in a different order and only the
first one in will have the macro process.

Not sure why it does not run for all files as the help document says it
should for any in that folder.

Currently using Excel 2003 version.

Would appreciate any help that can be provided with this functionality.

Thanks!

 

[Otto Moehrbach]

Please post your macro. HTH Otto

 

[Dave Peterson]

If you're going to keep the code in your personal.xls workbook, then you're
going to need an application event that fires each time you open a workbook.
That macro can check for the workbook's extension and do special stuff if it
sees .csv.

(Saved from a previous post.)

Put this in the personal.xls project's 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", ".csv"
Wb.Worksheets(1).UsedRange.Columns.AutoFit
End Select
End Sub

Save your personal.xls.
Close excel and reopen it. Your personal.xls workbook show open and set up this
application event.

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

 

[BB]

Hello! Thanks for the quick reply and will give that a try.

From reading the help any file in XLStart should open and run the Auto_Open
macro saved in personal.xls but will add the application event so that it
processes for each csv file.

Otto, here is the simple macro, it works on every csv in XLStart but not all
at the same time, just the one that was copied in there first.

Sub Auto_Open()
'
' Auto_Open Macro
' Macro recorded 7/21/2008
'

'
Cells.Select
Selection.ColumnWidth = 8.29
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub

Will code the application event and check how that works.

Thanks!

 

[BB]

Hello! Have copied that into the This Workbook module and saved and opened
Excel once more.

Have also kept the original Auto_Open macro in the Modules - Module1 which
may be an issue but the macro is only running for one of the two csv files in
the XLStart folder.

Have read McRichies introduction and he mentions about macros stored in
personal.xls being available to other workbooks which it is but could not
find anything about Auto_Open in his text.

Must have something set up incorrectly but not sure what yet.

Will keep trying and if you have anything else that should be checked here
please provide it when you have a chance.

Thanks!

 

[Otto Moehrbach]

BB
There is something basically wrong with what you are doing. Any
Auto-Open macro in any file will fire ONLY when that particular file is
opened, and the code in your macro will operate on only the active file and
the active file is the one that has the macro in it.
I think what you need to do is to place all of your files (that you want the
macro to work on) in some folder other than the XLStart folder. Then write
code in the macro to open each of those files and do to it what you want.
Or, leave all those files where they are but place that macro into each of
those files. HTH Otto

 

[Dave Peterson]

And CSV files are plain text--they don't support macros at all.

And your auto_open macro would try to run against the activesheet. So if you
got lucky and the CSV file was the activeworkbook, then that's why it worked.

 

[BB]

Hello! Thanks for the additional information here.

The Auto_Open macro is in personal.xls and had understood that if it was in
there and all files were in XLstart then all would have the macro applied
from the personal.xls file but apparently not.

The ten or so different csv files are created each week with new data so the
best approach is your recommendation to put these in some isolated folder on
the network and have the macro open each one and then do the Autofit for each
file.

The files are used by the end user team and trying to autofit them
automatically to save them a little time.

Have a couple more questions if you have the chance.

1) Where should the macro to open each file be placed, in personal.xls as
Auto_Open and then open each csv file and do the Autofit process

2) With the assumption that the above is correct, then the personal.xls
with the updated open macro should be copied to each end user XLstart folder
so that it would open the csv files from the network or should one copy of
the personal.xls be placed in the network folder with the csv files. That
might have some contention issues if all users access the personal.xls from
the network location.

3) Have a little VBA knowledge but do you have some sample code to possibly
open all files with the same extension, .csv, in the same folder or should
each be opened individually with the autofit macro commands run right after
each is opened as opposed to opening them all first then running the autofit
macro comands on all at the same time.

4) Would also assume that possibly a dummy spreadsheet can be placed in the
network folder that has the csv files and have the open macro in the dummy
spreadsheet or possibly copy the dummy spreadsheet to each end user XLstart
folder. Not sure which option is more efficient here.

Very much appreciate all the help so far.

Thanks!