Solved: Import Module via VBA code and delete existing Module with same name

[rajkumar]

Hi,

Needing your expertise in solving my problem. I have an add in which is used to import text files from folder to create reports.

They are basically an output from oracle. Whenever the report format changes i need to re write the whole macro to accommodate the changes. that's fine but i have send the whole add in to every user in my office.
Instead i want to just replace the affected report module alone.

I tried to use the code below

[vba]
Option Explicit
Sub testme()

Dim VBProj As Object 'VBIDE.VBProject
Dim myFileName As String

myFileName = "C:\Data_Analysis\Updates\test.bas"

Set VBProj = Nothing
On Error Resume Next
Set VBProj = ThisWorkbook.VBProject
On Error GoTo 0

If VBProj Is Nothing Then
MsgBox "Can't continue--I'm not trusted!"
Exit Sub
End If

VBProj.vbcomponents.Import myFileName

End Sub
[/vba]
This could import me the required module and i find the file is duplicated as test1.bas.

How do i maintain a list of module name in a column within one of the add-in's worksheets and the macro to read and compare them while importing and delete the old one and import the new one.

My VBA project is password protected. I want the code to enter password and delete the old module and import the new module and save the addin.

It will be great if some one can help me to do it

Thanks
Raj

[Sean.DiSanti]

There's a piece of code on this forum somewhere to remove all vb objects from a project (i only know that because googling that problem introduced me to this site) if you search for it, it would probably not be too tricky to tailor it to your needs.

[macropod]

Hi Raj,

That's probably going to be a whole lot more work than simply re-distributing the Add-in. And it's unlikely to produce a better outcome, since you're still going to have to get the users to run the macro that updates it.

[Bob Phillips]

... not to mention unprotecting the VBA project which requires the ultra-flakey Sendkeys.

[rajkumar]

HI Macropad, thanks and you are right. Thanks xld and Sean.DiSanti.

Raj