Copy macro's, is it possible?

[Airborne]

Hello, is it possible to copy macro's from one workbook to another workbook? Goal is that the button in the new workbook can use the copied macro. I have a workbook (xlt) with a macro. I copy and save a sheet out of the workbook as a xls. In the copied sheet a button (assigned to a macro remains). The problem is that the button is there but the macro has not been copied. Is it possible to copy the macro with the sheet?


Thanks.

 

[Ekim]

In my previous post, I referred to a routine to programmatically save myTemplate.xlt as a workbook under a different name (Data1.xls). The macro will also delete all worksheets in the new file except for the one you want (assumed to a worksheet named “Data”). By default, the new file will have all the macros. Does this matter, providing “Data” is assigned to the macro in Module2? May be this routine will suffice for your purposes.

Module1:

Option Explicit
Sub openWBandRename()
Dim NewName As String
Dim wb As Workbook

NewName = "Data1"  ' xls file
Set wb = ActiveWorkbook

Application.ScreenUpdating = False

With ActiveWorkbook
Dim sh As Object
   For Each sh In Sheets
      If sh.Name <> "Data" Then
        Application.DisplayAlerts = False
          sh.Delete
      End If
   Next
   
ChDir "C:\Temp3"
wb.SaveAs "C:\Temp3\" & NewName & "_" & Format(Date, "dd-mmm-yy") & ".xls"
Application.DisplayAlerts = True
End With

Application.ScreenUpdating = False

End Sub

Module2:

Sub myFormat()
Dim myRange
Set myRange = Sheets("Data").Range("A1:H10")

With myRange
  With .Font
    .Name = "Arial"
    .Size = 12
  End With
End With
End Sub

Bloodhound.ExcelMacro

I am intrigue by this Bloodhound error message. Here’s an article from Symantec Corporation (the owners of the Norton anti-virus software):
“What to do if Norton AntiVirus detects a Bloodhound virus when opening a Microsoft Office document or spreadsheet”
http://makeashorterlink.com/?Y5E621548

It seems that amongst other things, the Bloodhound message is triggered when a macro writes code to a spreadsheet. Here’s a response to a FAQ relating to the download of software called “AnalyzerXL” http://www.analyzerxl.com/faq.htm#13

I downloaded your software and received a warning from Norton Antivirus that there is a virus called Bloodhound.ExcelMacro.

Our software is using code, which is writing Macro commands into user's spreadsheet and that part of code is understood by Norton as Bloodhound.MacroInsert virus.

We have the following note on the download page:

Note to Norton Antivirus users. Prior to installing TraderXL Pro Package, add an exclusion from Norton virus scanning for the backtestingxl.xla file to prevent false virus alerts or automatic removal of the required backtesting.xla file. This is necessary, because Norton Antivirus reacts on backtestingxl.xla file as if it was infected with a macro virus. It reacts on a part of code that writes code to VBA project when you first time open Strategy Editor or click "Add new strategy" button. All our software is tested for viruses with AVP by Kaspersky Lab

From the above response, I note the comment “….add an exclusion from Norton virus scanning for the backtestingxl.xla file to prevent false virus alerts….”.

Please advise how you get on with Norton.

Regards,

Mike

 

[tusharm]

In general, it is a rather bad idea to propagate code by duplicating it in various workbooks. How will you make corrections and/or enhancements? Instead, consider an add-in. See http://www.google.com/groups?selm=MPG.185a8ddd52ccd5ce98a88c@msnews.microsoft.com for some links.

 

[Airborne]

Thanks very much Mike for all your trouble. The new macro works for me but to keep all the macro's is not a good idea in my case bacause I have about 15 macro's in my workbook. If you don't mind I will send you the workbook and explain to you why I want to copy a few macro's. I will look in the things you wrote about Norton.
I checked out the link you showed me and when I made some changes according to what was shown on the pages it worked without Norton protesting. The only problem is that while it copies the macro, the button assigned to it keeps the link from the myTemplate workbook. I've tried something to make it work but no success. Here are the macro's.

Module 1
Sub SaveSheet()
Dim sPath As String
Dim Fname

sPath = "C:\Temp\"

Application.ScreenUpdating = False

Fname = "Data"  ' New file name
Application.DisplayAlerts = False
Worksheets("Data").Copy
ActiveWorkbook.SaveAs sPath & Fname

