[RESOLVED] Adding code to workbook through macro

[Chrissy]

I'm writing a macro that takes a text file containing code and copies it behind sheet1 of an existing workbook. It works fine on my computer but every time I run it on someone else's computer, Excel gives a fatal error on the following line of code it is importing.... Not sure why.

Dim rsData as ADODB.Recordset

Below is my vba code and I also attached the text file.

Sub UpdateThisWorkbook()
On Error GoTo DownHere
 
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
 
Dim oFS
Dim FsFile
 
Set FsFile = CreateObject("Scripting.FileSystemObject")
Set oFS = FsFile.OpenTextFile(ThisWorkbook.Path + "\copy sheet1 code.txt")
 
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("sheet1")
Set CodeMod = VBComp.CodeModule
 
 
'This adds the ADO references.
VBProj.References.AddFromFile "C:\Program Files\Common Files\system\ado\msado15.dll"
VBProj.References.AddFromFile "C:\Program Files\Common Files\system\ado\msador15.dll"
 
 
CodeMod.DeleteLines 1, CodeMod.CountOfLines
        
With CodeMod
 
    Do Until oFS.AtEndOfStream
        LineNum = LineNum + 1
        stext = oFS.ReadLine
        
        .InsertLines LineNum, stext
    Loop
 
End With
 
Exit Sub
 
DownHere:
    MsgBox Err.Description, vbOKOnly, Format(Err.Number)
    Resume Next
 
End Sub

Any help is appreciated.

Thanks!
Chrissy

[westconn1]

they have no reference to ADO, when the project loads, so get error on that line change to
Dim rsData as object

[Chrissy]

The references are being added...

VBProj.References.AddFromFile "C:\Program Files\Common Files\system\ado\msado15.dll"
VBProj.References.AddFromFile "C:\Program Files\Common Files\system\ado\msador15.dll"

[westconn1]

i saw that, but they are not there when the project starts
also need to make sure the specific version is available on the target machine

[asmcreations]

Try to put a break on the first line of your code then do the F8 "debug". That way you can see what line it crashes.

[westconn1]

i got too many type mismatch errors, running the code to insert references

[Siddharth Rout]

Chrissy, show me your excel file...

[Chrissy]

Here is the workbook and the two files containing the code. The macro also manipulates the files too.

Maybe if i try adding the references and then saving the file before adding the code???

It's just strange that it works fine on my computer but not anyone else's.

[Chrissy]

asm... I already stepped through debugging... that is how i know it give a fatal error when importing that one line.

[Siddharth Rout]

It works fine on my computer but every time I run it on someone else's computer, Excel gives a fatal error on the following line of code it is importing.... Not sure why.

Sorry to add to your troubles but Application.Filesearch is not supported in Excel 2007...

I will test your file when I get back home where I have access to Excel 2003...

[Chrissy]

Actually I got it to work now... If I add the references, save the file, then add the macro code it works fine without an error.

Thanks for the help.

[Siddharth Rout]

But what about application.filesearch?

[Chrissy]

We don't have excel 2007 so it works fine for us right now.