macro
 

If I create a macro on my PC to format a text file that is being brought over
to excel on a weekly basis (and it is saved in a file on our server), can the
macro be saved so that every week when someone else opens up a new file, the
macros can run to format that file? Sometimes I notice when you create a
macro in your workbook on your PC, it does not show in other excel
workbooks. Thanks,

I'd do this...

Start a new workbook.
record a macro when you open that file and add all your features
(formatting/sorting/filtering/page setup, etc.)

Then go back to the first worksheet and add a button from the Forms toolbar.

Assign your macro to that button.

Then save that workbook and give it to anyone who wants to import a file with
the same name and same layout.

If the name of the file could change, you could modify your recorded macro to
ask for the name with something like:

Option Explicit
Sub testme()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

You could even continue recording when you save the imported text file as a
normal workbook (*.xls) and include that in your code.



Pam Coleman wrote:

If I create a macro on my PC to format a text file that is being brought over
to excel on a weekly basis (and it is saved in a file on our server), can the
macro be saved so that every week when someone else opens up a new file, the
macros can run to format that file? Sometimes I notice when you create a
macro in your workbook on your PC, it does not show in other excel
workbooks. Thanks,

--

Dave Peterson

hi,
Macros reside in the specific file that the macro was written in. when you
create a new excel file, it does not contain macros nor can it reference
other macros unless you write a macro to call the other macro.
to do what you want and keep it simple, you would have to have a standard
import file that everyone would use to import the text file. this standard
import file would contain the format macro. then after running the format
macro, save the file off as a different name using filesaveas. the file
saveas part can be added to the format macro. to get the syntax, just do it
on record macro.

Good luck

FSt1

"Pam Coleman" wrote:

If I create a macro on my PC to format a text file that is being brought over
to excel on a weekly basis (and it is saved in a file on our server), can the
macro be saved so that every week when someone else opens up a new file, the
macros can run to format that file? Sometimes I notice when you create a
macro in your workbook on your PC, it does not show in other excel
workbooks. Thanks,

Hi Pam

Yes, you can actually do this. Create the macro as Public, not as Private.
Save the file containing the macro as an .xla file in your C:\Documents and
Settings\username\Application Data\Microsoft\Addins folder. Click on
Tools|Add Ins, and tick the name of your new add in file.

From then on, if you double click on the text file, you can start the macro,
either by pressing<Alt<F8 and typing in the name of the macro, or by adding
a custom button to a toolbar, from where you can then launch it.
--
ve_2nd_at. Randburg, Gauteng, South Africa


"Pam Coleman" wrote:

If I create a macro on my PC to format a text file that is being brought over
to excel on a weekly basis (and it is saved in a file on our server), can the
macro be saved so that every week when someone else opens up a new file, the
macros can run to format that file? Sometimes I notice when you create a
macro in your workbook on your PC, it does not show in other excel
workbooks. Thanks,

Pam

The macro can be stored in your Personal.xls or an Add-in in
order for it to be available for all your Excel Workbooks.


Gord Dibben Excel MVP


On Thu, 21 Jul 2005 08:16:04 -0700, "Pam Coleman"
wrote:

If I create a macro on my PC to format a text file that is being brought over
to excel on a weekly basis (and it is saved in a file on our server), can the
macro be saved so that every week when someone else opens up a new file, the
macros can run to format that file? Sometimes I notice when you create a
macro in your workbook on your PC, it does not show in other excel
workbooks. Thanks,

Just my opinion...

I wouldn't store this kind of think in my personal.xls workbook and I wouldn't
make it an addin either.

I'd just use a workbook that has that button that points to that macro--a
dedicated workbook.

If you make it an addin, you'll have to provide some way to run the macro
(toolbar???) and you'll either have to use tools|Addins to turn it on (or just
file|open the workbook).

If you use tools|addins, my bet is you don't need this functionality each time
you open excel.

If you use file|Open, then you'll still need to add that user interface.

And I wouldn't put it in my personal.xls, either. I like to keep that for
generic routines and this seems very specific to me.

And by separating it to a different workbook, it could be easier/safer to make
updates. And much, much easier to share with others.

Just my two cents...

Pam Coleman wrote:

If I create a macro on my PC to format a text file that is being brought over
to excel on a weekly basis (and it is saved in a file on our server), can the
macro be saved so that every week when someone else opens up a new file, the
macros can run to format that file? Sometimes I notice when you create a
macro in your workbook on your PC, it does not show in other excel
workbooks. Thanks,

--

Dave Peterson