Call CopyOneModule2
 Call Activate
 
'ActiveWorkbook.Close SaveChanges:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Sub CopyOneModule2()
'http://www.cpearson.com/excel/vbe.htm
Dim Fname As String

With ThisWorkbook
    Fname = .Path & "\code.txt"
    .VBProject.VBComponents("Module2").Export Fname

End With
'The next line upsets Norton
'Workbooks("Data.xls").VBProject.VBComponents.Import Fname
Application.Run "imprt", Fname
Kill Fname
End Sub

Function imprt(ByVal strfile As String)
  Workbooks("Data.xls").VBProject.VBComponents.Import strfile
End Function

Sub Activate()
Windows("Data.xls").Activate
ActiveSheet.Shapes("Button 1").Select
    Selection.OnAction = "myFormat"
Range("A8").Select
End Sub


Module 2
Sub myFormat()
Dim rng As Range

Set rng = Worksheets("Data").Range("A1:H15")

With rng
  With .Font
    .Name = "Arial"
    .Size = 12
  End With
End With
End Sub

Thanks again Mike.

tusharm, I agree with you that you shouldn't spread codes by duplicating them in various workbooks because of the reasons you mention. But in my case it only concerns one workbook and one sheet that is used 3 times a day. A add-in is not convenient because the workbook will be accessed and used by numerous pc's on the network. Thanks anyway.

 

[tusharm]

{snip}
tusharm, {snip} But in my case it only concerns one workbook and one sheet that is used 3 times a day. A add-in is not convenient because the workbook will be accessed and used by numerous pc's on the network. Thanks anyway.

{shrug} That is just one more instance where an add-in is the correct solution!

 

[Ekim]

The only problem is that while it copies the macro, the button assigned to it keeps the link from the myTemplate workbook.

Yep, just realized that. When testing, I had the template file open. Naturally, the macro “worked”.

There is a very simple way of getting around the problem.

Remove the Forms button from the “Data” worksheet in myTemplate.xlt.
Replace with a Command Button from the Toolbox menu (rename the button to something more appropriate).

In the worksheet module of “Data” (myTemplate.xlt), put:

Private Sub CommandButton1_Click()
  Call myFormat
End Sub

The “myFormat” macro in Data.xls now works when the template file is closed (when the “Data” worksheet is copied, the Command Button and the associated macro, travel with the worksheet).

In fact, you could dump the entire routine of copying Module2 by putting the contents of the “myFormat” macro in the Command Button click event.

Naturally, I don’t know how Norton reacts to the above.

I adapted the following code that I saw on the Net. Apparently it works, but it doesn’t:

Sub CopyOneModule2()
'http://www.cpearson.com/excel/vbe.htm
Dim Fname As String
Dim newwb As Workbook
Dim newws As Worksheet

Set newwb = Workbooks("Data.xls")
Set newws = ActiveSheet
With ThisWorkbook
Fname = .Path & "\code.txt"
.VBProject.VBComponents("Module2").Export Fname

End With
'The next line upsets Norton
'Workbooks("Data.xls").VBProject.VBComponents.Import Fname
Application.Run "imprt", Fname
Kill Fname

newwb.newws.Shapes("Button 1").OnAction = newwb.Name & "!" & "MyFormat"
End Sub

I will keep working on the above macro. One day it may come in handy.

Regards,

Mike

 

[Airborne]

Great Mike! Seems like you did it. I first tried your idea with the commandbutton and the copying of module2. That worked and Norton was happy. Then I tried your idea of dropping the copying of module2 and putting the contents of myFormat in the commandbutton click event (which is really much easier) and that worked great. Norton did not complain.

Thanks

 

[yarahkahar]

Hello Mike and Airborne,

I found your tips really helpful but I'm still in a bit of dilemma. What I want to do is copy the macro to a new workbook but the vbprojectname is the default VBAProject, which is the same as all other excel files open.

I did a macro "transfer" where all the sheets that contains the data (including pivot tables and vlookup values) from the original file will be transferred to a new file but the "refresh" button does not copy to the new workbook (otherwise, the pivot and vlookup would still reference the original file, not the new one).

By copying the "refresh" code to the new workbook, the pivot tables and vlookup would reference the new workbook. Does this make sense?

Please help me.