programatically add module to xls 2003 using vbscript

Question

The first part is now working [ I have the following which just seems to hang; the part that adds/deletes the module works when running in VBA I note that I'm prompted with a dialog saying 'this workbook contains links to other data sources' which I ok to, then it hangs So I tried setting the second argument to 0 and also tried 2 but still it hangs (2nd arg is UpdateLinks as can be found here ) ]

dim objExcel
dim objWorkbook
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(  "H:\M\X\C.xls", 0 , , ,"PASSWORD!" )

Const modpath  = "H:\M\V\"
Const modtest  = "TEST.cls"
Const modname  = "TEST"

On Error Resume Next

Dim vbcomp
Set vbcomp = ActiveWorkbook.VBProject.VBComponents(modname)
objWorkbook.VBProject.VBComponents.Remove vbcomp
objWorkbook.VBProject.VBComponents.Import modpath & modtest

objWorkbook.Save
objWorkbool.Close
set vbcomp = nothing
set objworkbook = nothing
set objExcel = nothing

edited again 14/04/2009 I have now also allowed the 'tools - macro - security - vbproject access'

The script now finishes, however, when trying to open the xls to see if the changes have been made, I get a message informing me that the sheet is locked by "account used to run script"; open 'read only'/notify

Why isn't it releasing control correctly**?**

Answer 1

First thought: Does your workbook already contain a reference (within VBA) to the "Microsoft Visual Basic for Applications Extensibility" library? You'll need it to be able to talk to the VBProject object. Actually, you probably do have the reference if your code works in VBA.

Second thought: ActiveWorkbook is probably not defined outside of an actual workbook. Try replacing it with your objWorkbook object.

Answer 2

Here's a third thought. Did you try setting the Application's DisplayAlerts property to FALSE before you include the module?

Answer 3

The edited script works.

The problem was caused by the fact that I was supplying the password at the workbook level and not at the VBA project level.

A quick search on the web reveals that it is not possible to do this anyway (sendkeys etc) so after manually removing the password on the project, the problem is solved