PDA

View Full Version : "shell" not work in Excel ?



clarksonneo
08-11-2011, 08:24 PM
Hi,

I created a macro to return the file type of a file.
The name of the file is clock.avi.
The fold is C:\WINDOWS.

Please see below for the macro I created.

When I run the macro, there is a problem of "user-defined type not defined.
Please see the picture below.

Could you please amend my macro so that it works?

My macro:
Sub Get_File_Type()

Dim objShell As Shell
Dim objFolder As Folder

Set objShell = New Shell
Set objFolder = objShell.Namespace("C:\WINDOWS")

Dim objFolderItem As FolderItem
Set objFolderItem = objFolder.ParseName("clock.avi")

Range("a1") = objFolder.GetDetailsOf(objFolderItem, 2)

End Sub



In fact, I just modify the macro based on
http://msdn.microsoft.com/en-us/library/bb787870%28v=vs.85%29.aspx



Private Sub btnGetDetailsOf_Click()
Dim objShell As Shell
Dim objFolder As Folder

Set objShell = New Shell
Set objFolder = objShell.NameSpace("C:\WINDOWS")

If (Not objFolder Is Nothing) Then
Dim objFolderItem As FolderItem
Set objFolderItem = objFolder.ParseName("clock.avi")

If (Not objFolderItem Is Nothing) Then
Dim szItem As String
szItem = objFolder.GetDetailsOf(objFolderItem, 2)
End If

Set objFolderItem = Nothing
End If

Set objFolder = Nothing
Set objShell = Nothing
End Sub



Thanks

GTO
08-11-2011, 08:45 PM
The example code presumes early-binding, so you would need to set a reference to 'Microsoft Shell Controls and Automation' (Shell32.dll). Or, you could use late-binding like:

Sub Get_File_Type()

Dim objShell As Object ' Shell
Dim objFolder As Object ' Folder

'Set objShell = New Shell
Set objShell = CreateObject("Shell.Application")

Set objFolder = objShell.Namespace("C:\WINDOWS")

Dim objFolderItem As Object ' FolderItem
Set objFolderItem = objFolder.ParseName("clock.avi")

Range("a1") = objFolder.GetDetailsOf(objFolderItem, 2)
End Sub

Hope that helps,

Mark

clarksonneo
08-11-2011, 11:01 PM
The example code presumes early-binding, so you would need to set a reference to 'Microsoft Shell Controls and Automation' (Shell32.dll). Or, you could use late-binding like:

Sub Get_File_Type()

Dim objShell As Object ' Shell
Dim objFolder As Object ' Folder

'Set objShell = New Shell
Set objShell = CreateObject("Shell.Application")

Set objFolder = objShell.Namespace("C:\WINDOWS")

Dim objFolderItem As Object ' FolderItem
Set objFolderItem = objFolder.ParseName("clock.avi")

Range("a1") = objFolder.GetDetailsOf(objFolderItem, 2)
End Sub
Hope that helps,

Mark

Hi,

Do most/all code in this web are old things?
http://msdn.microsoft.com/en-us/library/bb787870%28v=vs.85%29.aspx

Is excel 2007 not able to use those code in the web above?

How can I know whether the code is late-binding or not?

thanks

GTO
08-11-2011, 11:16 PM
Hi there,

I would suggest reading thru the vba Help a bit, maybe starting with CreateObject().

In this bit:

Dim objShell As Object ' Shell

Without a reference set to Shell's library, declaring objShell As Shell is what got you the error returned. In short, VBA could not find a User Type or library, so it tells you.

Substituting As Object makes it late-bound, as we are now declaring objShell as a generic Object, rather than a specific type of object.

Hope thta helps,

Mark

GTO
08-11-2011, 11:19 PM
...Is excel 2007 not able to use those code in the web above?...

I forgot to mention: I'm not sure whether this part means you weren't able to get your code example running, or other examples on the web page you were at. If the former, I ran/tested the code I posted at #2, without any issue. I am currently in XP/Office 2010.

Mark