Search for a specific file's extension

  • Thread starter Thread starter 2007-User
  • Start date Start date
2

2007-User

Hi Guys,

I need a VBA to be able to search for a file with specific extension (like
"ecw"),
and have another application to open that file.
(search for the file can be in the same directory that the correct open
excel file is existing)

How this is possible?
Any help would be appreciated.
 
This should work after a buttonclick...

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory
_
As String, ByVal nShowCmd As Long) As Long

Private Sub Worksheet_Button_SearchECW_Click()
Dim searchPath, ecwFile As String
Dim fso, fs, fc, f
searchPath = ThisWorkbook.Path & "\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fs = fso.GetFolder(searchPath)
Set fc = fs.Files
For Each f In fc
If Right(f.Name, 4) = ".ECW" Then
ecwFile = f.Name
Exit For
End If
Next f
Set f = Nothing
Set fc = Nothing
Set fs = Nothing
Set fso = Nothing
ShellExecute vbNull, vbNullString, ecwFile, vbNullString, searchPath, 1
End Sub
 
wow, this is so good
thank you so much.


moon said:
This should work after a buttonclick...

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory
_
As String, ByVal nShowCmd As Long) As Long

Private Sub Worksheet_Button_SearchECW_Click()
Dim searchPath, ecwFile As String
Dim fso, fs, fc, f
searchPath = ThisWorkbook.Path & "\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fs = fso.GetFolder(searchPath)
Set fc = fs.Files
For Each f In fc
If Right(f.Name, 4) = ".ECW" Then
ecwFile = f.Name
Exit For
End If
Next f
Set f = Nothing
Set fc = Nothing
Set fs = Nothing
Set fso = Nothing
ShellExecute vbNull, vbNullString, ecwFile, vbNullString, searchPath, 1
End Sub
Click to expand...
 
Moon!,
It gives me an error on this line :
Set fso = CreateObject("Scripting.FileSystemObject")

the error is "ActiveX component can't create objects"

Do you know why I am getting this message?

Thanks.
 
Hi,

That "Scripting.FileSystemObject" must be use Reference:

Microsoft Scripting Runtime

Just check your Tools>References commandbar of VBE
Regards,

Halim


2007-User menuliskan:
 
The ScriptingRuntime FileSystemObject is included with
all Windows operating systems released after Windows 95.
It is not available on a Mac or the older Windows systems.
Sometimes it is necessary to re-registering the scrrun.dll with windows.
To register it, press the windows Start Button and choose Run.
Then in the Text Box type:
regsvr32 "C:\WINDOWS\system32\scrrun.dll"
Click OK
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"2007-User"
<[email protected]>
wrote in message
Moon!,
It gives me an error on this line :
Set fso = CreateObject("Scripting.FileSystemObject")
the error is "ActiveX component can't create objects"
Do you know why I am getting this message?
Thanks.
 
CreateObject is used when you don't create a reference.

However, if attempted, the inability to create the reference should
reinforce the fact that the scripting runtime is not registered.
 
Back
Top