EX2007 - Error 1004: method 'VBProject' of object '_Workbook' failed

[Marcin]

Hi,

I have serious problem with accessing VBProject of another Excel workbook.
I need to play with VBComponents and check CodeNames of all sheets.
Always when code refers to VBProject of another workbook error 1004 mentioned in subject of this post is generated.

Notes:
- “Trust access to the VBA project object model” is set on in Trust Center / Macro setting
- reference to “Microsoft Visual Basic for Application Extensibility 5.3” is added
- opened file is located in network drive

Below there is part of code in VBA.
Do you know what can be the reason of that problem?

Private Sub Test()

Dim MyFiles As Variant
Dim Fnum As Byte
Dim mybook As Workbook
Dim mybookProject As VBIDE.VBProject
Dim VBC As VBIDE.VBComponent

MyFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=True)

If IsArray(MyFiles) Then

For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Application.Workbooks.Open(MyFiles(Fnum), , True)

‘***** next line an error occurs ******
Set mybookProject = mybook.VBProject
For Each VBC In mybookProject.VBComponents
If VBC.Type = 100 Then
...
...

Kind regards,
Marcin

 

[GS]

Change the type for your VBC variable to Variant. Also, ditch
mybookProject (which is what VBA objects to) and go with...

For Each VBC In mybook.VBProject.VBComponents

 

[GS]

<BTW>
You don't need to access the VBProject to get the sheet codenames. You
can simply loop the sheets of a workbook to get those...

<air code>
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Debug.Print wks.CodeName
Next 'wks
</air code>

 

[aditel]

Hi Garry,

thank you for your comments.
I will start with results of my checks and observations from your second post.
Unfortunatelly it is not enough to simly loop through the sheets of another workbook, especially when it is opened from network folder. In such cases CodeName is 'Empty'. That's why I decided to get to VBComponents via VBProject.

And now going to first post...
I changed data type to Variant and it worked! However when I saved project and restarted Excel application it stopped working again.
After several tries it seems that it is not even necessary to change data type to Variant but just open VBA editor and run the code. When you do this without opening VBA in new Excel session it does not work.

I really have no idea how to solve this issue.

 

[GS]

Hi Garry,

thank you for your comments.
I will start with results of my checks and observations from your second
post. Unfortunatelly it is not enough to simly loop through the sheets of
another workbook, especially when it is opened from network folder. In such
cases CodeName is 'Empty'. That's why I decided to get to VBComponents via
VBProject.

I don't know what/how you're doing this but the worksheet loop code I
gave you ALWAYS returns the codename because ALL worksheets MUST HAVE A
CODENAME! Thus, CodeName will NEVER be 'Empty'! This works for ALL open
workbooks regardless of where they're stored/opened from.

CodeName is used by many developers to assign design time names to
multi-sheet projects/templates so a ref in our code can be acquired to
the needed sheet in case users rename their tabs. This is a reliable
methodology BECAUSE every sheet ALWAYS has a codename.

And now going to first post...
I changed data type to Variant and it worked! However when I saved project
and restarted Excel application it stopped working again. After several tries
it seems that it is not even necessary to change data type to Variant but
just open VBA editor and run the code. When you do this without opening VBA
in new Excel session it does not work.

I really have no idea how to solve this issue.

I ran YOUR code AS IS and VBA halted, highlighting the line 'dim
mybookProject As VBIDE.VBProject and stated 'User-defined type not
defined'. Changing VBC to Variant AND dropping mybookProject worked as
exampled.

 

[aditel]

So, again I will start from the end.
I am pretty sure that you did not add reference to “Microsoft Visual Basic for Application Extensibility 5.3” what I mentioned in original post. When I removed this reference the same error 'User-defined type not defined'is generated. This is quite obvious of course and this is not strange for me at all.

Next obvious thing is that CodeName called as (Name) in properties windows in VBA editor must not be Empty. But when I want to get Worksheet.CodeName it is Empty! Maybe one hint here - I noticed that this happens when a worksheet.Name contains space in between. I did not check how does it behave with other non-printible characters, but it seems (what is may case) that CodeName is not returned when worksheet name is called e.g. "Summary Report(1)"..

Try to run simple macro that goes through all worksheets collection where some of them have spane in their name and display Worksheet.Name and Worksheet.CodeName. Run code not going to to VBA editor, but directly from Excel (Alt+F8).

 

