Copy a Excel macro into new workbook 2

[par60056]

Is there a way using Excel 2000 to create a new workbook and copy 1 of the 2 code modules into the new workbook?

What I am trying to do is create a macro that populates a workbook with data and a macro for formatting. I don't want the macro that does this included in the workbook being sent. My other option is to put the macro I run in the personal.xls but that would mean that everybody in our company that needed to run it would need a copy.

Any other suggestions?

 

[DaleWatson123321]

par60056,

I'm always for the "easy way first". Does the following qualify ? ...

1) Copy (or Save) your original file under a new filename.

2) Delete the one module you don't want included.

Am I missing something, or can it be that simple ?

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dsi]

From the VB Editor, right click the module and select Export File. Then, in the second workbook, go to the VB Editor and select Insert > File. Select the exported .bas file and your good to go.

 

[par60056]

The thing that you are both missing is that I am trying to make this idiot proof (and we have a good breed of idiots).

I have 1 worksheet that contains all of the parameters for pulling the information from the corporate database into a worksheet, copies the data values to another worksheet, deletes the first worksheet (there by removing query and password information for the database), builds a pivot table from the resulting data.

The pivot table is what we send to the client. They can select what values they want to see and then they run another macro that formats the data "pretty" for them.

Since we are going to the effort to remove the query it would be bad to have the module with the password in it still in the workbook.

My thought is that the script will create a new workbook for the data being sent to the client. The question is how to get the script to copy the module for "pretty" formating into the new workbook.

The other option is to have the script that we run to build the pivot table reside in our users personal.xls but then you have the management nightmare of somebody having one that is out of date.

 

[DaleWatson123321]

par60056,

What about considering this option...

1) In your MAIN workbook, where you refer to "copies the data values to another worksheet", INSTEAD, extract/import (or copy/paste) this data into a SEPARATE file (the one you will be sending to the end-user).

2) In this SEPARATE file, you will have the code that builds the Pivot Table, and the macro that formats the data.

Having the TWO SEPARATE files should enable you to "sever" the connection to the query and password information.

I hope this helps. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Hi,
You might also consider putting password protection on the vba Project.

In the VBE project browser, right click the project and select VBA Project Properties - Protection Tab.

I assume that all the passwords and stuff you wat to protect is burried in the code.

Skip,
metzgsk@voughtaircraft.com

 

[par60056]

That looks like a good idea until somebody like me comes along.

The first thing that I did after locking it (and then panicking when it didn't wouldn't let me view it until I opened the VBE from a different worksheet) was to bring the workbook up in wordpad and search for ODBC. Took me right to the server name, user name and password in clear text.

 

[acron]

When you have your client sheet ready make it the active sheet and try the following :

Dim fName As String
    fName = "ModuleX.bas"
    Application.VBE.ActiveVBProject.VBComponents("ModuleX").Export fName
    ActiveSheet.Copy
    With ActiveWorkbook.VBProject.VBComponents
        .Import fName
    End With

ModuleX is the module you want to include in the client workbook. The code exports the module, creates a new workbook from the active sheet and then imports the module.

Hope that assists.

AC

 

[par60056]

Thank you. That was exactly what I was trying to find. I guess I didn't look far enough down the properties list to see how to get at the VBComponents and using the macro recorder would record anything about the export.

 

[DaleWatson123321]

par60056,

Pssssssssssst..... you neglected to "issue a STAR".

The issuing of a "STAR" is the "proper" method of showing full appreciation for the effort put forth by contributors who go out of their way to find a solution for you.

The STARS are the ONLY form of "payment" received, and it's the least one can do - especially seeing as it's not difficult - just click on the "Click here to mark this post as a helpful or expert post!" - located in the lower-left-corner of the contributor's posting.

I'm told these STARS also serve as "beacons" for other Tek-Tips "browsers" who are looking for useful solutions for similar problems or they just want to upgrade their general skills by building up a "library" of such solutions.

I hope you appreciate this "reminder". And PLEASE, do NOT issue a STAR for the "reminder", as I want to EARN stars in the normal manner. Thanks.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca