adding code to a workbook programmably

[Davidg]

I have an access macro that creates en excel workbook. After the the
wookbook is created I can activate it.
I need to add this code to the workbook programmably.
My thoughts are to add the code to a string?

Dim CodeString As String
CodeString = "my code here"

or do it all as s single statement below

please help what am I missing. I am sure it is a simple syntax problem


ActiveWorkbook.VBProject.VBComponents("ActiveWorkbook.ActiveSheet.CodeName").CodeModule.AddFromString
_
"Private Function LogInformation(LogMessage$)" & vbCr & _
" On Error GoTo MakeFolder" & vbCr & _
" Entry: " & vbCr & _
" Open "M:\LogFiles\" & vbCr & _
" & Left(MyWorkbook.Name, Len(MyWorkbook.Name) - 4) & " Log.Log" For
Append As #1" & vbCr & _
" Print #1, LogMessage" & vbCr & _
" Close #1" & vbCr & _
" Exit Function" & vbCr & _
" MakeFolder:" & vbCr & _
" MkDir "M:\LogFiles" & vbCr & _
" Resume Entry" & vbCr & _
" End Function" & vbCr & _
" & vbCr & _
" Private Sub Workbook_Open()" & vbCr & _
" LogInformation "Opened by " & Application.UserName &" & vbCr & _
" " " & Format(Now, "dd mmm yyyy hh:mm:ss")" & vbCr & _
" End Sub"

 

[Bob Phillips]

Well this works, but you are adding workbook open code to a sheet module,
not much point in that

Sub bob()
Dim sCode As String
Dim myWorkbook

Set myWorkbook = ThisWorkbook
sCode = "Private Function LogInformation(LogMessage$)" & vbCr & _
" On Error GoTo MakeFolder" & vbCr & _
" Entry: " & vbCr & _
" Open ""M:\LogFiles\" & _
Left(myWorkbook.Name, Len(myWorkbook.Name) - 4) & _
" Log.Log"" For Append As #1" & vbCr & _
" Print #1, LogMessage" & vbCr & _
" Close #1" & vbCr & _
" Exit Function" & vbCr & _
" MakeFolder:" & vbCr & _
" MkDir ""M:\LogFiles""" & vbCr & _
" Resume Entry" & vbCr & _
" End Function" & vbCr & vbCr & _
" Private Sub Workbook_Open()" & vbCr & _
" LogInformation ""Opened by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss") & """" & vbCr & _
" End Sub"

ActiveWorkbook.VBProject _
.VBComponents(ActiveWorkbook.ActiveSheet.CodeName) _
.CodeModule.AddFromString sCode

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)