Microsoft Common Dialog control, version 6.0

[Kevin E.]

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

 

[Kevin E.]

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

 

[Dave Peterson]

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

 

[Kevin E.]

I should have specified earlier. The macro that I am creating is for some
people at work, so the default directories that i want to create will always
be in the same location. After I work with the programming a little bit I
will start to work with logic to test if the directory I want to default to
exists (from what I can tell that is what the code you provided me is doing).
So I have two questions:

1. I'm a little stuck on a little piece of code that I can't seem to figure
out
TestStr = Dir(NewFolder & "\nul")
I'm not sure what the "\nul" means. I looked up the Dir Funcion in the VBA
help menu and I dont see what the "\nul" means.

2. I entered the following code in my project but it doesnt seem to work.
It's sloppying coding I know but I'm trying to compile a macro quickly for my
current employer to automate some 3-D cad assemblies before I leave for a new
job.

Private sub btnBrowse1_Click()
Dim varPartFilePath as Variant
ChDrive "U"
ChDir "U:\File Folder\File Folder\Final File
Folder"
varPartFilePath = application.GetOpenFilename()
end sub

but when i Click the Browse1 Button it opens to the original default
directory, not the drive or directory i specified in the chdrive and chdir
commands.

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

 

[Dave Peterson]

#1. NUL is a old DOS name for a Null device. Kind of like CON: for the console
or PRN: for the printer.

Every folder that exists has a Nul device. So this is just a quick way to test
to see if that folder exists.

#2. I'd bet that there really isn't a folder named:
File Folder\File Folder\Final File Folder
on your U: drive

Maybe you should change:
MsgBox "design error!"
to
MsgBox "Folder doesn't exist!"

 

[Dave Peterson]

Ps. I would have thought you would have gotten an error message unless your
code was suppressing errors.

 

[Kevin E.]

you are correct, I don't have a file on my "U" drive named "File Folder" or
"Final File Folder", I was just giving you a generic path to indicate i was
going many folders deep into the particular drive. and yes i left out error
suppression because i'm still new and still learning about this programming
stuff. with that said can you explain why that code didnt work and why the
open dialoge box wont open to the folder I specify in the "chDir" command.

 

[Dave Peterson]

Nope. I can't explain it if your folder really did exist and it didn't open to
that folder.

 

[Kevin E.]

I hate computers!!!!

Nope. I can't explain it if your folder really did exist and it didn't open to
that folder.

 

[Dave Peterson]

Did you try it with a folder on the C: drive?

Did it work then?