Mr. Pearson,
Thanks very much for your help. Coincidentally, I made a link to your Excel
help pages yesterday! I was specifically interested in "Programming to the
VBE" as I made a Word document that imports a text file, formats it, opens it
again through Excel, formats again, outlines, and copies a module from the
Word VBE into the Excel target VBE. This module contains a custom sort
command.
I had some difficulty in convincing the Word VBE to export the module. I
know you know more about Excel, but do you happen to know if Word and Excel
have similar command structure when programming to the VBE? I ask because I
followed your procedure to "Copy a Module Between Projects," and Word
wouldn't allow it. However, the procedure worked fine between 2 Excel files.
Here's what I ended up using:
' This is from the Word application
For Each Module In ActiveDocument.Application.VBE.ActiveVBProject.VBComponents
If Module.Name = "copy_collapse_functions" Then
fname = ActiveDocument.Path & "\" & Module.Name & ".txt"
Module.Export fname
Exit For
End If
Next
' This is to the Excel application
With xlApp
ActiveWorkbook.VBProject.VBComponents.import fname
ActiveWorkbook.VBProject.VBComponents("copy_collapse_functions").Name =
"collapse_functions"
' Delete text file
Set fso = CreateObject("scripting.filesystemobject")
fso.deletefile fname
end with
Again, the Excel commands you illustrated on your website worked great
WITHIN Excel, but Word seemed to have trouble with them. Thoughts?
Thanks again for the "Option Private Module" method. I knew there was a
proper way!
Pflugs
Chip Pearson said:
If you put
Option Private Module
at the top of your module, before and outside of any procedures
and declarations, the macros in that module will not show up in
the Macros dialog. You can still run the macro, but you must type
in the name in the dialog box.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Pflugs said:
I tried prefixing the subroutine with private, but because I
don't have it
stored in the same module, it tells me the routine isn't
defined. I can move
it into the same module if I have to, but I separated them into
modules by
category to make finding them easier. The dummy variable is
fine, I guess,
but I want to know if there is a proper way. Any other ideas?
Thanks,
Pflugs
macropod said:
Hi Pflugs,
You can hide the macros by prefixing their names with 'Private
', or you can
give the macro a dummy parameter, such as:
Sub MyMacro(Dummy As Boolean)
Even more brutally, you can disable macro access altogether
via:
Sub ToolsMacro()
MsgBox "Disabled"
End Sub
Cheers
--
macropod
[MVP - Microsoft Word]
I have written a series of macros and functions in Word that
transform a
very
large text file into a delimited format that can be imported
and formatted
in
Excel. Once that data has been imported and formatted
appropriately in
Excel, I want to copy a few modules of code into that
spreadsheet so that
the
user may run them when the macros are done.
Thanks to Chip Pearson, I am able to copy a full module, but
I've noticed
that the module is visible from the "Macros" dialog box. I
don't want the
user to try to run the macros meant for Excel from within
Word, but I
don't
know how to "hide" the macros so that they can't be run. My
alternative
now
is to run a function that generates a Long variable
containing the text of
the Excel macros, but I want to know if there is an easier
way.
Please let me know if clarification is needed, and thanks in
advance.
Pflugs
