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("ActiveWorkb ook.ActiveSheet.CodeName").CodeModule.AddFromStrin g _ "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" |
adding code to a workbook programmably
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" wrote in message .. . 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("ActiveWorkb ook.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" |
All times are GMT +1. The time now is 09:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com