Copying macro from word to excel
 

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

Copying macro from word to excel
 

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]


"Pflugs" wrote in message
...
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

Copying macro from word to excel
 

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" wrote:

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]


"Pflugs" wrote in message
...
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

Copying macro from word to excel
 

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" wrote in message
...
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" wrote:

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]


"Pflugs" wrote in message
...
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

Copying macro from word to excel
 

At the top of each module place this code.......

Option Private Module

Cheers
Nigel


"Pflugs" wrote:

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" wrote:

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]


"Pflugs" wrote in message
...
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

Copying macro from word to excel
 

those are the proper way.

--
Regards,
Tom Ogilvy


It is unclear what you mean by they are not in the same module

"Pflugs" wrote:

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" wrote:

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]


"Pflugs" wrote in message
...
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

Copying macro from word to excel
 

Just for Info,

Sub ToolsMacro()
MsgBox "Disabled"
End Sub

Will have no effect in an Excel module.

--
Regards,
Tom Ogilvy


"macropod" wrote:

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]


"Pflugs" wrote in message
...
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

Copying macro from word to excel
 

Noted, thanks

--
macropod
[MVP - Microsoft Word]


"Tom Ogilvy" wrote in message
...
Just for Info,

Sub ToolsMacro()
MsgBox "Disabled"
End Sub

Will have no effect in an Excel module.

--
Regards,
Tom Ogilvy


"macropod" wrote:

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]


"Pflugs" wrote in message
...
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

Copying macro from word to excel
 

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.VBC omponents
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_collap se_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" wrote:

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" wrote in message
...
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" wrote:

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]


"Pflugs" wrote in message
...
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