I want to be able to ask the user to point to a file (when running a macro
and without opening the file) and for the directory structure and filename
location to be stored in a variable within my VB code to be used at different
points later in the macro.

I have tried 'variable name = Application.Dialogs(xlDialogOpen).show'
but this opens the file and stores the value TRUE in the variable.

Can this be done?

Kelford

Application.GetOpenFileName will do what you want (following is from Help
file):
Displays the standard Open dialog box and gets a file name from the user
without actually opening any files.

Syntax

expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText,
MultiSelect)

expression Required. An expression that returns an Application object.

FileFilter Optional Variant. A string specifying file filtering criteria.

This string consists of pairs of file filter strings followed by the MS-DOS
wildcard file filter specification, with each part and each pair separated by
commas. Each separate pair is listed in the Files of type drop-down list box.
For example, the following string specifies two file filters€”text and addin:
"Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla".

To use multiple MS-DOS wildcard expressions for a single file filter type,
separate the wildcard expressions with semicolons; for example, "Visual Basic
Files (*.bas; *.txt),*.bas;*.txt".

If omitted, this argument defaults to "All Files (*.*),*.*".

FilterIndex Optional Variant. Specifies the index numbers of the default
file filtering criteria, from 1 to the number of filters specified in
FileFilter. If this argument is omitted or greater than the number of filters
present, the first file filter is used.

Title Optional Variant. Specifies the title of the dialog box. If this
argument is omitted, the title is "Open."

ButtonText Optional Variant. Macintosh only.

MultiSelect Optional Variant. True to allow multiple file names to be
selected. False to allow only one file name to be selected. The default value
is False

Remarks

This method returns the selected file name or the name entered by the user.
The returned name may include a path specification. If MultiSelect is True,
the return value is an array of the selected file names (even if only one
filename is selected). Returns False if the user cancels the dialog box.

This method may change the current drive or folder.



"Kelford" wrote:

I want to be able to ask the user to point to a file (when running a macro
and without opening the file) and for the directory structure and filename
location to be stored in a variable within my VB code to be used at different
points later in the macro.

I have tried 'variable name = Application.Dialogs(xlDialogOpen).show'
but this opens the file and stores the value TRUE in the variable.

Can this be done?

Kelford

Thank you,
I tried what you said and it worked first time.

"K Dales" wrote:

Application.GetOpenFileName will do what you want (following is from Help
file):
Displays the standard Open dialog box and gets a file name from the user
without actually opening any files.

Syntax

expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText,
MultiSelect)

expression Required. An expression that returns an Application object.

FileFilter Optional Variant. A string specifying file filtering criteria.

This string consists of pairs of file filter strings followed by the MS-DOS
wildcard file filter specification, with each part and each pair separated by
commas. Each separate pair is listed in the Files of type drop-down list box.
For example, the following string specifies two file filters€”text and addin:
"Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla".

To use multiple MS-DOS wildcard expressions for a single file filter type,
separate the wildcard expressions with semicolons; for example, "Visual Basic
Files (*.bas; *.txt),*.bas;*.txt".

If omitted, this argument defaults to "All Files (*.*),*.*".

FilterIndex Optional Variant. Specifies the index numbers of the default
file filtering criteria, from 1 to the number of filters specified in
FileFilter. If this argument is omitted or greater than the number of filters
present, the first file filter is used.

Title Optional Variant. Specifies the title of the dialog box. If this
argument is omitted, the title is "Open."

ButtonText Optional Variant. Macintosh only.

MultiSelect Optional Variant. True to allow multiple file names to be
selected. False to allow only one file name to be selected. The default value
is False

Remarks

This method returns the selected file name or the name entered by the user.
The returned name may include a path specification. If MultiSelect is True,
the return value is an array of the selected file names (even if only one
filename is selected). Returns False if the user cancels the dialog box.

This method may change the current drive or folder.



"Kelford" wrote:

I want to be able to ask the user to point to a file (when running a macro
and without opening the file) and for the directory structure and filename
location to be stored in a variable within my VB code to be used at different
points later in the macro.

I have tried 'variable name = Application.Dialogs(xlDialogOpen).show'
but this opens the file and stores the value TRUE in the variable.

Can this be done?

Kelford