You say in 4.)that you're copying modujles to an .xlsx file...

You cannot SAVE macros in an .xlsx file!
Microsoft created the .xlsx file format specifically so that
it would be data/formulas only.  No macros.
In order to ATTEMPT to keep malicious code from being passed
to unsuspecting users!

There are other ways to copy modules from one file to another:
  
'----------------------------------------------------------------
'This macro will export all "standard" modules to a folder
'----------------------------------------------------------------
Public Sub ExportAllMacros()
    Dim vbaModule
    Dim VBComp, tmp, Fldr
    Set vbaModule = ActiveWorkbook.VBProject.VBComponents
    Fldr = "C:\temp\modules\"
    
    Application.StatusBar = "Exporting all Modules..."
    Application.EnableEvents = False
    For Each VBComp In vbaModule
        If VBComp.Type = 1 Then
                ActiveWorkbook.VBProject.VBComponents(VBComp.Name).Export (Fldr 
& VBComp.Name & ".bas")
        End If
    Next VBComp
    Application.EnableEvents = True
    Application.StatusBar = False
End Sub 
'----------------------------------------------------------------
'This macro will import all module files in a folder into the currently active 
workbook
'----------------------------------------------------------------
Public Sub ImportAllMacros()
    Dim File, Files, f, fso, Fldr
    Dim fndMacroflag
    Set fso = CreateObject("Scripting.FileSystemObject")
    fndMacroflag = True
    Fldr = "C:\temp\modules\"
    Set f = fso.getfolder(Fldr)
    Set Files = f.Files
    For Each File In Files
        If (InStr(1, File.Name, ".bas") > 0) Then
            ActiveWorkbook.VBProject.VBComponents.Import Filename:=File.Path
            fndMacroflag = False
        End If
    Next File
    If (fndMacroflag) Then
       MsgBox "Error in loading Modules."
       Exit Sub
    End If
End Sub  
'----------------------------------------------------------------

You can modify the above macros to do a single module.
then, simply use:

Workbooks("Book2").Activate
to select the workbook you want to add the macros to.

However, even though the macro module will be copied to the new file,
if it is saved in .xlsx format, the macros will NOT be saved...

Paul

 


----- Original Message ----
> From: sudheer <sudheer1...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Mon, October 4, 2010 5:40:25 AM
> Subject: $$Excel-Macros$$ COPY MODULES FORM LOCKED .XLSB FILE
> 
> Hi
> 
> IAM IN URGENT NEED OF AN ADVISE THAT COULD HELP ME GET OUT OF THIS
> PROBLEM
> THE STEP BY STEP THAT NEEDS TO BE DONE ARE GIVEN .ONLY THING IS HOW TO
> DO IT??
> 
> 1..Running a particular code from .XLSB file
> 2..This .XLSB file is locked and i know the password also.
> 3..Code which is in this locked .XLSB file runs satisfactorily.
> 4.. When it comes to copying some modules from this locked .XLSB to
> opened XLSX file.IT    FAILS
> 
> my understainding
> 
> Since some code is running from locked .XLSB file, this copying code
> also needs to be run
> BUT THIS IS NOT HAPPENING WHY??
> 
> I TRIED ALL THESE
>--------------------------------------------------------------------------------------------------------
>-
> 
> Dim argPWD As String
> argPWD = "tcs"
> Dim VBP As VBProject
> Set VBP = ActiveWorkbook.VBProject
> MsgBox CStr(VBP.Protection)
> If VBP.Protection <> vbext_pp_locked Then
> Exit Sub
> Else
> Application.ScreenUpdating = True
> SendKeys "%{F11}%TE" & argPWD & "~~%{F11}", True
> End If
> 
> 
>---------------------------------------------------------------------------------------------------------------
>-
> 
>     With Application.VBE.ActiveVBProject
> 
>           .SendKeys "lolla"
>           .SendKeys "{ENTER}" '
> 
> 
>           .VBE.CommandBars("Menu Bar").Controls("Tools") _
>                       .Controls("VBAProject Properties...").Execute
>           .SendKeys "^{TAB}"
>           .SendKeys "{TAB}" & "lolla"
>           .SendKeys "{TAB}" & "lolla"
>           .SendKeys "{TAB}"
>           .SendKeys "{ENTER}"
> 
>     End With
> 
>----------------------------------------------------------------------------------------------------------------
>-
> 
> NONE OF THESE ARE WORKING
> 
> PLEASE COMMUNITY MATES HELP ME
> 
> -- 
>----------------------------------------------------------------------------------
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links : 
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
> 
> To post to this group, send email to excel-macros@googlegroups.com
> 
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
> 

-- 
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Reply via email to