Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

writing vba code to generate vba code

  Asked By: George    Date: Oct 10    Category: MS Office    Views: 10711
  

Is it possible to write code in vba that would automatically generate additional
code and create dynamic values.

Essentially I have vba code which uses ado to insert data into a newly created
worksheet. However, i want to be able to insert code that retains the connection
information so that the user can re-establish a connection another time. The
complication is that there is possibly more than one connection required
depending on the values output from the database. Therefore on one sheet 2 , 3 ,
4 etc recordsets may have been required to generate the sheet output. My code
would need to insert vba into the sheet as it was created and also be able to
identify the cells which refer to each recordset connection.

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Ivan Coleman     Answered On: Oct 10

Yes, this is possible, but I don't recall how as I've never needed it. I
am quite certain that this question has been asked before within the
newsgroup, and if you do a search you should find the answer. Either
that or if someone else can provide. Other wise a quick search on the
internet via Google should also find what you are looking for.

 
Answer #2    Answered By: Jet Brown     Answered On: Oct 10

With all respect and without meaning to sound rude I have checked and could not
find an answer so I came to list. Any help would be greatly appreciated.

 
Answer #3    Answered By: Ludkhannah Fischer     Answered On: Oct 10

Yes, you can do this. You can access the code  on pretty much a line-by-line
basis.



You may find my TechTrax article helpful:

pubs.logicalexpressions.com/.../LPMArticle.asp



Have fun – it’s pretty trippy, to be able to change your code on the fly!

 
Answer #4    Answered By: Sairish Kauser     Answered On: Oct 10
 
Answer #5    Answered By: Javairea Akram     Answered On: Oct 10

Thanks for this. It has given me some ideas on how to progress. Nevertheless, if
there are any further contributions i would be interested to hear them.

 
Answer #6    Answered By: Laura Rodriguez     Answered On: Oct 10

I've done a lot of this and there are some shortcomings!

Reporting lines of code  is not a problem but I'm sure that the VBA
compiler/interpreter gets it's panties twisted if you try to alter the code
you are actually running. This seems to occur if you alter code in the same
module so I think that "it" probably keeps a pointer to the line of code
it's working on so that it can come back to it after it's been on it's
travels. If you insert  code above this then there are all sortsa problems!
If you insert code below then you maaaay upset the pointers to a procedure
being run at a later stage.

I've written code to sort a set of Dim statements in a procedure and that
worked pretty well soooo my thinking is that if you keep the same number of
bytes between two places then you are ok.

Best practice I've found is to alter the code from another project even.

The calls to get lines of code are pretty straight forward and reasonably
documented. You can read a block of code or go down line by line.

If you need more I may be able to find some of that - very old... sorry -
code for you, but it's a great excersize to figure it out for yourself. If
you do want the code it won't be quick 'coz I have to look back quite a
way... Again sorry.

Once I tried to write  something that would write code to run itself and then
run it!! I don't remember why now... But it didn't work. If I restarted the
application running the written procedure worked fine though.

 
Answer #7    Answered By: Eric Seibel     Answered On: Dec 21

