Add code to existing xls using VBA...Error 1004
From https://www.experts-exchange.com/questions/22119724/Add-code-to-existing-xls-using-VBA.html
I have this solution to prapogate some code to all Excel files in a folder. It errors at:
TargetWorkbook.VBProject.V
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.V
TargetWorkbook.Close True
FileName = Dir
Loop
End Sub
Do you know how this could be corrected.
Thank you.
I have this solution to prapogate some code to all Excel files in a folder. It errors at:
TargetWorkbook.VBProject.V
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.V
TargetWorkbook.Close True
FileName = Dir
Loop
End Sub
Do you know how this could be corrected.
Thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
>"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.EnableEve
FileName = Dir(Folder & "\*.xls")
Do While FileName <> ""
Set TargetWorkbook = ExcelApplication.Workbooks
With TargetWorkbook.VBProject.V
.DeleteLines 1, .CountOfLines
.InsertLines 1, Code
End With
TargetWorkbook.Close True
FileName = Dir
Loop
ExcelApplication.Quit
End Sub
Kevin
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.EnableEve
FileName = Dir(Folder & "\*.xls")
Do While FileName <> ""
Set TargetWorkbook = ExcelApplication.Workbooks
With TargetWorkbook.VBProject.V
.DeleteLines 1, .CountOfLines
.InsertLines 1, Code
End With
TargetWorkbook.Close True
FileName = Dir
Loop
ExcelApplication.Quit
End Sub
Kevin
ASKER
Kevin,
I am speechless. It is such a pleasure to read your masterful code pieces. You may qualify as VBA Mozart.
Thank you.
Mike
I am speechless. It is such a pleasure to read your masterful code pieces. You may qualify as VBA Mozart.
Thank you.
Mike
ASKER
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
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
ASKER
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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\T
Code = "Private Sub Workbook_Open()"
Code = Code & vbCrLf & " MsgBox ""Code is here!"""
Code = Code & vbCrLf & "End Sub"
Set ExcelApplication = New Application
ExcelApplication.EnableEve
FileName = Dir(Folder & "\*.xls")
Do While FileName <> ""
Set TargetWorkbook = ExcelApplication.Workbooks
With TargetWorkbook.VBProject.V
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
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\T
Code = "Private Sub Workbook_Open()"
Code = Code & vbCrLf & " MsgBox ""Code is here!"""
Code = Code & vbCrLf & "End Sub"
Set ExcelApplication = New Application
ExcelApplication.EnableEve
FileName = Dir(Folder & "\*.xls")
Do While FileName <> ""
Set TargetWorkbook = ExcelApplication.Workbooks
With TargetWorkbook.VBProject.V
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
ASKER
Kevin,
with addition of:
Set ExcelApplication = Nothing
all is fine. I will test you latest code shortly.
Thank you,
Mike
with addition of:
Set ExcelApplication = Nothing
all is fine. I will test you latest code shortly.
Thank you,
Mike
ASKER
Glad to help, Mike, although to be honest I did little to earn the split :)
Patrick
Patrick
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