"shell" not work in Excel ?

[clarksonneo]

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:
[vba]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
[/vba]


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

[vba]

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

[/vba]

Thanks

[GTO]

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:

[VBA]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[/VBA]

Hope that helps,

Mark

[clarksonneo]

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:

[vba]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[/vba]
Hope that helps,

Mark

Hi,

Do most/all code in this web are old things?
http://msdn.microsoft.com/en-us/libr...=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]

Hi there,

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

In this bit:

[VBA]Dim objShell As Object ' Shell[/VBA]

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]

...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