hello
contact me at eseibel2010@gmail.com
Sub g_get_FILE_PATH()
'tools references microsoft visual basic for aplications extensibility
Application.ScreenUpdating = False
error_setup:
On Error Resume Next
Dim vbMod As Object
Set vbMod = Application.VBE.ActiveVBProject.VBComponents
vbMod.Remove VBComponent:=vbMod.Item("Module1")
If Range("a2") = "" Then
Else
Cells.Select
Selection.ClearContents
Range("a1").Select
GoTo stopat
End If
'file location
mynum = Application.InputBox(CreateObject("WScript.Shell").SpecialFolders("Desktop") & vbNewLine & CreateObject("WScript.Shell").SpecialFolders("MYDOCUMENTS") & vbNewLine & "\\corpnas\Drawings\CAD\PENDING_CN\..." & vbNewLine & "\\corpnas\drawings\in process\...", "Folder location", CreateObject("WScript.Shell").SpecialFolders("mydocuments"))
If mynum = vbCancel Then
GoTo stopat
Else
End If
If mynum = "" Then
GoTo stopat
Else
End If
If mynum = 0 Then
GoTo stopat
Else
End If
'file type
file_type:
Dim art As String
art = Application.InputBox("file extion ex." & vbNewLine & " .pdf" & vbNewLine & " .xls or xlsm" & vbNewLine & " .doc or docx" & vbNewLine & " .jpg" & vbNewLine & " etc..." & vbNewLine & " ", "file type")
If art = "" Then
ifat = MsgBox("Nothing entered, do you want to close?", vbYesNo)
If ifat = vbNo Then GoTo file_type
GoTo stopat
Else
End If
Length = Len(art)
'set values
Dim objFSO As Object
Dim objFolder As Object
'screen updating
'Application.ScreenUpdating = True 'False
'Dim objFile As Object 'REMOVE = = =
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
'Set objFolder = objFSO.GetFolder(file) '========================================
Set objFolder = objFSO.GetFolder(mynum)
i = 1
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
'For Each objFile In objFolder.mynum
Cells(i + 1, 1) = objFile.Path 'THE LAST NUMBER IN THE () IS THE COLUM TO PAST THE DATA
'Cells(i + 1, 10) = objmynum.Path
i = i + 1
Next objFile
'setup text for new macro name
Range("A1").Select
ActiveCell.FormulaR1C1 = "=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))"
Range("A2").Select
Dim now As String
now = Range("a1")
Range("A2").Select
'setup text for new macro
Range("b1") = "Option Explicit"
Range("b2") = "Declare Function apiShellExecute Lib ""shell32.dll"" Alias ""ShellExecuteA"" ( _"
Range("b3") = "ByVal hwnd As Long, _"
Range("b4") = "ByVal lpOperation As String, _"
Range("b5") = "ByVal lpFile As String, _"
Range("b6") = "ByVal lpParameters As String, _"
Range("b7") = "ByVal lpDirectory As String, _"
Range("b8") = "ByVal nShowCmd As Long) _"
Range("b9") = "As Long"
Range("b10") = "Public Sub PrintFile(ByVal strPathAndFilename As String)"
Range("b11") = "Call apiShellExecute(Application.hwnd, ""print"", strPathAndFilename, vbNullString, vbNullString, 0)"
Range("b12") = "End Sub"
Range("b13") = "Sub " & now & "()"
'get only sufffex
Dim a As String
Dim b As String
Dim prefex As String
Dim suffex As String
Dim coppy As String
prefex = "printfile ("""
suffex = """)"
a = 1
b = 13
Range("a2").Select
suffex:
If ActiveCell.Value = "" Then
GoTo addend
Else
End If
If Right(ActiveCell.Value, Length) = art Then
coppy = ActiveCell
Range("b1").Select
ActiveCell.Offset(b, 0).Activate
ActiveCell = prefex & coppy & suffex
Range("a2").Select
ActiveCell.Offset(a, 0).Activate
a = a + 1
b = b + 1
Else
Range("a2").Select
ActiveCell.Offset(a, 0).Activate
a = a + 1
End If
GoTo suffex
addend:
Range("b1").Select
ActiveCell.Offset(b, 0).Activate
ActiveCell.Value = "beep"
b = b + 1
Range("b1").Select
ActiveCell.Offset(b, 0).Activate
ActiveCell.Value = "end sub"
Range("a1").Select
'add macro
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim S As String
Dim LineNum As Long
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.name = "Module1" 'new name
Set CodeMod = VBComp.CodeModule
LineNum = CodeMod.CountOfLines + 1
Dim offsetby As String
offsetby = 1
Range("b1").Select
S = ActiveCell.Value
body:
Range("b1").Select
ActiveCell.Offset(offsetby, 0).Activate
If ActiveCell.Value = "" Then
GoTo bodystop
Else
S = S & vbCrLf & ActiveCell.Value
offsetby = offsetby + 1
GoTo body:
End If
bodystop:
CodeMod.InsertLines LineNum, S
Application.Run "'" & ActiveWorkbook.name & "'" & "!" & now
GoTo stopat
error_msg:
MsgBox ("contact system admisister")
stopat:
Cells.Select
Selection.ClearContents
Range("a1").Select
Application.ScreenUpdating = True
End Sub

 
Didn't find what you were looking for? Find more on writing vba code to generate vba code Or get search suggestion and latest updates.




Tagged: