Solved

Removal of VBA Modules after copying from a template

Posted on 2016-08-11
5
33 Views
Last Modified: 2016-08-26
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
0
Comment
Question by:Rob Henson
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41753014
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?
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 0 total points
ID: 41753488
Hi Martin,

Thanks for coming back to me and apologies for the confusion.

Scenario that I am hoping to accomplish is as follows:
1) Project Manager (PM) opens Master File (possibly Read Only)
2) PM clicks a button to Create a New Project file
3) Various Input Messages for Project Name etc
4) Routine uses Inputs to create new file with standard naming convention using inputs provided
5) New file only contains the VBA Subs and Functions it requires; so no chance of PM running VBA scripts that could mess up the file, eg New File routine which would then use the Project File rather than the Master as the basis.

Steps 1 to 4 are working fine. A New File gets created as expected.

For step 5 I was looking to use the ImportModules routine you provided previously to clear out all VBA modules from the New File and then Import a previously exported Module containing only the Subs and Functions required.

The existing project files and the Master are all saved in a shared network location and the Module with the latest version of Subs and Functions is also saved there.

The PM Team hot-desk so won't necessarily be at the same desk each day so no point having a local installation of an add-in and PMs probably won't entertain something being added to their Personal.xlsb files

How does these sound as options:

Option1
Master File only contains the Subs and Functions required
Create New File routine is in a separate "Code" file and the button in Master is linked to the separate file.
Will Code File have to be open for the Create New routine to work or will clicking the button open it?
If it needs to be open then I could put a WorkbookOpen statement in the Master file but that then takes us back to the original problem of getting rid of that statement.
If clicking the button will open the code file, I need that to be without any Alerts so the PM gets a seamless process.
Routine deletes the button so I assume that will break the link to the Code file.

Option 2
When the PM needs a new file they open a "Tools" file from the shared location. This file has an option for creating a new file; that routine will then open the Master file and save it based on inputs from the Tools file.

I think I like the idea of Option 2 and only thought of it while typing this so any suggestions or pitfalls to watch out for greatly appreciated.

Thanks
Rob
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41753813
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.
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 41754407
Sounds like you have it well in hand. Let me know if you need any further help.
0
 
LVL 31

Author Closing Comment

by:Rob Henson
ID: 41771440
Resolved it on my own.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now