The macros that I posted will have an issue because you cannot
remove the "ThisWorkbook" module and import a new one.
What you MAY have to do is to put the macro into a "temporary" .xlsb or .xlsm
file.
then, when the .xlsx file is opened, copy the sheet to the temporary file
and close the .xlsx file.
If you planned to save and overwrite the file, you can always include a saveas
statement.
Paul
>
>From: sudheer lolla <sudheer1...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Mon, October 4, 2010 12:26:16 PM
>Subject: Re: $$Excel-Macros$$ COPY MODULES FORM LOCKED .XLSB FILE
>
>HI PAUL
>
>
>IF I KEEP A PIECE OF CODE IN "This Workbook" SAY I KEEP IN "WORKBOOK_CHANGE"
>THEN WHENEVER SHEET CHANGES THIS WILL BE INVOKED
>
>
>FOR THIS TO HAPPEN I NEED TO COPY A MODULE FROM .XLSB INTO THAT
>WORKBOOK.....THATS THE REQUIREMENT
>
>
>THIS .XLSX IS OPENED ON THE FLY...DONT KNOW WHICH XLSX USER WILL SELECT..SO
>IAM
>KEEPING A MASTER .XLSB IN "XLSTART" FOLDER IN MICROSOFT-->EXCEL-->XLSTART
>
>
>THIS MASTER .XLSB HAS A CODE THAT GENERATES A CUSTOM BUTTON.(THE CODE FOR THIS
>IS KEPT IN "WORKBOOK_OPEN" EVENT IN .XLSB FILE)
>
>
>UPON CLICK OF THIS BUTTON..SOME VALIDATION ARE DONE ON THE SHEET(THE VBA CODE
>FOR THESE VALIDATIONS ARE WRITTEN IN .XLSB FILE) AND ALSO CODE IN "THIS
>WORKBOOK" MODULE OF .XLSB IS COPIED INTO "THIS WORKBOOK" OF .XLSX FILE. AND
>ALL
>MODULES IN .XLSB FILE ARE COPIED INTO .XLSX.
>
>
>FOR THIS WHOLE THING TO HAPPEN .
>
>
>1) .XLSB IS LOCKED
>2)WORKBOOK_OPEN OF .XLSB IS CREATING CUSTOM BUTTTON EVEN WHEN ITS LOCKD
>3)VALIDATIONS ARE DONE ACCORDINGLY
>4)BUT BUT THE MODULES ARE NOT GETTING COPIED INTO DESTINATION .XLSX FILE
>
>
>IF I REMOVE PROTECTION THIS WHOLE THING IS WORKING FINE
>BUT WITH PROTECTION AM HANGING AT POINT 4)...THAT IS MODULES NOT GETTING
>COPIED..
>
>
>REGARDS
>SUDHEER
>
>
>===============================================================
>
>
>On Mon, Oct 4, 2010 at 9:20 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>Why do you want to copy the macros?
>>
>>I have one application that has over 37,000 lines of code (macros)
>>all in one protected workbook (both the workbook and VBA project are password
>>protected)
>>I run the macros which open 15 other files and operate on each one.
>>At no time do I copy modules into these files.
>>
>>what is the requirement that makes it necessary to have the macro
>>reside in the xlsx file?
>>
>>Paul
>>
>>
>>>
>>>From: sudheer lolla <sudheer1...@gmail.com>
>>>
>>>To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
>>>Sent: Mon, October 4, 2010 11:19:15 AM
>>>Subject: Re: $$Excel-Macros$$ COPY MODULES FORM LOCKED .XLSB FILE
>>>
>>>
>>>HI PAUL
>>>
>>>
>>>THANKS FOR YOUR QUICK REPLY
>>>
>>>
>>>I DON'T WANT THE FILE TO BE SAVED (THATS NOT WITHIN SCOPE WHETHER USER SAVES
>>>IT
>>>OR NOT)WITH MACROS
>>>
>>>
>>>IN ORDER FOR THE EVENTS IN THE .XLSX TO BE RUN,IAM KEEPING THE MACROS IN
>>>"This
>>>Workbook" OF THE .XLSX FILE
>>>
>>>
>>>MACROS WONT GET COPIED WHEN PROTECTION IS ON (.XLSB IS PASSWORD PROTECTED).I
>>>NEED TO UNLOCK THE XLSB COPY MACROS INTO .XLSX AND AGAIN LOCK THE .XLSB
>>>
>>>
>>>AND ONE MORE THING THIS OPERATION OF COPYING IS TO BE DONE FROM THE LOCKED
>>XLSB.
>>>
>>>
>>>AM KEEPING THE CODE IN Workbook_Open AND UPON CLICK OF THE USER COPYING HAS
>>>TO
>>>BE DONE.
>>>
>>>
>>>
>>>
>>>BUT HOW CAN I COPY FROM LOCKED XLSB FILE INTO XLSX FILE
>>>=================================================================
>>>
>>>On Mon, Oct 4, 2010 at 3:10 PM, sudheer <sudheer1...@gmail.com> wrote:
>>>
>>>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
>>>>
>>>
>>>
>>>--
>>>sudheer.L
>>>--
>>>
>>>----------------------------------------------------------------------------------
>>>
>>>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
>>
>
>
>--
>sudheer.L
>--
>----------------------------------------------------------------------------------
>
>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
