adding code to a workbook programmably

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"

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)

"Davidg" <[email protected]> wrote in message
news:[email protected]...
> 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"
>
>
>