Limiting files to open to only files with certain name.

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
0
Utf
1/1/2010 4:35:01 PM
excel.programming 6508 articles. 2 followers. Follow

10 Replies
799 Views

Similar Articles

[PageSpeed] 42

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.

-- 
Rick (MVP - Excel)


"Brian" <Brian@discussions.microsoft.com> wrote in message 
news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
>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 

0
Rick
1/1/2010 5:14:23 PM
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" <Brian@discussions.microsoft.com> a écrit dans le message de groupe de discussion 
: B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
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 

0
michdenis
1/1/2010 5:34:36 PM
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



"Rick Rothstein" wrote:

> 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.
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Brian" <Brian@discussions.microsoft.com> wrote in message 
> news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
> >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 
> 
> .
> 
0
Utf
1/1/2010 7:06:01 PM
It works to open the file, but you can open any of the files no matter what 
the name is.

"Rick Rothstein" wrote:

> 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.
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Brian" <Brian@discussions.microsoft.com> wrote in message 
> news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
> >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 
> 
> .
> 
0
Utf
1/1/2010 7:10:01 PM
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 wrote:
> 
> It works to open the file, but you can open any of the files no matter what
> the name is.
> 
> "Rick Rothstein" wrote:
> 
> > 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.
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
> > >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
> >
> > .
> >

-- 

Dave Peterson
0
Dave
1/1/2010 8:42:31 PM
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" wrote:

> 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 wrote:
> > 
> > It works to open the file, but you can open any of the files no matter what
> > the name is.
> > 
> > "Rick Rothstein" wrote:
> > 
> > > 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.
> > >
> > > --
> > > Rick (MVP - Excel)
> > >
> > >
> > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
> > > >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
> > >
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
1/1/2010 9:58:01 PM
Dim myMsg as string

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

....

msgbox mymsg

Brian wrote:
> 
> 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" wrote:
> 
> > 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 wrote:
> > >
> > > It works to open the file, but you can open any of the files no matter what
> > > the name is.
> > >
> > > "Rick Rothstein" wrote:
> > >
> > > > 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.
> > > >
> > > > --
> > > > Rick (MVP - Excel)
> > > >
> > > >
> > > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > > news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
> > > > >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
> > > >
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
1/1/2010 10:20:09 PM
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???

Dave Peterson wrote:
> 
> Dim myMsg as string
> 
> mymsg = Engineer_2.value & vblf & "You can only open ..."
> 
> ...
> 
> msgbox mymsg
> 
> Brian wrote:
> >
> > 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" wrote:
> >
> > > 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 wrote:
> > > >
> > > > It works to open the file, but you can open any of the files no matter what
> > > > the name is.
> > > >
> > > > "Rick Rothstein" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > > --
> > > > > Rick (MVP - Excel)
> > > > >
> > > > >
> > > > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > > > news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
> > > > > >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
> > > > >
> > > > > .
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> --
> 
> Dave Peterson

-- 

Dave Peterson
0
Dave
1/1/2010 10:25:54 PM
I am getting a Compile Error: syntax error
mymsg = Engineer_2.value & vblf & "You can only open ..."


Dave Peterson" wrote:

> Dim myMsg as string
> 
> mymsg = Engineer_2.value & vblf & "You can only open ..."
> 
> ....
> 
> msgbox mymsg
> 
> Brian wrote:
> > 
> > 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" wrote:
> > 
> > > 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 wrote:
> > > >
> > > > It works to open the file, but you can open any of the files no matter what
> > > > the name is.
> > > >
> > > > "Rick Rothstein" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > > --
> > > > > Rick (MVP - Excel)
> > > > >
> > > > >
> > > > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > > > news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
> > > > > >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
> > > > >
> > > > > .
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
1/1/2010 11:21:01 PM
I'd guess you made a typing error that you didn't share.

