PDA

View Full Version : [SOLVED] Insert VBA code in new Workbook via VBA



ilyaskazi
05-25-2005, 11:29 PM
I have created a new workbook through VB.

Now I need to insert VBA code in ThisWorkbook

In ThisWorkbook,


Sub Test()
msgbox "Tested"
End Sub

Bob Phillips
05-26-2005, 01:23 AM
:)
I have created a new workbook through VB.

Now I need to insert VBA code in ThisWorkbook

In ThisWorkbook,


Sub Test()
msgbox "Tested"
End Sub

Aah. My favourite topic - I love inserting code dynamically.



Sub CreateCode()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines .CountOfLines + 1, _
"Sub Test()" & Chr(13) & _
" Msgbox ""Tested"" " & vbNewLine & _
"End Sub"
End With
End Sub

ilyaskazi
05-26-2005, 02:35 AM
my original vba code is in PERSONAL.xls file (Startup file of excel) from which i m trying to put vba code in new open workbook.

Your code gives me an error saying..
"Programmatic access to visual basic project is not trusted"
or
"Method 'VBproject' of object'_Workbook' failed"

Bob Phillips
05-26-2005, 02:56 AM
Your code gives me an error saying..
"Programmatic access to visual basic project is not trusted

You must have Excel XP (2002) or 2003.

A new macro security setting was added to Excel 2002 that you have to "approve" in order to perform almost any VBA manipulation of objects that are part of the VBProject of a workbook.

Choose Tools/Macro/Security from the Excel menu, select the Trusted Sources tab and put a check in the checkbox called "Trust Access to Visual Basic Project".

ilyaskazi
05-26-2005, 03:19 AM
Yes, U was right.

I m using office xp-2003, and made the necessary changes.
Now it is working perfect.

thankyou and with best regards,
ilyaskazi

ilyaskazi
06-15-2005, 12:51 AM
how to erase/delete all vb codes or particular function()/sub() in targetworkbook dynamically from activeworkbook ??

ilyaskazi
06-15-2005, 02:37 AM
see my previous post and reply...plz

also... i m getting compile error while writing following codes to export through vba..


Sub WriteTempv23code() 'WRITE VB-CODE IN TEMPLATE CREATED FOR VERSION 2.3
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines .CountOfLines + 1, _
"Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _
"Dim lRowIndex As Long" & vbNewLine & _
"Dim lColIndex As Integer" & vbNewLine & _
"' RTA, OTA" & vbNewLine & _
"With Worksheets(""Rules"")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 16 To 19" & vbNewLine & _
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = ""0""" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"End If" & vbNewLine & _
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"With Worksheets(""Validity"")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 17).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 17 To 38" & vbNewLine &_
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
"' do nothing" & vbNewLine & _
"Else" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "'" & .Cells(lRowIndex, lColIndex).value" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"If .Cells(lRowIndex, 40).value <> "" Then .Cells(lRowIndex, 40).value = "'" & .Cells(lRowIndex, 40).value" & vbNewLine & _
"If .Cells(lRowIndex, 41).value <> "" Then .Cells(lRowIndex, 41).value = "'" & .Cells(lRowIndex, 41).value" & vbNewLine & _
"If .Cells(lRowIndex, 43).value <> "" Then .Cells(lRowIndex, 43).value = "'" & .Cells(lRowIndex, 43).value" & vbNewLine & _
"If .Cells(lRowIndex, 44).value <> "" Then .Cells(lRowIndex, 44).value = "'" & .Cells(lRowIndex, 44).value" & vbNewLine & _
"If .Cells(lRowIndex, 46).value <> "" Then .Cells(lRowIndex, 46).value = "'" & .Cells(lRowIndex, 46).value" & vbNewLine & _
"If .Cells(lRowIndex, 47).value <> "" Then .Cells(lRowIndex, 47).value = "'" & .Cells(lRowIndex, 47).value" & vbNewLine & _
"If .Cells(lRowIndex, 49).value <> "" Then .Cells(lRowIndex, 48).value = "'" & .Cells(lRowIndex, 49).value" & vbNewLine & _
"If .Cells(lRowIndex, 50).value <> "" Then .Cells(lRowIndex, 50).value = "'" & .Cells(lRowIndex, 50).value" & vbNewLine & _
"If .Cells(lRowIndex, 52).value <> "" Then .Cells(lRowIndex, 52).value = "'" & .Cells(lRowIndex, 52).value" & vbNewLine & _
"If .Cells(lRowIndex, 53).value <> "" Then .Cells(lRowIndex, 53).value = "'" & .Cells(lRowIndex, 53).value" & vbNewLine & _
"If .Cells(lRowIndex, 55).value <> "" Then .Cells(lRowIndex, 55).value = "'" & .Cells(lRowIndex, 55).value" & vbNewLine & _
"If .Cells(lRowIndex, 56).value <> "" Then .Cells(lRowIndex, 56).value = "'" & .Cells(lRowIndex, 56).value" & vbNewLine & _
"If .Cells(lRowIndex, 58).value <> "" Then .Cells(lRowIndex, 58).value = "'" & .Cells(lRowIndex, 58).value" & vbNewLine & _
"If .Cells(lRowIndex, 59).value <> "" Then .Cells(lRowIndex, 59).value = "'" & .Cells(lRowIndex, 59).value" & vbNewLine & _
"If .Cells(lRowIndex, 61).value <> "" Then .Cells(lRowIndex, 61).value = "'" & .Cells(lRowIndex, 61).value" & vbNewLine & _
"If .Cells(lRowIndex, 62).value <> "" Then .Cells(lRowIndex, 62).value = "'" & .Cells(lRowIndex, 62).value" & vbNewLine & _
"If .Cells(lRowIndex, 64).value <> "" Then .Cells(lRowIndex, 64).value = "'" & .Cells(lRowIndex, 64).value" & vbNewLine & _
"If .Cells(lRowIndex, 65).value <> "" Then .Cells(lRowIndex, 65).value = "'" & .Cells(lRowIndex, 65).value" & vbNewLine & _
"End If" & vbNewLine &_
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"End Sub"
End With
End Sub

Bob Phillips
06-15-2005, 04:38 AM
Do you want to start a new thread, this is a bit confused now?

ilyaskazi
06-15-2005, 04:50 AM
alright.

check: http://www.vbaexpress.com/forum/showthread.php?t=3629