[aditel]

Probably the answer lays here.
It was confirmed on several portals unfortunately...

"The only time you CANNOT use a sheet's CodeName is when you reference a sheet that is in a workbook different from the one in which the code resides."

 

[GS]

aditel submitted this idea :

So, again I will start from the end.
I am pretty sure that you did not add reference to “Microsoft Visual Basic
for Application Extensibility 5.3†what I mentioned in original post. When I
removed this reference the same error 'User-defined type not defined' is
generated. This is quite obvious of course and this is not strange for me at
all.

Ditch this reference! It's NEVER a good idea to engage dependant
libraries when not needed. So to be clear.., I did not set a ref to
this library and so is why all worked fine for me. I'm suggesting you
redo your approach as I exampled (unless, of course, you just want to
keep messing around with using this lib for curiosity).

Next obvious thing is that CodeName called as (Name) in properties windows in
VBA editor must not be Empty. But when I want to get Worksheet.CodeName it is
Empty! Maybe one hint here - I noticed that this happens when a
worksheet.Name contains space in between. I did not check how does it behave
with other non-printible characters, but it seems (what is may case) that
CodeName is not returned when worksheet name is called e.g. "Summary
Report(1)".

Not true using the approach I posted.

Try to run simple macro that goes through all worksheets collection where
some of them have spane in their name and display Worksheet.Name and
Worksheet.CodeName. Run code not going to to VBA editor, but directly from
Excel (Alt+F8).

That's what I did and got what was expected without error! I suggest
YOU try this WITHOUT using the external ref! Just to be clear.., I ran
the macro from the macros dialog AND the VBE and got identical results.

 

[GS]

After serious thinking aditel wrote :

Probably the answer lays here.
It was confirmed on several portals unfortunately...

"The only time you CANNOT use a sheet's CodeName is when you reference a
sheet that is in a workbook different from the one in which the code
resides."

My tests prove otherwise and so I reiterate: Try my suggested approach!

 

[aditel]

I tried solution you proposed, without having a reference to VBA Ext.
Results are the same when this reference was set on.
Final test I made:

1. Created new workbook
2. Added several sheets more and renamed them as "Summary Report1",
"SummaryReport1", "Summary Report2", "SummaryReport2", etc..
3. Run following macro from another workbook.

-------------------------------
Sub Test()

Dim MyBook As Workbook
Dim Wks As Worksheet
Dim MyFiles As Variant
Dim VBC As Variant
Dim MyText As String

10 MyFiles = Application.GetOpenFilename("Excel files, *.xls", , "Select file...", "Select", True)

20 If IsArray(MyFiles) Then
30 For Each f In MyFiles
40 Set MyBook = Application.Workbooks.Open(f, , True)

50 For Each Wks In MyBook.Worksheets
60 MyText = MyText & "Worksheet Name: " & Wks.Name & vbTab & " CodeName: " & Wks.CodeName & vbCrLf
70 Next

80 MsgBox MyText, , "Based on Worksheets collection"
90 MyText = ""

100 For Each VBC In MyBook.VBProject.VBComponents
110 MyText = MyText & "Worksheet Name: " & VBC.Properties("Name").Value & vbTab & " CodeName: " & VBC.Name & vbCrLf
120 Next

130 MsgBox MyText, , "Based on VBComponents collection"
140 MyBook.Close False

150 Set MyBook = Nothing
160 Next
170 End If

End Sub
-------------------------------

Results:

1. Going through worskheets collection:

Worksheet Name: Summary Report1 CodeName: Sheet1
Worksheet Name: SummaryReport1 CodeName: Sheet2
Worksheet Name: Summary Report2 CodeName: Sheet3
Worksheet Name: SummaryReport2 CodeName: (Empty!)
Worksheet Name: Summary Report3 CodeName: (Empty!)
Worksheet Name: SummaryReport3 CodeName: (Empty!)

It looks newly last three sheets I added are somehow wrongly interpreted.

2. Reaching second For..Each loop (line 100) the same error 1004 raises.
"Method 'VBProject' of object '_Workbook' failed"

 

[GS]

aditel has brought this to us :

