Add code to existing xls using VBA...Error 1004

From http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_22119724.html

I have this solution to prapogate some code to all Excel files in a folder. It errors at:

TargetWorkbook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.InsertLines 99999, Code

saying: "Application-defined or object-defined error."

Here is the code:

Public Sub PropagateTheCode()
   
   Dim FileName As String
   Dim TargetWorkbook As Workbook
   Dim Code As String
   
   Const Folder = "C:\Documents and Settings\meghtebas\Desktop"
   
   Code = "Private Sub Workbook_Open()"
   Code = Code & vbCrLf & "   MsgBox ""Code is here!"""
   Code = Code & vbCrLf & "End Sub"
   
   FileName = Dir(Folder & "\*.xls")
   Do While FileName <> ""
      Set TargetWorkbook = Workbooks.Open(Folder & "\" & FileName)
      TargetWorkbook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.InsertLines 99999, Code
      TargetWorkbook.Close True
      FileName = Dir
   Loop
   
End Sub

Do you know how this could be corrected.

Thank you.

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hi Mike,

1) Make sure you set a reference to Visual Basic Extensibility

2) If Excel 2002 or later, make sure that the user has selected "trust access to VB project" under macro securiyt.

Regards,

Patrick
zorvek (Kevin Jones)ConsultantCommented:
1) Make sure you set a reference to Visual Basic Extensibility
Not needed if no objects from that library are created.

2) If Excel 2002 or later, make sure that the user has selected "trust access to VB project" under macro securiyt.
This should not be an issue since it's an external workbook with no VBA (I think).

I tried the code on my Windows XP and Windows 2000 systems running Excel 2003 (without the library reference) and the routine did not produce an error. This worked the other day - did you change something in the target workbooks? Are they read-only?

Kevin
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Patrick,

re:>  Make sure you set a reference to Visual Basic Extensibility

"Visual Basic Extensibility" option doesn't exist in the list of references.

re:> ...has selected "trust access to VB project" under macro securiyt.

I did this, it kind of worked (would explain) but colsed Excel application and did error reporting. It gave a link to learn more about the error:

http://wer.microsoft.com/responses/Response.aspx/604/en-us/5.1.2600.2.00010100.2.0?SGD=a6430742-1f3a-44e2-9821-a32b996723cf

For file ABC.xls, when I tried, after shutting doen Excel and error reporting, I reopened Excel and found ABC.xls unchanged plus ABC(version1).xls with the following code in it:

Private Sub Workbook_Open()
   MsgBox "Code is here!"
End Sub

BTW, it is Excel 2003 on Windows XP
============
Kevin,

re:> 1) Make sure you set a reference to Visual Basic Extensibility
"Visual Basic Extensibility" option doesn't exist in the list of references.

re:> Not needed if no objects from that library are created.
? I don't understand this comment.

re:> 2) If Excel 2002 or later, make sure that the user has selected "trust access to VB project" under macro securiyt.
This should not be an issue since it's an external workbook with no VBA (I think).
See my response to Patrick please.

re:> This worked the other day -
This is first time I had time to test this code.

re:> did you change something in the target workbooks?
No, I just coppied and pasted it.


re:> Are they read-only?
No.

Thank All,

Mike
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

zorvek (Kevin Jones)ConsultantCommented:
>"Visual Basic Extensibility" option doesn't exist in the list of references.
Very unlikely but possible I suppose. If you have VBA installed you should have the library. But you don't need it anyway so no worries in this department.

>? I don't understand this comment.
It was more for Patrick. Bascially, when using late binding (not defining object using specific library classes), you don't need to library references.

>This is first time I had time to test this code.
Oh...

Shoving code into VBA projects from VBA is a risky proposition to begin with. Especially when pushing code into ThisWorkbook and Sheet modules.

Try this version. It instantiates a new Excel instance and turns off events to keep any existing VBA code from compiling.

