Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a chart to large workbook brings workbook activity to a hal | Excel Discussion (Misc queries) | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
Code in one workbook to call code in another XL file | Excel Programming | |||
Adding Code to the This_workbook module of a created workbook | Excel Programming | |||
Adding Code Module to Workbook | Excel Programming |