Limiting files to open to only files with certain name.

[Brian]

I have a User Form with a Control Button that is for opening Exsisting Files.
I only want to Open Files with the word "Spec" in the name? Is there a way to
only show & Open Excel Files with "Spec' in the name?

I have the following code that opens the Dialog Box, but it shows all the
files with the ".xlsm" extension. I want to narrow it down to only files with
"Spec" in the name. It would be nice if it would show all the differnt excel
file extensions.


' Open Existing Eng Spec 9 Control Button
Private Sub Open_Existing_Eng_Spec_9_Click()

FileToOpen = Application.GetOpenFilename("SPEC" (*.xlsm), *.xlsm")

If FileToOpen = False Then

MsgBox "The Open Method Failed, No Eng Spec was Opened", , "C.E.S."

Exit Sub

End If

Set bk = Workbooks.Open(Filename:=FileToOpen)
End Sub

 

[Rick Rothstein]

You didn't say where you wanted the "Spec" to be in the name, so I assumed
in the front...

FileToOpen = Application.GetOpenFilename("SPEC (*.xlsm), Spec*.xlsm")

Note that you had a misplaced quote mark in your example statement.

 

[michdenis]

Try this in general module

'---------------------------------------------------
Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

'---------------------------------------------------
Private Function SelectAFile( _
Path As String, _
Optional filtre As String = "*.*") As String
Dim OpenFile As OPENFILENAME, lReturn As Long, sFilter As String
OpenFile.lStructSize = Len(OpenFile)
sFilter = "All Excel Files (" & filtre & ")" & Chr(0) & filtre & Chr(0)
With OpenFile
.lpstrFilter = sFilter
.nFilterIndex = 1
.lpstrFile = String(257, 0)
.nMaxFile = Len(OpenFile.lpstrFile) - 1
.lpstrFileTitle = OpenFile.lpstrFile
.nMaxFileTitle = OpenFile.nMaxFile
.lpstrInitialDir = Path
.lpstrTitle = "Files to Open"
.flags = 0
End With
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
Else
SelectAFile = Trim(Left(OpenFile.lpstrFile, _
InStr(1, OpenFile.lpstrFile, Chr(0)) - 1))
End If
End Function

'---------------------------------------------------
Sub test()
Dim File_to_Open As Variant
Dim Path As String

Path = "c:\"

File_to_Open = SelectAFile(Path, "*Spec*.xlsm")
If File_to_Open <> "" Then
MsgBox File_to_Open
End If
End Sub
'---------------------------------------------------

You may combine this string "*Spec*.xlsm" of a number of ways
"Spec*.xlsm : Begin by Spec
"*Spec*.xls* : All excel file having different extensions xls, xlsm...
..../ etc


"Brian" <[email protected]> a écrit dans le message de groupe de discussion
: (e-mail address removed)...
I have a User Form with a Control Button that is for opening Exsisting Files.
I only want to Open Files with the word "Spec" in the name? Is there a way to
only show & Open Excel Files with "Spec' in the name?

I have the following code that opens the Dialog Box, but it shows all the
files with the ".xlsm" extension. I want to narrow it down to only files with
"Spec" in the name. It would be nice if it would show all the differnt excel
file extensions.


' Open Existing Eng Spec 9 Control Button
Private Sub Open_Existing_Eng_Spec_9_Click()

FileToOpen = Application.GetOpenFilename("SPEC" (*.xlsm), *.xlsm")

If FileToOpen = False Then

MsgBox "The Open Method Failed, No Eng Spec was Opened", , "C.E.S."

Exit Sub

End If

Set bk = Workbooks.Open(Filename:=FileToOpen)
End Sub

 

[Brian]

Yes the name is as follows:

"SPEC " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

 

[Brian]

It works to open the file, but you can open any of the files no matter what
the name is.

 

[Dave Peterson]

Check the name of the file after the user gives it to you--and only open it if
you think it's ok:

Option Explicit
' Open Existing Eng Spec 9 Control Button
Private Sub Open_Existing_Eng_Spec_9_Click()

Dim FileToOpen As Variant 'could be boolean
Dim bk As Workbook
Dim LastBackSlashPos As Long

FileToOpen = Application.GetOpenFilename("SPEC (*.xlsm), Spec*.xlsm")

If FileToOpen = False Then
MsgBox "The Open Method Failed, No Eng Spec was Opened", , "C.E.S."
Exit Sub
End If

LastBackSlashPos = InStrRev(FileToOpen, "\", -1, vbTextCompare)

If UCase(Mid(FileToOpen, LastBackSlashPos + 1, 4)) <> UCase("SPEC") Then
MsgBox "must start with SPEC"
Exit Sub
End If

Set bk = Workbooks.Open(Filename:=FileToOpen)

End Sub

You may want to add a check for the extension or anything else you can think
of...

(instrrev was added in xl2k. If you're supporting xl97, then you'll have to
parse that name some other way (looping backwards from the last character until
you come to a backslash is as good as any).)

 

[Brian]

How would I add the message to read like this

MsgBox = msg Engineer_2.value "you can only a Open Installer Form", ,
"C.E.S."

Engineer_2.value is a value from a combo box.

I tried it but I get a compile Error: Expected End of Statement

What did i do wrong?

 

[Dave Peterson]

Dim myMsg as string

mymsg = Engineer_2.value & vblf & "You can only open ..."

....

msgbox mymsg

 

[Dave Peterson]

ps.

msgbox prompt:=mymsg, title:="C.E.S"
or without the variable:

msgbox prompt:=Engineer_2.value & vblf & "You can only open ...", _
title:="C.E.S"

I like using the named parms (Prompt:=, title:=) instead of using the positional
parameters--who knows how many commas I'd have to use???

 

[Brian]

I am getting a Compile Error: syntax error
mymsg = Engineer_2.value & vblf & "You can only open ..."

 

[Dave Peterson]

I'd guess you made a typing error that you didn't share.