Public Sub PropagateTheCode()
   
   Dim FileName As String
   Dim ExcelApplication As Application
   Dim TargetWorkbook As Workbook
   Dim Code As String
   
   Const Folder = "C:\Documents and Settings\meghtebas\Desktop"
   
   Code = "Private Sub Workbook_Open()"
   Code = Code & vbCrLf & "   MsgBox ""Code is here!"""
   Code = Code & vbCrLf & "End Sub"
   
   Set ExcelApplication = New Application
   ExcelApplication.EnableEvents = False
   
   FileName = Dir(Folder & "\*.xls")
   Do While FileName <> ""
      Set TargetWorkbook = ExcelApplication.Workbooks.Open(Folder & "\" & FileName)
      With TargetWorkbook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule
         .DeleteLines 1, .CountOfLines
         .InsertLines 1, Code
      End With
      TargetWorkbook.Close True
      FileName = Dir
   Loop
   
   ExcelApplication.Quit
   
End Sub

Kevin
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Kevin,
 
I am speechless. It is such a pleasure to read your masterful code pieces. You may qualify as VBA Mozart.

Thank you.

Mike
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Patrick,

I tested it without your solution, I found out that it is not working. I got carried away after testing of the code from Kevin. My apology for not paying attention in grading the question; that is because you deserve your share of the points.

I hope it is okay with both of you, the wonderful experts, for me to put a request to correct this grading error (by requesting this question to be re-opened for a proper grading).

Regards,

Mike
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I temporarily deleted the above request. I am investigating the irregularity of some sort after executing Kevin's code.

In brief, after executing his code (which works wonderfully) and closing its macro; it seems some cleanup has to take place. Now, after the code, it seems opening another excel file (any file) shows for a fraction of blink and then disappears. A second try gives a message that the file is already open.

During 2004, I had to handle lots of excel files from VB 6.0 and know it sometimes could get a bit stick (at least for me knowing only little about the subject).

I will update you on the progress of my investigation.

Thank you,

Mike
zorvek (Kevin Jones)ConsultantCommented:
It seems that the Quit method does not allways work so setting the Application object to nothing is necessary to terminte the instance:

Public Sub PropagateTheCode()
   
   Dim FileName As String
   Dim ExcelApplication As Application
   Dim TargetWorkbook As Workbook
   Dim Code As String
   
   Const Folder = "d:\Documents and Settings\xx49660\Desktop\Test"
   
   Code = "Private Sub Workbook_Open()"
   Code = Code & vbCrLf & "   MsgBox ""Code is here!"""
   Code = Code & vbCrLf & "End Sub"
   
   Set ExcelApplication = New Application
   ExcelApplication.EnableEvents = False
   
   FileName = Dir(Folder & "\*.xls")
   Do While FileName <> ""
      Set TargetWorkbook = ExcelApplication.Workbooks.Open(Folder & "\" & FileName)
      With TargetWorkbook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule
         .DeleteLines 1, .CountOfLines
         .InsertLines 1, Code
      End With
      TargetWorkbook.Close True
      FileName = Dir
   Loop
   
   ExcelApplication.Quit
   Set ExcelApplication = Nothing
   
End Sub

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantCommented:
Another little tweak:

Public Sub PropagateTheCode()
   
   Dim FileName As String
   Dim ExcelApplication As Application
   Dim TargetWorkbook As Workbook
   Dim Code As String
   
   Const Folder = "d:\Documents and Settings\xx49660\Desktop\Test"
   
   Code = "Private Sub Workbook_Open()"
   Code = Code & vbCrLf & "   MsgBox ""Code is here!"""
   Code = Code & vbCrLf & "End Sub"
   
   Set ExcelApplication = New Application
   ExcelApplication.EnableEvents = False
   
   FileName = Dir(Folder & "\*.xls")
   Do While FileName <> ""
      Set TargetWorkbook = ExcelApplication.Workbooks.Open(Folder & "\" & FileName)
      With TargetWorkbook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule
         If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
         .InsertLines 1, Code
      End With
      TargetWorkbook.Close True
      FileName = Dir
   Loop
   
   ExcelApplication.Quit
   Set ExcelApplication = Nothing
   
End Sub

Kevin
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Kevin,

with addition of:

Set ExcelApplication = Nothing

all is fine. I will test you latest code shortly.

Thank you,

Mike
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Patrick MatthewsCommented:
Glad to help, Mike, although to be honest I did little to earn the split :)

Patrick
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.