general macro question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an external program that creates a report into excel. I execute a
report in the program and it opens excel and populates the report data (with
formatting and all).

My issue is that I have a macro I want to run against this new file that is
created - but the way the external program works it opens a new instance of
excel so any macro I have created or have open in another instance wont run
in this instance - only the other one. Is thre any way to make a macro that
is open to any instance of excel you have running ?

Thanks,
Yosef
 
Hi Yosef,

You can do this by creating a default workbook that contains the macro
you want available.

To do this you'd open up a blank workbook, create your macro, and then
save the workbook as a template (.xlt).

You want to call this "book.xlt" and save it in your XLSTART folder
which is usually somewhere like "C:\Program Files\Microsoft
Office\Office11\XLStart".

Then you can close the workbook and open a new instance of Excel. It
should have your macro available to run.

After testing that, test your other application to see if it works as
well.
 
Thanks - Ill try it.
But does tgis mean I have to copy the xlt file into anyones PC that needs
the macro ?
 
I wouldn't use a .xlt file for this.

I'd create the macro, but store it in a workbook that was saved as .xla (excel
addin).

And then I'd share that addin with anyone who needed the macro.

If possible, I think I'd put it on a network share and have the other users
install the addin via Tools|addins|browse. Then I'd only have to update one
file when something changed.

But if not all the users have access to a common network drive, you could email
the workbook (still .xla) and tell them to store it where they want.

And still use tools|addins|browse to install it.

And to give them a way to run that macro, I'd either use stuff added to the
worksheet menubar or a dedicated toolbar.

I really like the way John Walkenbach does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)
 
To do it this way you would need them to have this book.xlt file i
their XLStart folder yes.

Another way to do this is to have an open workbook that has the publi
macro in it. Any other open workbook can then execute public macro
from that open workbook.

To test this you can create a workbook with a public macro in it, the
save that workbook and re-open it to be sure macros are enabled wit
it.

Then open up a blank workbook and hit Alt-F8 to get the list of macro
you can run. As long as the "Macros In" box says "All Open Workbooks
you should see your macro from the other workbook available.

An example of a public macro is:

Public Sub TestSub()
MsgBox "Hello World"
End Sub

This could be in a module or in a worksheet. The reference to it fro
another workbook would point to the workbookname and sheet i
necesarry. You'll see this when you do the Alt-F8.

Does that help
 
Dave - I tried it your way - crated the macro in a worksheet saved it as an
xla. brought it in as an add-in but it still didnt show up ? when I go to
the macro menu option - I dont see any macro ?
What am I doing wrong ?
 
Subs in .xla's aren't visible via the Tools|macro|macros dialog.

That's why I suggested:
 
I feel like a serious novice.
I am having such problems either trying to follow the directions or excel
isnt cooperating.
I finally got it to work and the toolbar opens up when I launch excel - but
when the external program launches excel it still doesnt show up. when I go
to add-in - its checked off !
What am I doing wrong ????
 
How is your external program launching excel?

If you're automating it (createobject???), you'll have to load the addin
yourself in the code that launches excel.


I feel like a serious novice.
I am having such problems either trying to follow the directions or excel
isnt cooperating.
I finally got it to work and the toolbar opens up when I launch excel - but
when the external program launches excel it still doesnt show up. when I go
to add-in - its checked off !
What am I doing wrong ????
Click to expand...
 
I dont know hoe its launching. from within the app I generate report and it
automatically opens excel with the report there !
 
If you don't control that other program, I think you'll just have to open the
addin yourself.

Tools|Addins
or
just File|Open

ps. If you do get a chance to control that other program, not only will you
have to start excel, you'll have to load the addin and run the Auto_open
procedure.
I dont know hoe its launching. from within the app I generate report and it
automatically opens excel with the report there !
Click to expand...
 
I think you'll have to talk to the developer of that other program.

If you share the name of the application, maybe someone can jump in with more
info--is it MSWord, PowerPoint, or Access?
thanks. Is there any way to see where/how it launches excel ?
Click to expand...
 
Its a mortgage program called Encompass made by EllieMae. Ill try their
tech. support - but they arent generally too helpful !
 
If I were them, I'd be hesitant to change something that works, too.

Good luck,
Its a mortgage program called Encompass made by EllieMae. Ill try their
tech. support - but they arent generally too helpful !
Click to expand...
 
so basically my only option is to store it in another excel file and open
that file into the excel instance after my report is run ?

Thanks !!
 
That's what I'd do.

But maybe you'll get lucky with EllieMae. (That doesn't sound right!)
so basically my only option is to store it in another excel file and open
that file into the excel instance after my report is run ?

Thanks !!
Click to expand...
 
Back
Top