I've used VBA code within EXCEL to do things like this, so I anticipate that
you could do this from ACCESS if you set the proper references to the EXCEL
objects.
Here's some sample code for doing various things with procedures in EXCEL --
note that this code is from EXCEL VBA code:
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+ FUNCTION DeleteProcedureFromModule +
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function DeleteProcedureFromModule(ByVal strWB As String, ByVal strCM
As String, _
ByVal strPN As String) As Boolean
'*** THIS FUNCTION DELETES AN ENTIRE PROCEDURE FROM THE CODE OR WORKSHEET
'*** MODULE OF A WORKBOOK. THE MODULE ITSELF IS NOT DELETED.
'*** IF THE DELETION HAS NO ERRORS, THE FUNCTION RETURNS A VALUE OF "TRUE";
'*** IF AN ERROR OCCURS, THE FUNCTION RETURNS A VALUE OF "FALSE".
'VARIABLES:
'---------
' lngNum is long variable that holds the number of lines that are in the
procedure
' that is to be deleted.
' lngStart is long variable that holds the starting line number of the
procedure
' that is to be deleted.
' strCM is the codename of the module from which the procedure is to be
deleted.
' strPN is the name of the procedure that is to be deleted.
' strWB is the name of the workbook (already opened) that contains the
module
' from which the procedure is to be deleted (filename only; not the path).
' vbCM is object variable that represents the CodeModule of the component
whose
' procedure is to be deleted.
' vbComp is object variable that represents the specific component (module)
whose
' procedure is to be deleted.
' vbProj is object variable that represents the VBProject of the workbook
whose
' procedure is to be deleted.
' xlWB is object variable that represents the workbook whose procedure is to
be
' deleted.
On Error GoTo Err_Function
Dim vbCM As CodeModule
Dim lngStart As Long, lngNum As Long
Dim vbcomp As VBComponent
Dim vbProj As VBProject
Dim xlwb As Workbook
Set xlwb = Workbooks(strWB)
Set vbProj = xlwb.VBProject
Set vbcomp = vbProj.VBComponents(strCM)
Set vbCM = vbcomp.CodeModule
With vbCM
'get line number of first line (the "sub" or "function" line) for the
' procedure
lngStart = .ProcStartLine(strPN, vbext_pk_Proc)
'get number of lines in the procedure (including the "sub" or "function"
line,
' and the "end sub" or "end function" line) for the procedure
lngNum = .ProcCountLines(strPN, vbext_pk_Proc)
'delete the procedure
.DeleteLines lngStart, lngNum
End With
DeleteProcedureFromModule = True
Exit_Function:
Set vbCM = Nothing
Set vbcomp = Nothing
Set vbProj = Nothing
Set xlwb = Nothing
Exit Function
Err_Function:
DeleteProcedureFromModule = False
Resume Exit_Function
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+ FUNCTION InsertLinesIntoModule +
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function InsertLinesIntoModule(ByVal strTxt As String, _
ByVal strWB2 As String, ByVal strCM2 As String) As Boolean
'*** THIS FUNCTION INSERTS THE ENTIRE CONTENTS OF A CODE OR WORKSHEET MODULE
FROM
'*** A TEXT FILE INTO A CODE OR WORKSHEET MODULE OF ANOTHER WORKBOOK.
'*** IF THE INSERTION HAS NO ERRORS, THE FUNCTION RETURNS A VALUE OF "TRUE";
'*** IF AN ERROR OCCURS, THE FUNCTION RETURNS A VALUE OF "FALSE".
'*** CAUTION:
'*** THIS FUNCTION CONCATENATES THE ENTIRE CODE INTO ONE STRING VARIABLE. BE
'*** CAREFUL WHEN DOING AN INSERTION OF A LARGE MODULE OR PROCEDURE, AS THAT
'*** MAY EXCEED THE STORAGE CAPACITY OF THE STRING VARIABLE!
'VARIABLES:
'---------
' lngLine is the first line onto which the code will be inserted.
' strCM2 is the codename of the module into which the code is to be
inserted.
' strCodeText is used to store all lines from the text file into one
variable.
' strTemp is used to store each line from the text file.
' strTxt is path and filename for ".txt" file into which the original
module's
' code is exported, and from which the code is imported in order to copy
the
' module into the second workbook.
' strWB2 is the name of the workbook (already opened) into which the module
' is to be inserted (filename only; not the path).
' strXFunction holds the text string " Function ".
' strXSub holds the text string " Sub ".
' vbCM2 is object variable that represents the code module of the module
into which
' the code is to be inserted.
' vbProj2 is object variable that represents the VBProject of the workbook
into
' which the code is to be inserted.
' xlWB2 is object variable that represents the workbook into which the code
is to
' be inserted.
' ynUse is a flag variable used to know when the first line of the first
procedure
' in the module's exported text is found during the import process.
Dim ynUse As Boolean
Dim lngline As Long
Dim strTemp As String, strCodeText As String
Dim vbCM2 As CodeModule
Dim vbComp2 As VBComponent
Dim vbProj2 As VBProject
Dim xlwb2 As Workbook
Const strXFunction As String = " Function "
Const strXSub As String = " Sub "
On Error GoTo Err_Function
Set xlwb2 = Workbooks(strWB2)
Set vbProj2 = xlwb2.VBProject
Set vbComp2 = vbProj2.VBComponents(strCM2)
Set vbCM2 = vbComp2.CodeModule
'import module's code from text file
strCodeText = ""
Open strTxt For Input As #1
Do While Not EOF(1)
Line Input #1, strTemp
strCodeText = strCodeText & strTemp & Chr(13)
'check to see if code string exceeds 50,000 characters
If Len(strCodeText) > 50000 Then
'insert portion of module's code into other module
' Call InsertNewCode(vbCM2, strCodeText)
' strCodeText = ""
End If
Loop
'close text file
Close #1
'insert module's code into other module
'Call InsertNewCode(vbCM2, strCodeText)
'insert code into other module (starting in line 1)
lngline = 1
vbCM2.InsertLines lngline, strCodeText
'vbCM2.InsertLines lngLine, "Private Sub TestKen()" & Chr(13) & "MsgBox
""one""" & Chr(13) & "End Sub"
InsertLinesIntoModule = True
Exit_Function:
Set vbCM2 = Nothing
Set vbComp2 = Nothing
Set vbProj2 = Nothing
Set xlwb2 = Nothing
Exit Function
Err_Function:
InsertLinesIntoModule = False
Resume Exit_Function
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+ FUNCTION InsertProcedureIntoModule +
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function InsertProcedureIntoModule(ByVal strTxt As String, _
ByVal strWB2 As String, ByVal strCM2 As String) As Boolean
'*** THIS FUNCTION INSERTS THE ENTIRE CONTENTS OF A CODE OR WORKSHEET MODULE
FROM
'*** A TEXT FILE INTO A CODE OR WORKSHEET MODULE OF ANOTHER WORKBOOK.
'*** IF THE INSERTION HAS NO ERRORS, THE FUNCTION RETURNS A VALUE OF "TRUE";
'*** IF AN ERROR OCCURS, THE FUNCTION RETURNS A VALUE OF "FALSE".
'*** CAUTION:
'*** THIS FUNCTION CONCATENATES THE ENTIRE CODE INTO ONE STRING VARIABLE. BE
'*** CAREFUL WHEN DOING AN INSERTION OF A LARGE MODULE OR PROCEDURE, AS THAT
'*** MAY EXCEED THE STORAGE CAPACITY OF THE STRING VARIABLE!
'VARIABLES:
'---------
' lngLine is the first line onto which the code will be inserted.
' strCM2 is the codename of the module into which the code is to be
inserted.
' strCodeText is used to store all lines from the text file into one
variable.
' strTemp is used to store each line from the text file.
' strTxt is path and filename for ".txt" file into which the original
module's
' code is exported, and from which the code is imported in order to copy
the
' module into the second workbook.
' strWB2 is the name of the workbook (already opened) into which the module
' is to be inserted (filename only; not the path).
' strXFunction holds the text string " Function ".
' strXSub holds the text string " Sub ".
' vbCM2 is object variable that represents the code module of the module
into which
' the code is to be inserted.
' vbProj2 is object variable that represents the VBProject of the workbook
into
' which the code is to be inserted.
' xlWB2 is object variable that represents the workbook into which the code
is to
' be inserted.
' ynUse is a flag variable used to know when the first line of the first
procedure
' in the module's exported text is found during the import process.
Dim ynUse As Boolean
Dim lngline As Long
Dim strTemp As String, strCodeText As String
Dim vbCM2 As CodeModule
Dim vbComp2 As VBComponent
Dim vbProj2 As VBProject
Dim xlwb2 As Workbook
Const strXFunction As String = " Function "
Const strXSub As String = " Sub "
On Error GoTo Err_Function
Set xlwb2 = Workbooks(strWB2)
Set vbProj2 = xlwb2.VBProject
Set vbComp2 = vbProj2.VBComponents(strCM2)
Set vbCM2 = vbComp2.CodeModule
'import module's code from text file
ynUse = False
strCodeText = ""
Open strTxt For Input As #1
Do While Not EOF(1)
Line Input #1, strTemp
If InStr(strTemp, strXSub) > 0 Or InStr(strTemp, strXFunction) > 0 Then
_
ynUse = True
If ynUse = True Then strCodeText = strCodeText & strTemp & Chr(13)
'check to see if code string exceeds 50,000 characters
If Len(strCodeText) > 50000 Then
'insert portion of module's code into other module
' Call InsertNewCode(vbCM2, strCodeText)
strCodeText = ""
End If
Loop
'close text file
Close #1
'insert module's code into other module
'Call InsertNewCode(vbCM2, strCodeText)
'insert code into other module (starting in line 1)
lngline = vbCM2.CountOfLines + 1
vbCM2.InsertLines lngline, strCodeText
'vbCM2.InsertLines lngLine, "Private Sub TestKen()" & Chr(13) & "MsgBox
""one""" & Chr(13) & "End Sub"
InsertProcedureIntoModule = True
Exit_Function:
Set vbCM2 = Nothing
Set vbComp2 = Nothing
Set vbProj2 = Nothing
Set xlwb2 = Nothing
Exit Function
Err_Function:
InsertProcedureIntoModule = False
Resume Exit_Function
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+ FUNCTION InsertNewCode +
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Function InsertNewCode(vbCM As CodeModule, ByVal strCT As String, _
Optional ByVal lngline As Long) As Boolean
'*** THIS FUNCTION INSERTS CODE INTO A MODULE, STARTING AT THE END OF THE
'*** MODULE.
'VARIABLES:
'---------
' lngLine is the first line onto which the code will be inserted.
' strCT is the code text that is to be inserted.
' vbCM is the code module of the component into which the code is to be
inserted.
On Error GoTo Err_Function
'insert module's code into other module
If lngline = 0 Then lngline = vbCM.CountOfLines + 1
vbCM.InsertLines lngline, strCT
InsertNewCode = True
Exit_Function:
Exit Function
Err_Function:
InsertNewCode = False
Resume Exit_Function
End Function
--
Ken Snell
http://www.accessmvp.com/KDSnell/