I tried solution you proposed, without having a reference to VBA Ext.
Results are the same when this reference was set on.
Final test I made:

1. Created new workbook
2. Added several sheets more and renamed them as "Summary Report1",
"SummaryReport1", "Summary Report2", "SummaryReport2", etc..
3. Run following macro from another workbook.

-------------------------------
Sub Test()

Dim MyBook As Workbook
Dim Wks As Worksheet
Dim MyFiles As Variant
Dim VBC As Variant
Dim MyText As String

10 MyFiles = Application.GetOpenFilename("Excel files, *.xls", , "Select
file...", "Select", True)

20 If IsArray(MyFiles) Then
30 For Each f In MyFiles
40 Set MyBook = Application.Workbooks.Open(f, , True)

50 For Each Wks In MyBook.Worksheets
60 MyText = MyText & "Worksheet Name: " & Wks.Name & vbTab & "
CodeName: " & Wks.CodeName & vbCrLf 70 Next

80 MsgBox MyText, , "Based on Worksheets collection"
90 MyText = ""

100 For Each VBC In MyBook.VBProject.VBComponents
110 MyText = MyText & "Worksheet Name: " &
VBC.Properties("Name").Value & vbTab & " CodeName: " & VBC.Name & vbCrLf 120
Next

130 MsgBox MyText, , "Based on VBComponents collection"
140 MyBook.Close False

150 Set MyBook = Nothing
160 Next
170 End If

End Sub
-------------------------------

Results:

1. Going through worskheets collection:

Worksheet Name: Summary Report1 CodeName: Sheet1
Worksheet Name: SummaryReport1 CodeName: Sheet2
Worksheet Name: Summary Report2 CodeName: Sheet3
Worksheet Name: SummaryReport2 CodeName: (Empty!)
Worksheet Name: Summary Report3 CodeName: (Empty!)
Worksheet Name: SummaryReport3 CodeName: (Empty!)

It looks newly last three sheets I added are somehow wrongly interpreted.

Sorry! I'm not able to reproduce the results you report here. I get a
sheetname AND a codename for every sheet regardless of naming used.

Examples:

Sheets(1).Name = "SheetNameWithoutSpaces(1)"
'Sheets(1).CodeName: "wksSheet1"

Sheets(2).Name = "Sheet Name With Spaces (1)"
'Sheets(2).CodeName: "wksSheet2"

Sheets(3).Name = "SheetNameWithoutSpaces(2)"
'Sheets(3).CodeName: "wksSheet3"

Sheets(4).Name = "Sheet Name With Spaces (2)"
'Sheets(4).CodeName: "wksSheet4"

Sheets(5).Name = "SheetNameWithoutSpaces(3)"
'Sheets(5).CodeName: "wksSheet5"

Sheets(6).Name = "Sheet Name With Spaces (3)"
'Sheets(6).CodeName: "wksSheet6"

Using the following macro...

Sub TestWksCodename()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Sheets
Debug.Print wks.Name & "::" & wks.CodeName
Next 'wks
End Sub

...produces the following results in the Immediate Window:

SheetNameWithoutSpaces(1)::wksSheet1
Sheet Name With Spaces (1)::wksSheet2
SheetNameWithoutSpaces(2)::wksSheet3
Sheet Name With Spaces (2)::wksSheet4
SheetNameWithoutSpaces(3)::wksSheet5
Sheet Name With Spaces (3)::wksSheet6

2. Reaching second For..Each loop (line 100) the same error 1004 raises.
"Method 'VBProject' of object '_Workbook' failed"

And this macro...

Sub TestWkbVBProject()
Dim VBC As Variant, mywkb As Workbook
Set mywkb = ActiveWorkbook
For Each VBC In mywkb.VBProject.VBComponents
Debug.Print VBC.Properties("Name") & "::" & VBC.Name
Next 'vbc
End Sub

...produces the following results in the Immediate Window:

TestWksCodename.xls::ThisWorkbook
Module1::Module1
SheetNameWithoutSpaces(1)::wksSheet1
Sheet Name With Spaces (1)::wksSheet2
SheetNameWithoutSpaces(2)::wksSheet3
Sheet Name With Spaces (2)::wksSheet4
SheetNameWithoutSpaces(3)::wksSheet5
Sheet Name With Spaces (3)::wksSheet6

The results are the same no matter where I save the test wkb saved to,
OR where I open it from. The results are the same no matter where I run
the macro from (VBE or Macros dialog).

 

[GS]

<FWIW>
1. Just want to mention that the variable 'f' used in your 1st
For...Each loop isn't declared in this procedure. That suggests that
either it is declared at a module or global level, OR you do not have
the VBE set up to always require variable declaration. I suspect the
latter and if the case then I strongly suggest you turn this feature on
in Tools>Options on the Editor tab. Doing so will make your code much
easier to troubleshoot and will eliminate coding errors that are common
to NOT turning this feature on.

2. You could move line 150 to the bottom of the procedure since your
For...Each loop resets it each iteration. IOW, you only need to set it
'= Nothing' when you're done using it. Not that it's a big deal BUT it
does add extra unnecessary processing to your loop which, were it a
large number of files, would slow down your progress.

3. You only 'initialize' your variable 'MyText' once before using it.
In the context used here, it needs to be initialized BEFORE each use.

Otherwise, your code worked fine for me selecting several workbooks.

Revised code...

Sub Test()
Dim MyBook As Workbook, Wks As Worksheet
Dim MyFiles, VBC, f, MyText As String

MyFiles = Application.GetOpenFilename("Excel files, *.xls", , "Select
file...", "Select", True)
If IsArray(MyFiles) Then
For Each f In MyFiles
Set MyBook = Application.Workbooks.Open(f, , True)
MyText = "" '//initialize
For Each Wks In MyBook.Worksheets
MyText = MyText & "Worksheet Name: " & Wks.Name _
& vbTab & " CodeName: " & Wks.CodeName & vbCrLf
Next
MsgBox MyText, , "Based on Worksheets collection"

MyText = "" '//initialize
For Each VBC In MyBook.VBProject.VBComponents
MyText = MyText & "Worksheet Name: " _
& VBC.Properties("Name").Value _
& vbTab & " CodeName: " & VBC.Name & vbCrLf
Next
MsgBox MyText, , "Based on VBComponents collection"

MyBook.Close False
Next
End If
Set MyBook = Nothing
End Sub

 

[aditel]

Hi Garry,

thank you for all your comments and suggestions.
This code I shared is just a short example to show the problem I faced.
Nevertheless it still does not work at me side and case is not resolved.
I do have no more ideas and start giving up fighting with that issue.
Nevertheless thank you once again for your help here.

-Marcin-

 

[GS]

aditel submitted this idea :

Hi Garry,

thank you for all your comments and suggestions.
This code I shared is just a short example to show the problem I faced.
Nevertheless it still does not work at me side and case is not resolved.
I do have no more ideas and start giving up fighting with that issue.
Nevertheless thank you once again for your help here.

-Marcin-

Marcin,
Sorry you're still having troubles with this. As I reported, your code
(revised as posted) works fine for me and so I suggest you look
elsewhere for the cause of the problem. Best wishes!

 

[aditel]

I found out what was reason of the problem with error 1004.
It was security level of macros that by default is set to "Disable all macros with notification". So when workbook was opened and code was running, it was not possible to get to VBProject object when macros in the workbook are not enabled.

Solution for that is to read value of AutomationSecurity, then change it to Low level, open workbook, do necessary actions, close workbook and finally back to origional AutomationSecurity setting before file was opened.

SecurityLevel = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityLow

...
code lines
...

Application.AutomationSecurity = SecurityLevel

 

[GS]

aditel submitted this idea :

I found out what was reason of the problem with error 1004.
It was security level of macros that by default is set to "Disable all macros
with notification". So when workbook was opened and code was running, it was
not possible to get to VBProject object when macros in the workbook are not
enabled.

Solution for that is to read value of AutomationSecurity, then change it to
Low level, open workbook, do necessary actions, close workbook and finally
back to origional AutomationSecurity setting before file was opened.

SecurityLevel = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityLow

...
code lines
...

Application.AutomationSecurity = SecurityLevel

This suggests that this property was altered (by code at some point) as
the default Excel setting is 'msoAutomationSecurityLow' and works fine
regardless of what UI security settings are in place.

Note that automated instances of Excel have no security settings at
all. I suspect this is the reason why the AutomatedSecurity default is
'msoAutomationSecurityLow'.