PDA

View Full Version : Solved: How to Insert Code into This Workbook Mod



jdub12280
09-28-2010, 07:17 AM
Hello VBAX experts,

I have searched and tinkered with a few samples found here and there, but none seem to be working as I intend. I have a wb where I export certain worksheets over to an archive by moving a sheet to a new wb, renaming the wb, and saving in a predetermined directory. I would like to be able to insert the following code into the "ThisWorkbook" module of the NEWLY created wb before it is saved and closed.

Here is the code i use to export to the archive:

Sub MoveToArchive()
'
' MoveToArchive Macro
' Macro recorded 7/16/2010 by SanAntonio - JS1982
'
'
'Declare variables
Dim MoveWks As Worksheet
Set MoveWks = ThisWorkbook.Sheets("Sep 25") '<--this line changes daily
Dim CopyRng As Range
Set CopyRng = MoveWks.Range("A1:EZ1313")
Dim Fn As String 'new wkb File Name
Fn = "STR_09_25_10.xlsb" '<--this line changes daily
Dim Fp As String 'new wkb File Path
Fp = "\\Omitted\for\this\SalesTrackerArchive\ (file://\\Omitted\for\this\SalesTrackerArchive\)"
Dim NewWkb As Workbook 'new wkb object set after wks.move
'Dim modSource As vbcomponent
'Dim modTarget As vbcomponent


With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'kill all formula's and links by copy / paste special
'move file to new book
With MoveWks
.Select
Application.Run ("UnProtectSheet")
End With
With CopyRng
.Copy
.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
With MoveWks
.Range("E5").Select
Application.Run ("ProtectActiveSheet")
.Move
End With
Set NewWkb = ActiveWorkbook

'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
''On Error Resume Next
''loop to copy Workbook.open module to new workbook (allows outlining on pw protected sheets)
'For Each modSource In ThisWorkbook.VBProject.VBComponents
''if it is a module (vbcomponent type = 1 means it is a module)
'If modSource.Type = 1 Then
''Create a new module in new workbook
'Set modTarget = NewWkb.VBProject.VBComponents.Add(vbext_ct_StdModule)
''Rename it to original module name in source workbook
'modTarget.Name = modSource.Name
''Put all code in source module throught new module in new workbok
'modTarget.CodeModule.InsertLines 1, modSource.CodeModule.Lines(1, modSource.CodeModule.CountOfLines)
'End If
'Next
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

ChDir Fp
'save new workbook in archive
NewWkb.SaveAs FileName:=Fp & Fn _
, FileFormat:=50, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'close new workbook after save
NewWkb.Close

'restore application settings
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Here is the code that i need copied into the ThisWorkbook mod of the new wb before I close and save it.

Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic '<--MAKE SURE CALC IS ON
Dim wb As Workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ("LINKS") And ws.Visible = xlSheetVisible Then

With ws
.Protect Password:="JS1982", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
'.Cells.EntireColumn.AutoFit
.Outline.ShowLevels rowlevels:=2
.Outline.ShowLevels rowlevels:=0, columnlevels:=2
End With

End If
On Error Resume Next
Next ws
Application.ScreenUpdating = True

End Sub

Any thoughts, suggestions, corrections are greatly appreciated...

Thanks again,

Justin

Kenneth Hobs
09-28-2010, 08:02 AM
Here are 3 methods in my order of preference.
1. Copy to a master workbook copy with the code already in it.
2. Use a VBAProject method. See Chip Pearson's: http://www.cpearson.com/excel/vbe.aspx
3. Use SendKeys(). UAC must be off in Vista and Win7.

Jan Karel Pieterse
09-28-2010, 08:03 AM
Does the "source" workbook contain the same code in the thisworkbook module?

jdub12280
09-28-2010, 10:19 AM
@ Kenneth:

1. Is something that i haven't thought of... I may try to play with that. I am hoping to find a solution that would be handled in the source workbook only... if possible.

2. It looks like maybe the "Create an Event Procedure" may do what I am needing... will test.

3. I'll come back to that if 1 or 2 will not suffice.


@ Jan:

The source workbook has the same code among others. All I am wanting to copy is the Workbook Open event from the source workbook.

Thanks for both replies... I will try to test some of these suggestions to get it running, if you have any specific tid bits... feel free to chime in :)

Thanks,

Justin

jdub12280
09-28-2010, 11:38 AM
@ Kenneth:

Creating the Event Procedure seems to do what i need it to do. But i need to set a reference to the following library programatically...

Microsoft Visual Basic for Applications Extensibility 5.3

I tried inserting the following:

ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=5, Minor:=3


I get a compile error: User-defined type not defined

However, if i manually go and Check the reference, and rerun... no error is thrown.


Your thoughts?

-Justin

jdub12280
09-28-2010, 12:11 PM
Apologies all, i have stumbled upon my own error. I needed to change Thisworkbook to ActiveWorkbook... I will mark this thread as solved! Thanks Jan / Kenneth for guidance.

-Justin