Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Durril Jansen   on Nov 15 In MS Office Category.

  
Question Answered By: Grady Stewart   on Nov 15

It utilizes the concept of exporting/importing code  modules.
First, I create  a text  file and write the cell contents to the file.
(the first line contains the new code module name)
then, I remove the module (if it exists) and import the file
as a new module.

Sub Load_Module()
Dim RowNum
Dim fso, f, ModFileName, ModName, NewMod
Dim vbaModules

NewMod = "TestMod"
ModFileName = "C:\temp\" & NewMod & ".bas"
Set fso = CreateObject("Scripting.FileSystemObject")
'---------------------------
'Write Cells to Module file
'---------------------------
If (fso.fileexists(ModFileName)) Then
fso.deletefile ModFileName
End If
Set f = fso.createTextFile(ModFileName)
f.writeline "Attribute VB_Name = ""TestMod"""
For RowNum = 1 To 1000
If Cells(RowNum, 1) = "" Then Exit For
f.writeline Cells(RowNum, 1)
Next RowNum
f.Close
'vafilename = "C:\temp\Module2.bas"
'---------------------------
' Remove Existing Module
'---------------------------
Set vbaModules = ThisWorkbook.VBProject.VBComponents

On Error Resume Next
With ActiveWorkbook.VBProject
For cnt = .VBComponents.Count To 1 Step -1
ModName = .VBComponents(cnt).Name
If (UCase(ModName) = UCase(NewMod)) Then
.VBComponents.Remove .VBComponents(cnt)
End If
Next cnt
End With
'---------------------------
' Import module
'---------------------------
VBProject.VBComponents.Import Filename:=ModFileName
End Sub

Share: 

 

This Question has 2 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Turn range values into code...anyone know how?? Or get search suggestion and latest updates.


Tagged: