Excel: Calling Foreign Procedure

[NeoPa]

I am setting up a public procedure which I plan to have available to all open workbooks. Technically, it sets the column formatting for any column in other workbooks, which matches any of the field names listed in my library workbook (which also contains the procedure). This comes about because copying data from SQL Management Studio and pasting into Excel doesn't maintain any info about the data. Access works more intuitively. If I copy in data that is textual, but contains all digits, it knows to maintain the data as text. Unfortunately, from SQL Management Studio it doesn't.

It may well be a clumsy way of going about this, but I'm looking for the syntax I would need to use to say :
I want to call procedure A from loaded workbook B (from code or even the Immediate Pane of the IDE).

I will consider all solutions of course, but I'm interested in the specific answer to the question, regardless of whether or not it is the best solution in this case.

[TheSmileyCoder]

Maybe im miss-understanding what your trying to achieve, but why not create this as an excel add-in? That way it will be available each time you open Excel (Assuming the add-in is installed on the PC)

For a quick guide on how to make an add in:
Excel Add In

You can then add it to a toolbar (to autoload) if you want.

[NeoPa]

I will certainly explore this further Smiley. I'm interested in creating Add-Ins (I've not done any yet), but I'm also interested in the structure of calling a routine from another project.

It seems (after further reading and digging in various places) that there are fundamentally two ways of calling routines within Excel :

1. Calling like a macro. This can be done by the operator directly, or by using Application.Run from code. Not much good for function procedures, but does give access to any workbook, loaded or not.
   
   The format of the macro parameter string requires the optional file name to be specified with single-quotes (') if it contains any ambiguous characters (space, dot, etc) follwed by the exclamation mark (!) then, optionally again, the name of the project (This defaults to VBAProject) and then a dot followed by the name of the procedure itself. For example, my routine would be called thusly :
   

   Call Application.Run("'U:\Excel\Code\SQLFields.Xls'!VBAProject.ColumnFormats")

   NB. If this is run infrequently it can easily be left unloaded in the normal course of events.
   If loaded, and there is only one instance of the procedure in the project (true in almost all cases) then it can be simplified to :
   

   Call Application.Run("'SQLFields.Xls'!ColumnFormats")

2. Calling the procedure directly from code and using it in a way that's very similar to calling a procedure that's available in the current project. It seems this requires a Reference to be set up before it can work. More complicated than I need for this particular situation, but worth bearing in mind for more fundamental code probably.