Brian wrote:
> 
> I am getting a Compile Error: syntax error
> mymsg = Engineer_2.value & vblf & "You can only open ..."
> 
> Dave Peterson" wrote:
> 
> > Dim myMsg as string
> >
> > mymsg = Engineer_2.value & vblf & "You can only open ..."
> >
> > ....
> >
> > msgbox mymsg
> >
> > Brian wrote:
> > >
> > > 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" wrote:
> > >
> > > > 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 wrote:
> > > > >
> > > > > It works to open the file, but you can open any of the files no matter what
> > > > > the name is.
> > > > >
> > > > > "Rick Rothstein" wrote:
> > > > >
> > > > > > 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.
> > > > > >
> > > > > > --
> > > > > > Rick (MVP - Excel)
> > > > > >
> > > > > >
> > > > > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > > > > news:B3A497CE-CDEA-4534-9444-B139C21C9182@microsoft.com...
> > > > > > >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
> > > > > >
> > > > > > .
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
1/1/2010 11:47:21 PM
Reply:

Similar Artilces:

#Delete Mark in Bound Memo filed
I have form that has bound memo field, sometime, no sure how it happen, the memo filed is filled with #Delete. My application is a stand alone program. Kindly advise what can cause this and how to avoid it from happening. -- TS Lim When was the last time you performed a compact and repair? Is you db split? Does each user have their own copy of the front-end? Please checkout http://www.granite.ab.ca/access/corruptmdbs.htm http://www.granite.ab.ca/access/corruption/symptoms.htm http://office.microsoft.com/en-ca/access/HA011865661033.aspx - No very helpful but directly from MS ...

Opening publisher 97 with a later version
I have publisher 97 on my windows xp and it works fine. However, I had someone refine some work I had done and apparently they used a later version of Publisher because when I loaded their CD, I got the message "Publisher cannot load files from a different version" What can I do--does this mean I have to buy a later version to match the version he used, or do I have to buy one of those programs that can open other programs. If I do the later, will I be able to use my 97 version to make changes once I get the files open on the later version Thanks rjda Refer to http://www.mvps....

CSV File Problem
Can any one explain why the following problem occurs please? A colleague has a small list of data which is a csv file exported from SQL. Column One is a serial number from 1 to 29. Column 2 is a number of transactions which have occurred.(it should look like example A below in Excel) 1 475 1,475 2 1732 21,732 3 1670 31,670 4 2176 42,176 5 608 5,608 6 1579 61,579 7 43101 743,101 8 54512 854,512 9 51258 951,258 10 61050 1,061,050 A B If I double click on the csv file, it opens in Excel but c...

Override Limit
Customer credit limit override approval workflow Hello all, Just wondering if/where we can have the override credit limit just in effect on either the Order or Invoice and not both. TY No, the concept is that when you clear a credit hold on an order the customer must have paid their bill our you increased their credit, which would allow it to move forward as an invoice, unless, of course, they over charged and under paid again. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our...

CFile (delete file)
How can I delete all files that end with ".temp" in some folder? CFile::Remove remove unlink -- cheers, Alok Gupta Blogs: http://wdevs.com/thatsalok "Petar Popara" <my.fake@mail.net> wrote in message news:Op6#URKfFHA.2644@TK2MSFTNGP09.phx.gbl... > > How can I delete all files that end with ".temp" in some folder? > > SHFileOperation() will and it supports wildcards! DeleteFile() will operate on one file at a time. "Petar Popara" <my.fake@mail.net> wrote in message news:Op6%23URKfFHA.2644@TK2MSFTNGP09.phx.gbl... > >...

Opening an *.MSG file with it's respective attachment.
Hello, The file *.MSG is a message file. This can be viewed by any editor, like Notepad. However, when there's an attachment to it, we see garbage. Is there a way to open the attachments present inside the *.MSG files ? Thank you Yes, by opening it with Outlook. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox!...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

