Link to home
Start Free TrialLog in
Avatar of Rob Henson
Rob HensonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Removal of VBA Modules after copying from a template

As a follow on question from this question:

https://www.experts-exchange.com/questions/28959590/Placement-of-a-Function.html

Martin Liss succesfully provided code adapted from Ron De Bruin to enable me to import a VBA Module into several files, in doing so getting rid of all existing modules in that file.

I have successfully used this code to implement a couple of modifications of the Function and some subsequent codes which were needed in each file.

I am now looking to use the Master file (which contains the code from Ron De Bruin) as a template for creating new project files.

I have got as far as creating the file, input or generation of file name parameters to create the variable NewFile and then:

ActiveWorkbook.SaveCopyAs NewFile  to create the new file and then re-open the NewFile because SaveCopyAs doesn't leave it open.

I am now stuck. In the file created, because it came from the Master file, it has all the VBA Modules that were written while tweaking this Master. I have copied all those that aren't needed as such in this file to my Personal.xlsb leaving the following:

Module 1
Function for Concatenating comments for onward link to a summary file (reason for Original question above)
Two Subs for inserting rows and ensuring formulas are copied down correctly

The above are all on one Module which I have also Exported for future Import using the Routine from Martin/Ron.

Module 2
The routine for creating the Copy file, as decribed above.

Module 3
Import and Export Module Routines from Martin.

With the Exported Module and the ImportModules routine I have been able to successfully update all of the existing files on a number of occasions but it is now that I am trying to apply it to a file that was created from the Master that I am having problems.

In the final Copy File I want to only have the Function/Subs of Module 1 so was thinking I could use the Import Modules routine previously used to delete all of the above from the file created and then Import the Exported Module 1 containing only the Function and Subs required.

However, when I call the ImportModules routine in my Create New File routine Excel just freezes and eventually goes to Not Responding and I have to use Task Manager to close it.

The NewFile code goes as follows:

    'Various Inputs or Calculations to get the parameters to fulfill NewFile
    NewFile = FilePath & "\" & FileDate & "-FOO_" & FY & "_" & ProjName & "_V0_1_" & ProjArea & "-OS.xlsm"
    ActiveWorkbook.SaveCopyAs NewFile
    Workbooks.Open Filename:=NewFile, UpdateLinks:=1
    ActiveSheet.Shapes.Range(Array("Bevel 3")).Delete
    Range("F5") = ProjName
    
    Call ImportModules

Open in new window

Up to Line 6 of the code works fine.

Line 5: Bevel 3 is just the button that I have put on the Master file main sheet with the "Create New" macro assigned to it, not needed in Final file so gets deleted.

Is it Freezing on the Call statement because it is confused as to which version of the ImportModules to use, there is effectively two copies open when it gets to that point, the copy in Master File and the copy in the New File which was copied from the Master. If there was ambiguity, I would have thought I would get an error message telling me that it didn't know which routine to use.

The same happens if I try and run the ImportModules routine manually when I have the NewFile reopened, making sure I select the Master File routine listed in the Run Macro window.

What is the correct syntax to include the filename with the Call statement if that is what is required?

If that is not the issue then I am stumped!!

Many thanks for all assistance.

Regards
Rob H
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

I'm confused by
I have got as far as creating the file, input or generation of file name parameters to create the variable NewFile...

Are you saying that the Master contains code that you don't want to be in the new template? If so why not delete what you don't need and save the resulting workbook under a new name and use that new workbook as the template, keeping the original Master for whatever purpose you desire?
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rob Henson

ASKER

I am currently working on Option 2 and it looks like it is going to work.

I believe I will be able to have a copy of the ImportModules routine in my "Tools" file and when there are changes to the Subs or Function, I will make the changes in the Master and Export a copy of the individual Module. I can then use the ImportModules routine in the Tools file to implement those changes across existing files.

Seems like it is all coming together.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Resolved it on my own.