import a 123 file
My excel version cannot open a Lotus 123 file. Is there an import feature to move the data from Lotus 123 to Excel? Thanks No, you need to convert the file either from Lotus 123 or get commercial converter -- Regards, Peo Sjoblom "Frank" <Frank@discussions.microsoft.com> wrote in message news:59B55F98-4C79-4917-928B-F1B309660730@microsoft.com... > My excel version cannot open a Lotus 123 file. Is there an import feature > to > move the data from Lotus 123 to Excel? > Thanks > > ...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Where is the cache files folder located for windows media player?
Does windows media player have a cache files folder? Where is the cache files folder located for windows media player? Or how do I find the cache files folder for windows media player? Thank You for your Help. Bob Background info: I have a desktop AMD 4400+ 64X2 4GB memory 320GB HD(way bigger than I need) Acer computer (AM3100-U3201A) I purchased new February 2008 running 32-bit MS Vista Home Premium SP2 box. I use MS auto updates. I think it has onboard shared memory for video and sound. My ISP is Comcast 12mbps with Motorola SURFboard SB5101 Cable Modem direct to...

opening .pst archives
I am trying to open an outlook e-mail archive file (pst) that was made when I had Office 98 and transferred to my new computer (Office 2003 Pro). I am looking for an old e-mail that has now become evidence in a lawsuit. Can anyone help? You didn't say if the PST file was on a CDR/RW, hard drive, or network share. In any case, the PST file must be located on a local/network drive where you have full access (read, write, modify, delete, .etc) rights. Once the file is located in said place, you can open the PST file via File | Open | Outlook Data File. To close the PST file when you ...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Unable to open Outlook Express after upgrade to Win. XP
I upgraded from windows98 to windows XP Pro. Now when I try to use OE I get two error messages. "Outlook Express could not be started. The application was unable to open the OE message store. Your compter may be out of memory or your disk is full.....0x800c012e,3" "OE could not be started because MSOE.DLL could not be initialized. OE maynot be installd correctly" I found a similiar problem fix for window2000 which said to delete OE, rename the old folder, then edit the registry and reinstall. I did the above but was not allowed to rename the old folder and th...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

opening a .docx with word 2004
Version: 2004 Operating System: Mac OS X 10.3 (Panther) How can I open a .docx attachment that has been sent to me with word 2004?? Can I convert it somehow at my end or does it have to be converted from the sender end? Thanks for any help... <cscs@sympatico.ca> wrote: > How can I open a .docx attachment that has been sent to me with word > 2004?? Can I convert it somehow at my end or does it have to be > converted from the sender end? Make sure that Office is up to date (or at least in version 11.5.0 - the altest version being 11.5.1) and install the XML convertrs you'...

printing imported gif files in Excel
When printing gif file with transparent background on the grey(25%) background of Excel non-transparent part of the picture has white border around it. Is there any way to prevent it? Thank you in advance... Tim ...

unknown .edb file
I have error messages in the log files that references a corrupt database named C:\Program Files\exchsrvr\Exchangeserver_Outlook.edb. "Outlook" is the name of the server. The problem is that this database is not listed in any of the Mail Stores or Public stores. I want to run the repair utilities on it, but since I'm not sure what it is used for, I don't know how I can restore it if the repair goes bad. Does anyone know what this is used for or how I can determine that myself? BTW, the database always has a current time stamp, so I assume that it is used by something....

Copying dbx files from a CDR back into Outlook
My computer was recently hijakced and I had to reinstall everything. I did however save all of my folders in Outlook onto a CD Rom and I do not know how to transfer them back so I can use them. Also I had an animated version of outlook where you could choose loads of diffent stationery and I cannot seem to find that upgrade anaywhere. Can you help me? Tee <tsahai33@hotmail.com> wrote: > My computer was recently hijakced and I had to reinstall > everything. I did however save all of my folders in > Outlook onto a CD Rom and I do not know how to transfer > them back so...

Effectively stoping open relay.
I have a client who started using exchange 2003 few days back. This client is having more than 12 subnets starting from 172.27.50.0, 172.16.0.0 to 172.27.0.0 and 10.172.172.0 & 10.172.173.0 subnets in different location in town which are conneted by DSL. This users are connecting to the Exchange server 172.16.5.25. Most of these users are POP3 users. I have 2 SMTP Virtual Server running. Default SMTP Virtual server 172.16.5.25 and the second for Externel. In the Mail connector I have added only the external as the local bridgehead server. In the relay properties of the internal I...

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...